No value given for one or more required parameters (VB net ms-access)

Question

1.00/5 (1 vote)

See more:

i have a 2 datatable with these field
*dataordner
1. Archive Date/Time (datetimepicker, it's already data)
2. Storage Short Text (combobox)
3. BulanOrdner ShortText (combobox)
4. TahunOrdner Date/Time (datetimepicker)

*datalemari
1. Archive Date/Time (datetimepicker, it's already data)
2. Storage Short Text (combobox)
3. Lemari Short Text (combobox)
4. BulanOrdner ShortText (combobox)
5. TahunOrdner Date/Time (datetimepicker)

and i now very confused why i'm wrong

What I have tried:

i've done with my codes but always error with that error "no value given for one more...."
i call 2 table with if else in combobox(storage)
this is my codes, i think that's all true with selected item in combobox
note:
storage = combobox

Private Sub storage_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles storage.SelectedIndexChanged
    If storage.SelectedIndex = 1 Then
        groupordner.Visible = True
        grouplemari.Visible = False
    Else
        If storage.SelectedIndex = 2 Then
            groupordner.Visible = False
            grouplemari.Visible = True
        End If
    End If
End Sub

Private Sub save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click
    If storage.SelectedItem = "Ordner" Then
        str = "Update dataordner set Storage = " & storage.Text & ", BulanOrdner = " & bulanordner.Text & ", TahunOrdner = '" & tahunordner.Value & "' Where Archive = '" & tanggalarchive.Value & "'"
        proses.ExecuteNonQuery(str)
        MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
        Call bersih()
        Call data_penjualan()
    Else
        If storage.SelectedItem = "Lemari" Then
            str = "Update datalemari set Storage = " & storage.Text & ", Lemari = " & lemari.Text & ", BulanLemari = " & bulanlemari.Text & ", TahunLemari = '" & tahunlemari.Value & "' Where Archive = '" & tanggalarchive.Value & "'"
            proses.ExecuteNonQuery(str)
            MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
            Call bersih()
            Call data_penjualan()
        End If
    End If
End Sub

Posted 5-Sep-18 18:17pm

Member 13974630

Updated 5-Sep-18 21:59pm

Add a Solution

Comments

Bryian Tan 6-Sep-18 0:42am

Not clear which line is throwing the error, but look like lemari.Text, storage.Text is a string, should be wrap in a '' ex: '" & storage.Text & "'

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-09-05T21:19:00

Solution 1

Don't do it like that!
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Fix that throughout your whole app - because if you miss just one somebody else will find it - and you will find that your existing problem vanishes at the same time.

Posted 5-Sep-18 21:19pm

OriginalGriff

Comments

Member 13974630 6-Sep-18 4:26am

you said i edit my codes with this?

If storage.SelectedItem = "Ordner" Then
str = "Update dataordner set Where Archive = @tanggalarchive"
command = New OleDbCommand(str, con)
command.Parameters.Add("@storage", OleDbType.VarChar).Value = storage.Text
command.Parameters.Add("@bulanorder", OleDbType.VarChar).Value = bulanordner.Text
command.Parameters.Add("@tahunorder", OleDbType.Date).Value = tahunordner.Value
command.Parameters.Add("@tanggalarchive", OleDbType.Date).Value = tanggalarchive.Value
proses.ExecuteNonQuery(str)
MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
Call bersih()
Call data_penjualan()
Else

OriginalGriff 6-Sep-18 4:37am

No, because that isn't anything like your original UPDATE query - it's not even syntactically correct!

OriginalGriff 6-Sep-18 5:01am

So show us the version that has parameters, and gave the error.

Member 13974630 6-Sep-18 5:27am

Try
If storage.SelectedItem = "Ordner" Then
str = "Update dataordner set Storage = @storage, BulanOrdner = @bulanordner, TahunOrdner = @tahunordner Where Archive = @tanggalarchive"
command = New OleDbCommand(str, con)
command.Parameters.Add("@storage", OleDbType.VarChar).Value = storage.Text
command.Parameters.Add("@bulanorder", OleDbType.VarChar).Value = bulanordner.Text
command.Parameters.Add("@tahunorder", OleDbType.Date).Value = tahunordner.Value
command.Parameters.Add("@tanggalarchive", OleDbType.Date).Value = tanggalarchive.Value
proses.ExecuteNonQuery(str)
command.ExecuteNonQuery()
MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
Call bersih()
Call data_penjualan()
Else

command.ExecuteNonQuery() *****error in here

OriginalGriff 6-Sep-18 6:20am

Well what did you expect?
You try and do the same query twice!
Once without any parameter data, and once with...

Member 13974630 6-Sep-18 5:37am

edit, now already be saved, but data is not entered in access, how do I make the data appear in the database? can you help me??

Maciej Los · Answer 2 · 2018-09-05T21:59:00

In addition to OriginalGriff's solution, i'd recommend to separate your into DAL[^] and BLL[^].
The reason why you have to do that is described in referenced articles.

To find out why you get such of error, you have to debug your programme: Learn to debug using the Visual Studio debugger - Visual Studio | Microsoft Docs[^]

I guess you've got oledbexception (0x80040e10), which means you did not provide parameter, which is obligatory. See: OLEDB Errors[^]

Few notes about your code:
1. Your code does not return the result of ExecutNonQuery method, so have no idea if this method has been succeeded.
2. Your code does not have an error handler... It's good programming practice to catch errors by code. Take a look at below code. It's not perfect, but it's good for starters.

VB.NET

Function GetEmployee(Optional ByVal empIdToFind As Integer = 111) Ad DataTable
    Dim dt As DataTable = New DataTable()
	Try
		Dim sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FullFileNameOfMsAccessDatabase];Persist Security Info =False;"
		Dim oRdr As OleDbDataReader
			
		Using oConn As OleDbConnection = New OleDbConnection(sConn)
			oConn.Open()
			Using oComm AS OleDbCommand = New OleDbCommand()
				oComm.Connection = oConn
				oComm.CommandType = CommandType.Text
				'using named paramters
				oComm.CommandText =  "SELECT * FROM MyUsers WHERE EmpId=@empid;"
				oComm.Parameters.AddWithValue("@empid", empIdToFind)
				oComm.ExecuteNonQuery()
				oRdr = oComm.ExecuteReader()
				dt.Load(oRdr)

			End Using
			oConn.Close()
		End Using
	
	Catch ex As Exception
		Console.WriteLine(ex.Message)
	
	End Try
    Return dt
End Function

Above function requires empIdToFind input parameter to be able to return DataTable (complete record).
Note: the order of named/non-named parameters does matter!
3. This code:

If storage.SelectedIndex = 1 Then
    groupordner.Visible = True
    grouplemari.Visible = False
Else
    If storage.SelectedIndex = 2 Then
        groupordner.Visible = False
        grouplemari.Visible = True
    End If
End If

can be simplified into form: