CODEWITHSUNDEEP
c#sqlforeachsql-insert
Caroline Olivia
Caroline Olivia

Reputation: 356

Insert into SQL database, in foreach with checkbox list

I have this, and made a scope identity on another insert that makes the int id variable

foreach (ListItem item in CheckBoxList1.Items)
{
    if (item.Selected)
    {
        int CheckId = Convert.ToInt32(item.Value);
        cmd.CommandText = "INSERT INTO FarveTilCykel (fk_cy_id, fk_farve_id) VALUES (@cy, @farve)";

        cmd.Parameters.Add("@cy", SqlDbType.VarChar).Value = id;
        cmd.Parameters.Add("@farve", SqlDbType.VarChar).Value = CheckId;

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    } //end if
} //end foreach

The problem is (I think) that, when it has run it 1 time and does second, the @cy is already declared, how can I make it different for each time?

Upvotes: 2

Views: 916

Answers (3)

Can PERK
Can PERK

Reputation: 630

I prefer open connection (conn) once and write it in using statement then clear parameters(cmd) for each record.

Upvotes: 1

Soner Gönül
Soner Gönül

Reputation: 98740

You can't add the same parameters to a command more than once. You can Clear your parameters at the end of every iteration like;

foreach (ListItem item in CheckBoxList1.Items)
{
   ...
   ...
   cmd.Parameters.Clear();
}

Or better, define your parameters before your foreach and just add/change their values inside of it.

var parameter1 = new SqlParameter("@cy", SqlDbType.VarChar);
var parameter2 = new SqlParameter("@farve", SqlDbType.VarChar);    
cmd.Parameters.Add(parameter1);
cmd.Parameters.Add(parameter2);

foreach (ListItem item in CheckBoxList1.Items)
{
   ...
   ...
   cmd.Parameters["@cy"].Value = id;
   cmd.Parameters["@farve"].Value = CheckId;
}

Also use using statement to dispose your connection and command automatically instead of calling Close or Dispose methods manually.

Upvotes: 4

Peter Krassoi
Peter Krassoi

Reputation: 567

place

var p1 = cmd.Parameters.Add("@cy", SqlDbType.VarChar);
var p2 = cmd.Parameters.Add("@farve", SqlDbType.VarChar);

before the foreach block and 

p1.Value = id;
p2.Value = CheckId;

inside the block instead of the 2 current lines.

Upvotes: 1

Related Questions