How to update a single column with Multiple values using MS Access as RDBMS and c#?

Question

I have a table named subjects in which i am saving records regarding students' academia. I am updating 3 fields named program, faculty, and subjectN in it. I am showing you all the fields below in this table:

 RollNo    RegYear    program          faculty              subjectN
   1       2010       Intermediate     Pre-Engineering         A
   1       2010       Intermediate     Pre-Engineering         B
   1       2010       Intermediate     Pre-Engineering         C
   1       2010       Intermediate     Pre-Engineering         D
   1       2010       Intermediate     Pre-Engineering         E
   1       2010       Intermediate     Pre-Engineering         F

Problem i am facing is that (program and faculty are being updated accurately) but subjectN is being updated like this:

 RollNo    RegYear    program          faculty              subjectN
   1       2010       Intermediate     Pre-Medical            G
   1       2010       Intermediate     Pre-Medical            G
   1       2010       Intermediate     Pre-Medical            G
   1       2010       Intermediate     Pre-Medical            G
   1       2010       Intermediate     Pre-Medical            G
   1       2010       Intermediate     Pre-Medical            G

My code to update records is:

            string SQLString2 = "";
            OleDbCommand SQLCommand = new OleDbCommand();
            SQLCommand.Parameters.AddWithValue("@ProgramU", this.comboBox6.SelectedItem.ToString());
            SQLCommand.Parameters.AddWithValue("@FacultyU", this.comboBox5.SelectedItem.ToString());
            foreach (var item in checkedListBox1.CheckedItems)
            {
                foreach (string subName in (item.ToString().Split('+')))
                {
                    SQLString2 = "UPDATE subjects SET program = @ProgramU, faculty = @FacultyU, subjectN = @SUBJECT WHERE RollNo = " + Convert.ToInt32(this.rollNumber7_combo.SelectedItem.ToString()) + " AND regYear = " + Convert.ToInt32(this.comboBox3.SelectedItem.ToString()) + " AND program = '" + this.comboBox1.SelectedItem.ToString() + "' AND faculty = '" + this.comboBox2.SelectedItem.ToString() + "'";
                    SQLCommand.Parameters.AddWithValue("@SUBJECT", subName);
                }
                SQLCommand.CommandText = SQLString2;
                SQLCommand.Connection = database;
                int response2 = -1;
                try
                {
                    response2 = SQLCommand.ExecuteNonQuery();
                    if (response2 > 0)
                    {
                        MessageBox.Show("subjects table has been updated successfully!");
                    }
                    else
                        MessageBox.Show("studentBio table has not been updated!");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

Can somebody please help? How should i structure my query to populate multiple values in a single column? UPDATED On the form i have this:

Answer 1

Try to put in an array the split words first:

  string[] subjects = item.ToString().Split('+');

then use for each

  bool hasErrorFlag = false; // use Error Flag

  foreach(string subject in subjects)
  {
    //your update here
    SQLString2 = "UPDATE subjects SET program = @ProgramU, faculty = @FacultyU, subjectN = @SUBJECT WHERE RollNo = " + Convert.ToInt32(this.rollNumber7_combo.SelectedItem.ToString()) + " AND regYear = " + Convert.ToInt32(this.comboBox3.SelectedItem.ToString()) + " AND program = '" + this.comboBox1.SelectedItem.ToString() + "' AND faculty = '" + this.comboBox2.SelectedItem.ToString() + "'";
    SQLCommand.Parameters.AddWithValue("@SUBJECT", subject);

    // And by the way put inside this bracket the execution of this query

    SQLCommand.CommandText = SQLString2;
                SQLCommand.Connection = database;
                int response2 = -1;
                try
                {
                    response2 = SQLCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    hasErrorFlag = true; // Error Flag updated
                }

  }

  // Put outside the messagebox message for success or failure
  if (hasErrorFlag == false)
     {
         MessageBox.Show("Subjects table has been updated successfully!");
     }
  else
     {
         MessageBox.Show("StudentBio table has not been updated! ");
     }

Answer 2

In your code, you are executing the same UPDATE statement for multiple subject names, which is why you are only seeing the result of the last one. Rather than trying to update the data, I'd suggest you DELETE any records that meet your primary criteria, and the INSERT new records for each subject name.

SQLCommand.Parameters.AddWithValue("@RollNo", Convert.ToInt32(this.rollNumber7_combo.SelectedItem.ToString());
SQLCommand.Parameters.AddWithValue("@regYear", Convert.ToInt32(this.comboBox3.SelectedItem.ToString());
SQLCommand.Parameters.AddWithValue("@program ", this.comboBox2.SelectedItem.ToString();
SQLCommand.Parameters.AddWithValue("@faculty ", this.comboBox1.SelectedItem.ToString();     

 SQLString2 = "DELETE subjects WHERE RollNo = @RollNo AND regYear = @regYear AND program = @program AND faculty = @faculty; 

'---Execute the delete statement...

 foreach (var item in checkedListBox1.CheckedItems)
        {
            foreach (string subName in (item.ToString().Split('+')))
            {
                SQLCommand.Parameters.AddWithValue("@SUBJECT", subName);

                SQLString2 = "INSERT INTO subjects ('RollNo','RegYear','faculty','program','subjectN') VALUES (@RollNo,@regYear,@faculty,@program,@SUBJECT);


            }