[BooMGiRL]

Hi all. 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

[Vishu Sukhdev]

hi ,
You can try this

        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 & "' ;"
            SQL &= "UPDATE employees SET emprole2 = '" & val3 & "' WHERE emprole2 = '" & val2 & "' ;"
            SQL &= "UPDATE employees SET emprole3 = '" & val4 & "' WHERE emprole3 = '" & val3 & "'"
            myCommand.CommandText = SQL
            myCommand.ExecuteNonQuery()

	End If

[BooMGiRL]

Hi Vishi. I tried your code... It doesn't work. It compiles without error, but does not change the values in my MySQL database.


EDIT: It does work... I forgot the & character in the second etc lines. And WOW it is faster. Only problem is it's a little slow just the first time I run the code. Then the second, third, fourth etc. time I run the same code it's really fast. How come?

This post has been edited by BooMGiRL: 29 March 2011 - 03:04 AM

[modi123_1]

If you have three separate where clauses you'll need three separate statements. Trying to insert some sort of conditional 'where' is pretty much a bad idea.