OleDbException was unhandled; Error in .ExecuteNonQuery in vb

Question

Im just a newbie in vb.net But what's wrong with my code, the error exist in cmd.ExecuteNonQuery and it says No value given for one or more required parameters. How shall I deal with this, please help. I already checked all the forums that contains this problem but none of them suits my code problem. PLEASE help.

Dim con1 As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Admin\Documents\Visual Studio 2010\Projects\AG Hostel Room Management System1\AG Hostel Room Management System1\AGHostelRooms.accdb")
    Dim str As String
    str = "UPDATE Rooms SET [Room Number] = ? , [Room Type] = ?, Price = ?" & _
          " WHERE [Room Number] = ?"
    con1.Open()
    Using cmd = New OleDbCommand(str, con1)
        cmd.Parameters.AddWithValue("@p1", TextBox2.Text)
        cmd.Parameters.AddWithValue("@p2", TextBox3.Text)
        cmd.Parameters.AddWithValue("@p3", TextBox1.Text)
        cmd.ExecuteNonQuery()
        con1.Close()
    End Using

Count the question marks in your query and then count the parameters in your code..... — Steve, Mar 12 '16 at 13:05

Steve · Accepted Answer · 2016-03-13T09:29:45.483

You are missing a parameter in your parameters collection. There are four parameters placeholders (the ?) but you add only three parameters to the collection.
You need to add the last one, the one used in the WHERE clause.

But a part from this trivial error, you need to pay a lot of attention when using AddWithValue because that method has unexpected behavior when you don't specify correctly the parameter value.

In your case, you pass always strings as the parameter value, but I am pretty sure that your database fields are not all strings.
In this context AddWithValue has no way to know if a particular parameter will be used to update a numeric, a datetime or a boolean field and thus looks at the datatype of the value to determine the datatype of the parameter.

This could easily cause a DataType Mismatch exception or wrong values inserted, in particular with dates and decimal values. These values are sent to the database engine represented as strings in the localization of the client program but they could be incompatible with the code used by the database to convert the strings back to the correct type expected by the underlying columns.

See more info in this blog article Can we stop to use AddWithValue already?

Better to use always the specific implementation of the Parameters.Add method that allows you to specify the exact datatype of the receiving column.
Of course the parameter value should be of the correct datatype.

Dim str As String
str = "UPDATE Rooms SET [Room Type] = ?, Price = ? WHERE [Room Number] = ?"
Using con1 = New OleDbConnection("......")
Using cmd = New OleDbCommand(str, con1)
    con1.Open()
    cmd.Parameters.Add("@p1", OleDbType.VarWChar).Value = TextBox3.Text
    cmd.Parameters.Add("@p2", OleDbType.Decimal).Value = Convert.ToDecimal(textBox1.Text)
    cmd.Parameters.Add("@p3", OleDbType.Integer).Value = Convert.ToInt32(textBox2.Text)
    cmd.ExecuteNonQuery()
End Using
End Using

Notice that I have changed your query to work with just three parameters because, as far as I know, there is no need to change the Room Number value to the same value used in the WHERE clause

Of course the exact OleDbType to use in each Add should match the real datatype of your column.

OleDbException was unhandled; Error in .ExecuteNonQuery in vb

1 Answers1