Dan Dan - 1 year ago 108
C# Question

ExecuteNonQuery inside loop

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

It works when I hard code the values like this:

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 parameterised queries it doesn't work - even if I hard code a value into the parameterised query like this:

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'm going wrong here?

Many thanks!

Answer Source

It looks like you are adding to the command's parameter collection over and over. Clear it with each iteration.

I would also suggest throwing the actual exception so you can see what the problem is.