CODEWITHSUNDEEP
c#sql
Matt
Matt

Reputation: 361

Loop SQL Insert from C# Array

I have an array that holds a product id and the quantity, acting as a shopping basket.

My present loop allows me to insert the data into my table when the array count is 1:

var p = Basket.arrayList;

    for (int i = 0; i < p.Count; i++)
        // Loop through List 
    {
        var ProductId = p[i][0];
        var Quantity = p[i][1];
        itemsQueryCommand.CommandText = "INSERT INTO tOrderItems (orderId, name, quantity) VALUES (@OrderId, @name, @quantity )";
        itemsQueryCommand.Parameters.AddWithValue("@OrderId", id);
        itemsQueryCommand.Parameters.AddWithValue("@name", ProductId);
        itemsQueryCommand.Parameters.AddWithValue("@quantity", Quantity);

        itemsQueryCommand.ExecuteNonQuery();
    }

If the array holds any more than 1 it throws an error saying; "The variable name '@OrderId' has already been declared. Variable names must be unique within a query batch or stored procedure."

I really don't know how to fix this... Please help

Upvotes: 0

Views: 1487

Answers (3)

Noctis
Noctis

Reputation: 11763

Like the other answers and your error suggests, you're reusing a parameter value (so your '@OrderId' is added more than once).

Either create a new command everytime (serman22 solution), or reuse the command value only (like hogan suggested).

In either case, I'd suggest you do some reading about the sql commands and C#, to refresh them in your mind.

Upvotes: 0

Selman Gen&#231;
Selman Gen&#231;

Reputation: 101691

Dispose your command with using statement on each iteration and create a new one:

for (int i = 0; i < p.Count; i++)
{
    var ProductId = p[i][0];
    var Quantity = p[i][1];
    using(var cmd = new SqlCommand())
    {
        cmd.Connection = connection; // <-- don't forget to set connection
        cmd.CommandText = "INSERT INTO tOrderItems (orderId, name, quantity) VALUES (@OrderId, @name, @quantity )";
        cmd.Parameters.AddWithValue("@OrderId", id);
        cmd.Parameters.AddWithValue("@name", ProductId);
        cmd.Parameters.AddWithValue("@quantity", Quantity);
        cmd.ExecuteNonQuery();
    }

}

Upvotes: 3

Hogan
Hogan

Reputation: 70523

Something like this:

var p = Basket.arrayList;

itemsQueryCommand.CommandText = "INSERT INTO tOrderItems (orderId, name, quantity) VALUES (@OrderId, @name, @quantity )";
itemsQueryCommand.Parameters.Add("@OrderId");
itemsQueryCommand.Parameters.Add("@name");
itemsQueryCommand.Parameters.Add("@quantity");


for (int i = 0; i < p.Count; i++)
    // Loop through List 
{
    itemsQueryCommand.Parameters["@OrderId"] = id;
    itemsQueryCommand.Parameters["@name"] =  p[i][0]; // ProductId;
    itemsQueryCommand.Parameters["@quantity"] = p[i][1]; //Quantity;

    itemsQueryCommand.ExecuteNonQuery();
}

Upvotes: 0

Related Questions