UPDATE MySQL using VB.NET

Question

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

Anton Gogolev · Accepted Answer · 2011-03-29 10:15:20Z

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.

answered Mar 29, 2011 at 10:15

Anton Gogolev

116k39 gold badges204 silver badges293 bronze badges

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

4 Comments

BooMGiRL Over a year ago

I was able to do it like this:

BooMGiRL Over a year ago

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 & "'

invert Over a year ago

@Boomgirl please click to accept the answer as correct if it solved your problem.

invert Over a year ago

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 :-) stackoverflow.com/questions/652978/…

Collectives™ on Stack Overflow

UPDATE MySQL using VB.NET

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related