Pass items from checkboxlist to SQL Server table

Question

I'm trying to take items from a checkboxlist and add them to a SQL Server table. I assumed it would be as easy as looping through each item and then inserting it into the table, but I'm getting an unhandled exception with the following code:

using (SqlConnection connection = new SqlConnection(connectionString))
{
     for (int i = 0; i <= UPCList.Items.Count; i++)
     {
         string finalSubmit =
              "INSERT INTO Boxes (BoxNumber)"
              + "VALUES @selected";

          SqlCommand command = new SqlCommand(finalSubmit, connection);
          command.Parameters.AddWithValue("@selected", i);
          command.Connection.Open();
          command.ExecuteNonQuery();
          command.Connection.Close();
      }
 }

Edit: one of the suggestions below worked, but it's putting in the ID of the item rather than the value of the list item itself. How can I insert the value for each item in the list into the SQL table?

If you want to know the exact error as well I would recommend wrapping your code inside the for loop around a `try{}catch{}` — MethodMan, Jul 19 '13 at 20:17

Tim Schmelter · Accepted Answer · 2013-07-19T20:32:31.507

5

Put the VALUES in paranthesis:

"INSERT INTO Boxes (BoxNumber) VALUES (@selected)";

Also, i assume you have to replace

for (int i = 0; i <= UPCList.Items.Count; i++)

with

for (int i = 0; i < UPCList.Items.Count; i++)

and you might want to insert the item's text instead of the index:

command.Parameters.AddWithValue("@selected", listBox.Items[i].ToString());

edited Jul 19 '13 at 20:32

answered Jul 19 '13 at 20:12

Tim Schmelter

450,073
74
686
939

That worked, but now it's just putting the ID into the table instead of the list item value. How can I go about doing that? – Michael Bowman Jul 19 '13 at 20:19
What is the datsource of your `ListBox`? Try `command.Parameters.AddWithValue("@selected", listBox1.Items[i].ToString());` – Tim Schmelter Jul 19 '13 at 20:25
I didn't, and that works! However, it's adding the items twice as if it's going over the list twice. I'm quite new to programming, so my apologies if that's something obvious that I missed. – Michael Bowman Jul 19 '13 at 20:30
If you wouldn't mind putting that in the form of an answer, I'll mark it. – Michael Bowman Jul 19 '13 at 20:30
@MichaelBowman: Why twice? My code above shows just a single loop. – Tim Schmelter Jul 19 '13 at 20:31
Not sure. Boxes was completely empty, I hit the button that initiated this code, and looked again and the list was added twice. – Michael Bowman Jul 19 '13 at 20:32
@MichaelBowman: Edited my answer to include my comment. According to your last comment: maybe your code is executed twice, where it is located? Use a `button`-click event. – Tim Schmelter Jul 19 '13 at 20:33
I'm currently using a button-click event to initiate this code. I received some help from another individual about viewstates, so I'm learning more about that to make sure that's not my problem. – Michael Bowman Jul 24 '13 at 14:24

Pass items from checkboxlist to SQL Server table

1 Answers1

Linked