Loop SQL Insert from C# Array

Question

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

Selman Genç · Accepted Answer · 2014-03-02 22:57:05Z

3

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

}

edited Mar 2, 2014 at 22:57

answered Mar 2, 2014 at 22:38

Selman Genç

102k15 gold badges126 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Over a year ago

I think this is my best bet, I've tried with your code but it errors on cmd.itemsQueryCommand ( so i changed it to var cmd.Parameters.AddWithValue("@name", ProductId); and added cmd.dispose(); now it errors with - Connection property has not been initialized.

Selman Genç Over a year ago

@Matastic sorry it's my mistake :) I have noticed that and updated my answer.Set your connection property and it should be fine

Matt Over a year ago

You've nailed it, Thank You Very Much :)

Hogan · Accepted Answer · 2014-03-02 22:39:30Z

0

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

answered Mar 2, 2014 at 22:39

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

3 Comments

Steve Over a year ago

I suggest to change the Add with AddWithValue using a dummy value of the correct datatype.

Hogan Over a year ago

@Steve - The best way is to change the parameter to Add() to a new Parameter object.

Steve Over a year ago

Well, too late now, but I was talking about that this form of Add is not valid. Passing a string doesn't work. It throws an InvalidCastException. It is a pity because the idea is right. The parameter collection doesn't change inside the loop so there is no point to rebuild the whole thing at every loop.

Noctis · Accepted Answer · 2014-03-02 23:01:13Z

0

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.

answered Mar 2, 2014 at 23:01

Noctis

11.8k3 gold badges46 silver badges86 bronze badges

2 Comments

Matt Over a year ago

Agreed - But I've only been programming for 8 weeks. Thanks :)

Noctis Over a year ago

that's a start ... keep doing it if you enjoy it, and in no time (well, some time), it'll all come together :)

Collectives™ on Stack Overflow

Loop SQL Insert from C# Array

3 Answers 3

3 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related