Multiple requests to mysql

[jakub.peciak]

Hi,
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
mySqlConnection.Close()

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?

[chocomilk]

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
    con.Open()
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)
    cmd1.ExecuteNonQuery
        msgbox("query 1 success")
    cmd2 = New SqlCommand(sqlquery2,con)
    cmd2.ExecuteNonQuery
        msgbox("query 2 success")
con.Close()

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

[Reverend Jim]

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.