VB.NET: Generating MySQL Query Using Variables

Question

I have written the code below to update my table, but it doesn't work.

sqlstr = "UPDATE Student SET " & S & " = @field1, " & L & " = @field2, " & R & " =@field3, " & W & "=@field4 WHERE Code='" & StdID & "'"
DBCmd = New MySql.Data.MySqlClient.MySqlCommand(sqlstr, DBConn)
With DBCmd
        .Parameters.AddWithValue("@field1", CB_S.SelectedItem)
        .Parameters.AddWithValue("@field2", CB_L.SelectedItem)
        .Parameters.AddWithValue("@field3", CB_R.SelectedItem)
        .Parameters.AddWithValue("@field4", CB_W.SelectedItem)
    End With
    DBCmd.Dispose()

Where S, L, R and W are strings: S1, L1, R1, W1. And StdID is an integer. CB_S, CB_L, CB_R and CB_W are comboboxes.

Could anyone tell me what is wrong?

John Woo · Accepted Answer · 2013-04-07 17:14:27Z

2

you did not open the connection and call ExecuteNonQUery(). Also parameterized the value for Code

sqlstr = "UPDATE Student SET " & S & " = @field1, " & L & " = @field2, " & R & " =@field3, " & W & "=@field4 WHERE Code=@code"
DBCmd = New MySql.Data.MySqlClient.MySqlCommand(sqlstr, DBConn)
With DBCmd
        .Parameters.AddWithValue("@field1", CB_S.SelectedItem)
        .Parameters.AddWithValue("@field2", CB_L.SelectedItem)
        .Parameters.AddWithValue("@field3", CB_R.SelectedItem)
        .Parameters.AddWithValue("@field4", CB_W.SelectedItem)
        .Parameters.AddWithValue("@code", StdID)
End With
DBConn.Open()
DBCmd.ExecuteNonQuery()

answered Apr 7, 2013 at 17:14

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

1 Comment

Parsa Dorbeigi Over a year ago

Oh, That's embarrassing!! Thanks!

Collectives™ on Stack Overflow

VB.NET: Generating MySQL Query Using Variables

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