The variable name '@' has already been declared. Variable names must be unique within a query batch or stored procedure.'

Question

string subject=  "INSERT INTO Subjects (ThesisNo, [Subject]) VALUES (2, @subject)";
      
SqlCommand commandSubject = new SqlCommand(subject,con);

string temp;

foreach (var control in checkedListBox.CheckedItems) 
{
    temp = control.ToString();
    commandSubject.Parameters.AddWithValue("@subject", temp);
}

con.Open();
commandSubject.ExecuteNonQuery();
con.Close();

I want to add items that are checked in the Checkedlistbox to @subject.

But I can only add one item. How can I add all checked items to @subject?

Answer 1

You're adding parameters inside the loop. Add outside, and set the value inside and execute every time:

    using(SqlConnection con = ...) {
        string subject=  "INSERT INTO Subjects(ThesisNo,[Subject]) VALUES (2, @subject)";
        con.Open();
        
        SqlCommand commandSubject = new SqlCommand(subject,con);
        commandSubject.Parameters.Add("@subject", SqlDbType.VarChar);

        foreach (var control in checkedListBox.CheckedItems) 
        {
            commandSubject.Parameters["@subject"].Value = control.ToString();
            commandSubject.ExecuteNonQuery();
        }
    }

Or if you're wanting to execute once you need to modify the SQL, and add as you go, so that you end up with an SQL like e.g. INSERT INTO .. VALUES (2,@p0), (2,@p1), (2,@p2) and a parameters collection that is 3 long (@p0 to @p2) with 3 different data values:

    using(SqlConnection con = ...) {
        string subject=  "INSERT INTO Subjects(ThesisNo,[Subject])VALUES";
        
        
        SqlCommand commandSubject = new SqlCommand(subject, con);
        
        int p=0;
        foreach (var control in checkedListBox.CheckedItems) 
        {
            commandSubject.CommandText += $"(2,@subject{p}),"
            commandSubject.Parameters.Add($"@subject{p}", SqlDbType.VarChar).Value = control.ToString();
            p++;
        }

        commandSubject.CommandText = commandSubject.CommandText.TrimEnd(','); //remove trailing comma from concat

        con.Open();
        commandSubject.ExecuteNonQuery();
    }

If you have thousands of these string concats to do, use a StringBuilder. For what you might reasonably want a user to tick in a UI (20 or fewer?) string concat will be fine

Answer 2

The best method depends on whether the number of items is large and variable.

If that is the case either a Table-Valued Parameter or using SqlBulkCopy is the most performant option.

Here is how you would do SqlBulkCopy:

var table = new DataTable{
    Columns = {
        {"ThesisNo", typeof(int)},
        { "Subject", typeof(string)
    }
};

foreach (var control in checkedListBox.CheckedItems)
    DataTable.Rows.Add(2, control.ToString());

using(var bulk = new SqlBulkCopy(con, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers)
{
    DestinationTableName  = "Subjects"
})
    bulk.WriteToServer(table);

---

The other method, which, at a push, you can get away with on smaller sets, is to add a numbered parameter for each item. The maximum is 2100. Do NOT do this if range of possible rowcounts is large, otherwise you will kill your query plan store:

using(SqlCommand comm = new SqlCommand("",con))
{
    var n = 0;
    foreach (var control in checkedListBox.CheckedItems)
        comm.Parameters.Add("@subject" + (++n), SqlDbType.Varchar, *sizeofcolumnhere*).Value = control.ToString();

    comm.CommandText = "INSERT INTO Subjects(ThesisNo,[Subject])\r\nVALUES\r\n" +
        string.Join("\r\n",
            Enumerable.Range(1, n).Select(i => $"(2 ,@subject{i})");
    
    comm.ExecuteNonQuery();
}

Answer 3

It's been a long time since the problem. Maybe it will be helpful for newbies. Add the line 'SqlCommand commandSubject = new SqlCommand(subject,con)' to the foreach loop. As follows.

string subject=  "INSERT INTO Subjects (ThesisNo, [Subject]) VALUES 
(2, @subject)";
  
string temp;

foreach (var control in checkedListBox.CheckedItems) 
{
SqlCommand commandSubject = new SqlCommand(subject,con);
temp = control.ToString();
commandSubject.Parameters.AddWithValue("@subject", temp);
}

con.Open();
commandSubject.ExecuteNonQuery();
con.Close();