CODEWITHSUNDEEP
c#sql
JoeMarvel
JoeMarvel

Reputation: 41

Insert more than one item from checkedlistbox to AccessDB

I have a listcheckedbox that lists the employee numbers and I want to be able to add each employee that attended a training class to show up under a training session. However, when I try to submit the information to insert into the DB, it will only add one of the employees selected. How can I have it submit more than 1 employee to a class session?

try
            {
                string cmdstring = "INSERT INTO [SESSION] (PrincipleName, Comments, SessionDate, SessionName, TellerNum) VALUES (@principle, @comments, @date, @session, @teller)";
                using (OleDbCommand cmd = new OleDbCommand(cmdstring, con))
                {
                    cmd.Parameters.AddWithValue("@principle", comboBox12.Text);
                    cmd.Parameters.AddWithValue("@comments", textBox3.Text);
                    cmd.Parameters.AddWithValue("@date", dateTimePicker1.Value);
                    cmd.Parameters.AddWithValue("@session", comboBox1.Text);
                    cmd.Parameters.AddWithValue("@teller", checkedListBox1.Text);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    MessageBox.Show("Submitted Successfully");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to " + ex.Message);
            }

Here is my updated code after the answer from LarsTech answer

                    con.Open();
                    string cmdstring = "INSERT INTO [SESSION] (PrincipleName, Comments, SessionDate, SessionName, TellerNum) VALUES (@principle, @comments, @date, @session, @teller)";
                    foreach (int s in checkedListBox1.CheckedItems)
                    {
                        using (OleDbCommand cmd = new OleDbCommand(cmdstring, con))
                        {
                            cmd.Parameters.AddWithValue("@principle", comboBox12.Text);
                            cmd.Parameters.AddWithValue("@comments", textBox3.Text);
                            cmd.Parameters.AddWithValue("@date", dateTimePicker1.Value.ToShortDateString());
                            cmd.Parameters.AddWithValue("@session", comboBox1.Text);
                            cmd.Parameters.AddWithValue("@teller", s);

                            cmd.ExecuteNonQuery();
                        }
                    }
                            con.Close();
                            MessageBox.Show("Submitted Successfully");
                            textBox3.Clear();
                            checkedListBox1.ClearSelected();
                            comboBox1.Refresh();
                            comboBox12.Refresh();
                            dateTimePicker1.Refresh();

Upvotes: 1

Views: 81

Answers (1)

LarsTech
LarsTech

Reputation: 81675

You have to iterate over the CheckedItems collection:

foreach (string s in checkedListBox1.CheckedItems)
{
  using (OleDbCommand cmd = new OleDbCommand(cmdstring, con))
  {
    cmd.Parameters.AddWithValue("@principle", comboBox12.Text);
    cmd.Parameters.AddWithValue("@comments", textBox3.Text);
    cmd.Parameters.AddWithValue("@date", dateTimePicker1.Value);
    cmd.Parameters.AddWithValue("@session", comboBox1.Text);
    cmd.Parameters.AddWithValue("@teller", s);
    cmd.ExecuteNonQuery();
  }
}

Upvotes: 1

Related Questions