1

I am using VB.NET with a MySQL database. I want to update this code to do it all in ONE SQL instead of THREE. Anyone know how?

Here's the code I'm using, works fine but too slow with multiple lines...

If count3 = "1" Then

    Dim myCommand As New MySqlCommand
    Dim myAdapter As New MySqlDataAdapter
    Dim SQL As String
    myCommand.Connection = conn
    myAdapter.SelectCommand = myCommand
    SQL = "UPDATE employees SET emprole1 = '" & val2 & "' WHERE emprole1 = '" & val1 & "'"
    myCommand.CommandText = SQL
    myCommand.ExecuteNonQuery()
    SQL = "UPDATE employees SET emprole2 = '" & val3 & "' WHERE emprole2 = '" & val2 & "'"
    myCommand.CommandText = SQL
    myCommand.ExecuteNonQuery()
    SQL = "UPDATE employees SET emprole3 = '" & val4 & "' WHERE emprole3 = '" & val3 & "'"
    myCommand.CommandText = SQL
    myCommand.ExecuteNonQuery()

End If
BooMGiRL
  • 11
  • 1
  • 2

1 Answers1

1

This is just my guess, but you could try gluing all three SQL statements together; pretty much like

SQL = "update employees set ... ; update employees set ... ;";

Note the semicolon that separates statements.

Anton Gogolev
  • 113,561
  • 39
  • 200
  • 288
  • I was able to do it like this: – BooMGiRL Mar 29 '11 at 10:16
  • SQL = "UPDATE employees SET emprole1 = '" & val2 & "' WHERE emprole1 = '" & val1 & "' ;" SQL &= "UPDATE employees SET emprole2 = '" & val3 & "' WHERE emprole2 = '" & val2 & "' ;" SQL &= "UPDATE employees SET emprole3 = '" & val4 & "' WHERE emprole3 = '" & val3 & "' – BooMGiRL Mar 29 '11 at 10:17
  • @Boomgirl please click to accept the answer as correct if it solved your problem. – invert Mar 29 '11 at 10:35
  • 2
    I really recommend you also use parameterized queries, instead of doing things like `emprole = " & var`, that is _exactly_ what SQL injection attacks rely on. See here for how its done :-) http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c – invert Mar 29 '11 at 10:48