3 Replies - 419 Views - Last Post: 03 October 2012 - 09:25 AM Rate Topic: -----

#1 clee06  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 66
  • Joined: 01-June 10

insert to sql table

Posted 03 October 2012 - 07:33 AM

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



Is This A Good Question/Topic? 0
  • +

Replies To: insert to sql table

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: insert to sql table

Posted 03 October 2012 - 08:03 AM

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

Was This Post Helpful? 1
  • +
  • -

#3 clee06  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 66
  • Joined: 01-June 10

Re: insert to sql table

Posted 03 October 2012 - 08:42 AM

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

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: insert to sql table

Posted 03 October 2012 - 09:25 AM

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

Page 1 of 1