Solved

update multiple records mysql vbnet

Posted on 2006-11-06
12
963 Views
Last Modified: 2012-06-27
I cant get my function to update more then one record at a time

EG:

this will work: UPDATE tableName SET field='blah1' WHERE id=1;
However this wont: UPDATE tableName SET field='blah1' WHERE id=1; UPDATE tableName SET field='blah2' WHERE id=2;

This is the function, Im thinking its to do with the executeNonQuery() method.

Public Function executeNonQuerySQL(ByVal statement As String, ByVal errorType As String, ByVal login As String) As Boolean

        Try
            ' open connection
            sqlConnection.Open()
            ' now we got here lets get another record count, this time on our list count!
            dbDataAdapter.InsertCommand = New OdbcCommand
            dbDataAdapter.InsertCommand.Connection = sqlConnection
            dbDataAdapter.InsertCommand.CommandText = statement

            ' execute!
            dbDataAdapter.InsertCommand.ExecuteNonQuery()

            ' close connection
            sqlConnection.Close()

        Catch ex As Exception
            sqlConnection.Close()
            MsgBox(errorType & ControlChars.CrLf & ControlChars.CrLf & "statement: " & statement _
                & ControlChars.CrLf & ex.Message, MsgBoxStyle.Exclamation, "Whoops!")
            ' and send error to IT
            errorEmail(errorType & ControlChars.CrLf & ControlChars.CrLf & "statement: " & statement _
                    & ControlChars.CrLf & ex.Message, login)
            Exit Function
        End Try

 End Function

It works fine for single updates, just not multiple. The same multiple record update command (the one compiled from my vb application) works fine when tried directly to mysql (through phpmyadmin), just not through VB net.
Cheers
0
Comment
Question by:bedind
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17886339
This is because through the connector it will not let you run multiple queries (good for preventing sql injection)

to replicate what you have above you could do (in one query)

Update tableNAme set field = case when id=1 then 'blah1' when id=2 then 'blah2' end where id in (1,2)
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17886340
otherwise you would have to loop and run each distinct statement in turn
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17886343
what driver do you use to connect to mysql?
0
 
LVL 3

Author Comment

by:bedind
ID: 17886386
I use the straight mysql odbc connector, windows applciation.

Do I need to rewrite the function? is there a way to overide the limitations within vbnet?
I figured it was a restriction in vbnet.

"...Update tableNAme set field = case when id=1 then 'blah1' when id=2 then 'blah2' end where id in (1,2).."

Im unsure of this...is this SQL or just pseudospeak?

Cheers guys
0
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 35 total points
ID: 17886403
Hi,

This is sql - not pseudospeak.  for mysql case definitions please see http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Alternativley you could use the same function you have and just call it more than once each time with your separate queries.

You could create another function with the same name that takes instead of a string an array of strings.

You could then pass each of the strings to the database using the same logic.
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17886654
another solution involving spending money is to buy a managed .NET connector, like CoreLab MySQL connector, which provides a control like MySQLScript which allows you to send multiple queries. but it would be much like developing your own custom function that will split the input string using the ";" as a delimiter and loop the commands into the database.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Accepted Solution

by:
Sancler earned 35 total points
ID: 17888204
Can I ask why you are using ExecuteNonQuery on a dataadapter's InsertCommand?  The more normal approach if you wanted to do your own updating with ExecuteNonQuery would be

            ' open connection
            sqlConnection.Open()
            ' now we got here lets get another record count, this time on our list count!
            Dim cmd As New OdbcCommand
            cmd.Connection = sqlConnection
            cmd.CommandText = statement

            ' execute!
            cmd.ExecuteNonQuery()

            ' close connection
            sqlConnection.Close()

Although your approach amounts, at the end of the day, to the same thing it indicates that you do have a dataadapter.  And a dataadapter is, in effect, a wrapper for multiple updates.  So I wonder why you are just not using that?

I suppose it may be that the information from the update is not coming from any table, or something like that.  But I'd be interested to know.

Roger
0
 
LVL 7

Assisted Solution

by:foobarr
foobarr earned 30 total points
ID: 17893587
Hi,

I agree with Sancler

one thing that nobody has mentioned is you may want to include your multiple updates within a transaction.  MySQL supports transactions well I know version 5.0 does.  

Transaction will be helpful in terms of your db consistency.  Because if you run your first update successfully but for some reason your 2nd update fails you should remove the first update.  BUT if your updates are completely independent of each other then you will not need to use a transaction

normally if both updates completed then you can "commit" the transaction else if anything failed you would "rollback" the transaction


mysqltransaciton.open

run your query code

if everything passes
mysqltransaction.commit

if something failed
mysqltransaction.rollback


if you open up the .net section of the mysql manual they include transaction examples


0
 
LVL 3

Author Comment

by:bedind
ID: 17893933

Goodaye Guys, Thanks for the replies.

The reason I used the data adapter, insertCommand method is because I simply started with it and never researched it properly.
Thanks for that though, I changed it and it seems quicker.

I still have the multiple queries error though. Works fine on one update at a time.

Previously I would open the connection, do the multiple commands in a loop, close it.

Because its in a function now, If I open, do one update, close multiple times it hammers the database and having done that before, sometimes crashes.

I can make it so that the multiple query command uses an array or something as suggested, but im still at a loss why there is the restriction. Maybe there isnt and it actually has to run one at a time, not a multiple query (eg, when phpmyadmin sees the semi colon, it parses each update seperately)

In that case im staring at walls!

If there is no other ideas and this is the case, ill share some points out and thanks once again for the help

0
 
LVL 34

Expert Comment

by:Sancler
ID: 17894137
Now you've changed to a dataadapter for the update, what do you use for its .Update command?

If we assume

(a) that it's .Select Command is (using terms from your sample SQL) "SELECT field id FROM tablename"

(b) that id is a Primary Key from your database table that the dataadapter recognises

(c) that you use that dataadapter to fill a datatable in your app called myDataTable and

(d) that the changes that you want to get back to the database have been made in that datatable

then what you need to do is

(1) some time after you have given the dataadapter its select command use a commandbuilder, just once, to create the update commands you need

    Dim cb As OdbcCommandBuilder = New OdbcCommandBuilder(dbDataAdapter)

(2) replace the code in your function with

    dbDataAdapter.Update(myDataTable)

The dataadapter (or, in fact, the commandbuilder on its behalf) will then cycle through the rows in myDataTable looking for ones which have been altered and, for each one that it finds, will use a parameterised query to do the necessary update.  As I said earlier "a dataadapter is, in effect, a wrapper for multiple updates".

Is that approach worth a try in your situation?

Roger
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17894384
Typo ;-(

For
>>
(a) that it's .Select Command is (using terms from your sample SQL) "SELECT field id FROM tablename"
<<

read
>>
(a) that its .Select Command is (using terms from your sample SQL) "SELECT field, id FROM tablename"
<<

Roger
0
 
LVL 3

Author Comment

by:bedind
ID: 17910418
thanks for the help, I went with the arrays for now, very much because it was the easiest solution and I thank the other guys for the help in fixing some code and alleviating me to some nifty SQL functionality!

Thanks guys
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now