3 Replies - 4829 Views - Last Post: 29 March 2011 - 06:49 AM Rate Topic: -----

#1 BooMGiRL  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 27-February 11

UPDATE MySQL using VB.NET

Posted 29 March 2011 - 02:22 AM

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



Is This A Good Question/Topic? 0
  • +

Replies To: UPDATE MySQL using VB.NET

#2 Vishu Sukhdev  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 150
  • Joined: 19-February 10

Re: UPDATE MySQL using VB.NET

Posted 29 March 2011 - 02:41 AM

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


Was This Post Helpful? 0
  • +
  • -

#3 BooMGiRL  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 27-February 11

Re: UPDATE MySQL using VB.NET

Posted 29 March 2011 - 02:58 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9258
  • View blog
  • Posts: 34,735
  • Joined: 12-June 08

Re: UPDATE MySQL using VB.NET

Posted 29 March 2011 - 06:49 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1