[clee06]

Hey guys, I have an array that contains the employees' name, favorite food, color, and hobby (coma separated).

so far this is what I came up with.. it's very simple. I was just wondering if i can do multiple insert statement at once? like run "INSERT INTO..." 3x in the temp_query string and just have it run once.

            'temp_array() = {"joe, pizza, blue, football",
            '             "katy, toca, purple, tennis",
            '             "mike, steak, red, basketball"}

            For i As Integer = 0 To temp_array.Length - 1

                temp_dbcon = New MySqlConnection(temp_connection)
                temp_query = "INSERT INTO  " & shipping_method & " VALUES(" & temp_array(i) & ")"
                temp_cmd = New MySqlCommand(temp_query, temp_dbcon)
                temp_dbcon.Open()
                temp_dr = temp_cmd.ExecuteReader
                temp_dbcon.Close()

            Next

[CharlieMay]

Yes but...
when you are inserting the item, a string will need to be enclosed in apostrophes (')

However, to save you the time and get you going more correctly, you need to set a parameter in your statement and use the array element as the value for the parameter.


I also recommend that you specify both the Fields and Values in your insert

This would mean your temp_query would be something like:

...
temp_query = INSERT INTO myTable (Field1) VALUES (@field1)
temp_cmd = New MySqlCommand(temp_query, temp_dbcon)
temp_cmd.Parameters.AddWithValue("@field1", temp_array(i))
temp_cmd.ExecuteNonQuery
...

Notice ExecuteNonQuery. A query is a SELECT statement and uses the Reader, INSERT UPDATE and DELETE are NonQuery so you will need to make that change for the INSERT to work.

Now the parameter will take the raw element of the array and place the proper enclosing marks as it deems necessary. This makes for a lot safer statement against SQL injection and alleviates a lot of the problems with enclosing properly.

I haven't done a lot with MySQL so I'm not sure that it will take parameters specified this way so you may have to look it up for the correct syntax.

Also Please Note: Using a variable for your table name is very unsafe. A simple injected statement from someone can wreak havoc on your database. Try to alleviate any concatenation of strings into your statements.


Also don't open and close your connection with every loop

Open Connection
Start loop
Set INSERT statement and execute it
end loop
Close Connection

This post has been edited by CharlieMay: 03 October 2012 - 08:12 AM

[clee06]

Thank you so much Charlie, for the explanation. It didn't occur to me that there is potential injection with my codes. I did put the close connection out side the loop at first, but then my TRY/CATCH got a message saying something like "too much open connection" when I got an error.

[CharlieMay]

Try catch would catch that because every iteration of the loop would perform an open but you weren't closing it until the loop finished giving you a "Connection is already open"

With both in the loop like you had, at least the connection was being closed before it was being opened again, it was just a lot of overhead opening and closing with each insert. Outside the loop, opens the connection, allows you to perform the various transactions and then closes the conenction when the loop is finished.