Dan Dan - 1 month ago 20
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

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.

Comments