CODEWITHSUNDEEP
sqlvb.net
Dunkin Ni
Dunkin Ni

Reputation: 23

Parameterized SQL update query doesn't work

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()

Upvotes: 2

Views: 230

Answers (1)

Steve
Steve

Reputation: 216358

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

Upvotes: 1

Related Questions