CODEWITHSUNDEEP
c#sqlcommandparameterized-query
Dan
Dan

Reputation: 5986

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!

Upvotes: 10

Views: 12724

Answers (7)

Vaibhav Jain
Vaibhav Jain

Reputation: 2407

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();

Upvotes: 7

eDriven_Levar
eDriven_Levar

Reputation: 396

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.

Upvotes: 0

Austin Salonen
Austin Salonen

Reputation: 50235

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))
{

}

Upvotes: 3

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

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

Upvotes: 1

competent_tech
competent_tech

Reputation: 44941

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

Upvotes: 1

user596075
user596075

Reputation:

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

Upvotes: 1

Tim M.
Tim M.

Reputation: 54377

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.

Upvotes: 7

Related Questions