I have following code.

Dim myData1 As MySqlDataReader
Dim sqlquary1 = "INSERT INTO  table1 (text1) Values (Value1); INSERT INTO  table2 (text2) Values (Value2); INSERT INTO  table3 (text3) Values (Value3)"

Dim command1 As New MySqlCommand
command1.Connection = mySqlConnection

command1.CommandText = sqlquary1
myData1 = command1.ExecuteReader

I need to send few request to the mysql in the same time but I need to manage errors as well. I was using ; to seperate queries like:

"INSERT INTO  table1 (text1) Values (Value1); INSERT INTO  table2 (text2) Values (Value2); INSERT INTO  table3 (text3) Values (Value3)"

but when error apear in first "INSERT" then db returned error and other INSERS are skipped.
Is there a easy way to open connection and then send the all INSERTS and close connection?

Recommended Answers

All 2 Replies

Try to make a separate query strings on every SQL Statement, and in every query strings, try to use another command

like this:

If con.ConnectionState = ConnectionState.Closed then
End If

    Dim sqlquery1 As String = "INSERT INTO Table1(text1) VALUES (textValue1)"
    Dim sqlquery2 As String = "INSERT INTO Table2(text2) VALUES (textValue2)"

    cmd1 = New SqlCommand(sqlquery1,con)
        msgbox("query 1 success")
    cmd2 = New SqlCommand(sqlquery2,con)
        msgbox("query 2 success")

And everytime you code a query, try so send a pop up message to confirm if query is successfully insert in the database
just comment or delete the message code if your done with the code.

Note: everytime you insert or update a query in DB, we use cmd.ExecuteNonQuery,
cmd.ExecuteReader, for what i know is just reading the data inside the database.use for select queries most of the time

If you need an "all or nothing" action where if any insert fails then they all fail you will have to create a Transaction and use a Try/Catch so you can roll back the entire transaction on an error.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.