Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

UPDATE MySQL using VB.NET

BooMGiRLBooMGiRL Posts: 3Member
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...

[code] 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[/code]

Comments

  • DaiMitnickDaiMitnick Posts: 77Member
    Because you are trying to change values in different columns, based on the values in other various columns, you may find the way you have now is the simplest, however you can use some case statements to test if it's any quicker, something like below.

    [code]
    UPDATE categories
    SET display_order = CASE id
    WHEN 1 THEN 3
    WHEN 2 THEN 4
    WHEN 3 THEN 5
    END,
    title = CASE id
    WHEN 1 THEN 'New Title 1'
    WHEN 2 THEN 'New Title 2'
    WHEN 3 THEN 'New Title 3'
    END
    WHERE id IN (1,2,3)
    [/code]

    Go to this site to understand it, it is where I took above code from:

    http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/

    HTH, Dai


    ------------------------------------------
    Do or do not, there is no try. |
    ------------------------------------------
Sign In or Register to comment.