ExecuteNonQuery inside loop

Question

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!

Vaibhav Jain · Answer 1 · 2020-10-13T08:11:24.300

7

Tested & simple solution. If you are using parameters in loop You need to clear the parameters after execution of query. So you can use that

cmd3.executeNonQuery();
cmd3.Parameters.Clear();

edited Oct 13 '20 at 08:11

answered Jan 01 '14 at 09:56

Vaibhav Jain

1,939
26
36

score 7 · Accepted Answer · answered Jan 05 '12 at 17:42

7

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.

answered Jan 05 '12 at 17:42

Tim M.

53,671
14
120
163

Thanks - I've been scratching my head on that one for AGES! Takes you guys seconds to figure out. Thanks very much everyone. – Dan Jan 05 '12 at 17:59
@Dan - no problem! BTW, I would suggest updating your code to use proper disposition with a `using` statement (as suggested by Austin Salonen's post). – Tim M. Jan 06 '12 at 04:31

score 3 · Answer 3 · answered Jan 05 '12 at 17:46

This is untested but should work as an alternative. Just add it once and continually update 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();
        }
....

As an aside, your SqlCommand should be within a using block as well as your SqlConnection. The full code isn't shown so I don't know if your connection is actually done in such as way.

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

}

score 1 · Answer 4 · answered Jan 05 '12 at 17:44

What you are doing is adding a parameter ever loop iteration. In the below code, it adds the parameter once, and just modifies the single parameter's value. Try this:

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 
        } 
    }

score 1 · Answer 5 · answered Jan 05 '12 at 17:45

Yes, don't add the parameter in the loop, only 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
        }
    }

score 1 · Answer 6 · answered Jan 05 '12 at 17:45

1

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

// don't keep adding it in the loop either

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

No @needed in the parameters collection whne using sql server

answered Jan 05 '12 at 17:45

Tony Hopkinson

20,172
3
31
39

score 0 · Answer 7 · answered Oct 24 '16 at 17:05

0

Another solution for those who are looking at this thread. Create two connections. One for your loop and another to send your NonQuery statements. This worked for me.

answered Oct 24 '16 at 17:05

eDriven_Levar

361
3
10

ExecuteNonQuery inside loop

7 Answers7

Linked