ExecuteNonQuery inner loop

Question

I am trying to insert a database record inside a loop in C #.

It works when I hard code the following values:

string query3 = "INSERT INTO furniture (room_id,member_id) VALUES (222,333);"; SqlCommand cmd3 = new SqlCommand(query3, sqlConnection3); sqlConnection3.Open(); for (int i = 0; i < arrItemsPlanner.Length; i++) { try { cmd3.ExecuteNonQuery(); } catch { return "Error: Item could not be saved"; } finally { //Fail } } 

But when I use parameterized queries, it does not work - even if I hard-code the value into a parameterized query, for example:

  string query3 = "INSERT INTO furniture (room_id,member_id) VALUES (@room_id,333);"; SqlCommand cmd3 = new SqlCommand(query3, sqlConnection3); sqlConnection3.Open(); for (int i = 0; i < arrItemsPlanner.Length; i++) { try { cmd3.Parameters.Add("@room_id", System.Data.SqlDbType.Int); cmd3.Parameters["@room_id"].Value = 222; cmd3.ExecuteNonQuery(); } catch { return "Error: Item could not be saved"; } finally { //Fail } } 

Can anyone see where I made a mistake here?

Many thanks!

Answer 1

It looks like you are adding a collection of command parameters over and over again. Clean it with every iteration.

I also suggest throwing the actual exception so that you can understand what the problem is.

Answer 2

I am telling you a simple solution, and it definitely works. If you use parameters in a loop, you need to clear the parameters after executing the query. So you can use this

 cmd3.executeNonQuery(); cmd3.parameters.clear(); 

Answer 3

This is untested, but should work as an alternative. Just add it once and keep updating its value.

 .... cmd3.Parameters.Add("@room_id", System.Data.SqlDbType.Int); for (int i = 0; i < arrItemsPlanner.Length; i++) { try { cmd3.Parameters["@room_id"].Value = 222; cmd3.ExecuteNonQuery(); } .... 

Aside, your SqlCommand should be inside the using block, as well as your SqlConnection . The full code is not displayed, so I don’t know if your connection is really made this way.

 using (var conn = new SqlConnection(...)) using (var cmd = new SqlCommand(..., conn)) { } 

Answer 4

What you do is adding parameter iteration always. In the code below, it adds a parameter once and simply changes one parameter value. Try the following:

 string query3 = "INSERT INTO furniture (room_id,member_id) VALUES (@room_id,333);"; SqlCommand cmd3 = new SqlCommand(query3, sqlConnection3); cmd3.Parameters.Add("@room_id", SqlDbType.Int); sqlConnection3.Open(); for (int i = 0; i < arrItemsPlanner.Length; i++) { try { cmd3.Parameters["@room_id"].Value = 222; cmd3.ExecuteNonQuery(); } catch { return "Error: Item could not be saved"; } finally { //Fail } } 

Answer 5

Yes, do not add the parameter to the loop, just set its value:

 string query3 = "INSERT INTO furniture (room_id,member_id) VALUES (@room_id,333);"; SqlCommand cmd3 = new SqlCommand(query3, sqlConnection3); sqlConnection3.Open(); cmd3.Parameters.Add("@room_id", System.Data.SqlDbType.Int); for (int i = 0; i < arrItemsPlanner.Length; i++) { try { cmd3.Parameters["@room_id"].Value = 222; cmd3.ExecuteNonQuery(); } catch { return "Error: Item could not be saved"; } finally { //Fail } } 

Answer 6

 cmd3.Parameters.Add("room_id", System.Data.SqlDbType.Int); 

// do not add it to the loop either

 cmd3.Parameters["room_id"].Value = 222; 

Not in the parameter collection using sql server

Answer 7

Another solution for those who are looking at this topic. Create two connections. One for your loop and one for sending your NonQuery instructions. It worked for me.