Parameterized SQL update query doesn't work

Question

I am already stuck for hours and I don't know what to do.
My code doesn't work in updating student records using parameterized SQL query in VB.Net.

    Dim result As Integer
    cmd = New OleDbCommand("UPDATE students SET Student_Name=@name, Address=@address, Contact_No=@contact WHERE ID_Number=@id_number;", conn)
    conn.Open()
    cmd.Connection = conn
    cmd.Parameters.AddWithValue("@id_number", txt_IDNumber.Text)
    cmd.Parameters.AddWithValue("@name", txt_Name.Text.ToString)
    cmd.Parameters.AddWithValue("@address", txt_Address.ToString)
    cmd.Parameters.AddWithValue("@contact", txt_ContactNo.Text.ToString)
    result = cmd.ExecuteNonQuery()
    If result = 1 Then
        MsgBox("Changes updated successfully.", vbInformation, "Message")
    Else
        MsgBox("Update unsuccessful.", vbCritical, "Message")
    End If
    conn.Close()

Please refer this link. Also txt_Address.ToString should be txt_Address.Text.ToString — Arulkumar
– Arulkumar, Commented Apr 7, 2017 at 7:48
@Arulkumar correct, but it depends ultimately on the underlying driver. Access, for example accepts the syntax with the @ — Steve
– Steve, Commented Apr 7, 2017 at 7:53
Oops..I didn't see that. Thanks for the quick observation mate. It helps. — Dunkin Ni
– Dunkin Ni, Commented Apr 7, 2017 at 8:00

Steve · Accepted Answer · 2017-04-07 07:48:25Z

1

OleDb doesn't recognize parameters by their name. In OleDb parameters should be added to the parameters collection in the same order in which you have listed them in the commandtext.

This means that your code tries to update a record whose ID_Number is equal to the content of the txt_ContactNo

Just move the parameter @id_number after adding the first three

' No need to use ToString on a property of type string'
cmd.Parameters.AddWithValue("@name", txt_Name.Text)
cmd.Parameters.AddWithValue("@address", txt_Address.Text)
cmd.Parameters.AddWithValue("@contact", txt_ContactNo.Text)
cmd.Parameters.AddWithValue("@id_number", txt_IDNumber.Text)

As a side note, please be aware of the problems of AddWithValue. It is an handy shortcut but you could pay a price for it

edited Apr 7, 2017 at 7:48

answered Apr 7, 2017 at 7:43

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dunkin Ni Over a year ago

Wow! It works!..That was fast. Well, Honestly I didn't know that in order to work, it should be in the same order as what I have listed in the CommandText. Thank you very much for your time and help. Cheers!

Collectives™ on Stack Overflow

Parameterized SQL update query doesn't work

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related