Insert into SQL database, in foreach with checkbox list

Question

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?

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Feb 11, 2016 at 12:50

Soner Gönül · Accepted Answer · 2016-02-11 12:56:27Z

4

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.

edited Feb 11, 2016 at 12:56

answered Feb 11, 2016 at 12:46

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

Comments

Peter Krassoi · Accepted Answer · 2016-02-11 12:52:23Z

1

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.

answered Feb 11, 2016 at 12:52

Peter Krassoi

5673 silver badges11 bronze badges

Comments

Can PERK · Accepted Answer · 2016-02-11 13:00:27Z

1

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

answered Feb 11, 2016 at 13:00

Can PERK

63010 silver badges26 bronze badges

Collectives™ on Stack Overflow

Insert into SQL database, in foreach with checkbox list

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related