ExecuteNonQuery inside loop
Asked Answered
V

7

10

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!

Vincenty answered 5/1, 2012 at 17:41 Comment(0)
B
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.

Borchert answered 5/1, 2012 at 17:42 Comment(2)
Thanks - I've been scratching my head on that one for AGES! Takes you guys seconds to figure out. Thanks very much everyone.Vincenty
@Vincenty - no problem! BTW, I would suggest updating your code to use proper disposition with a using statement (as suggested by Austin Salonen's post).Borchert
M
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();
Murine answered 1/1, 2014 at 9:56 Comment(0)
C
3

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

}
Cigarillo answered 5/1, 2012 at 17:46 Comment(0)
R
1

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 
        } 
    } 
Ruhr answered 5/1, 2012 at 17:44 Comment(0)
M
1

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
        }
    }
Misprize answered 5/1, 2012 at 17:45 Comment(0)
M
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

Marcin answered 5/1, 2012 at 17:45 Comment(0)
L
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.

Lunula answered 24/10, 2016 at 17:5 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.