title: VB.NET: Updating MySQL Database From (CheckedListBox-ComboBox)
body: <p>I have made a form that is supposed to get the attendance details for a specific session.
  The used elements are:</p>
  
  <p>1- CheckedListBox</p>
  
  <p>2- Combobox :CB_Students</p>
  
  <p>3- Button : Update</p>
  
  <p>My table is Student, which contains Code| First Name| Last Name| Day1| Day2| Day3| Day4| Day5, where the Days are of the type tinyint, referring to Presence or Absence.</p>
  
  <p>I want the user to check the students' names from the CheckedListBox, so I populated the CheckedListBox with the concatenation of First Names and Last Names, each item showing the full name.</p>
  
  <p>Since the CheckedListBox is <strong>indirectly</strong> created by the database records, I suppose I cannot connect it to the database directly, so I have created a hidden ComboBox with values of Code corresponding to each student shown in the CheckedListBox.</p>
  
  <p>For example, my student table is as below:</p>
  
  <p>Code | F_Name | L_Name</p>
  
  <p>1 | F1 | L1</p>
  
  <p>2 | F2 | L2</p>
  
  <p>The CheckedListBox contains: F1 L1 and F2 L2, and the ComboBox contains 1 and 2.</p>
  
  <p>The day number also is found during the form load and saved as the Public variable Inquiry.</p>
  
  <p>Below is my code for the Update Button:</p>
  
  <pre><code>Dim j, S As Integer
      sqlstr = "UPDATE Student SET Day'" &amp; Inquiry &amp; "'=@field1 WHERE Code='" &amp; CB_Students.Items.Item(j) &amp; "'"
      DBCmd = New MySql.Data.MySqlClient.MySqlCommand(sqlstr, DBConn)
      With DBCmd
          For j = 0 To CheckedListBox1.Items.Count - 1
              S = CheckedListBox1.GetItemCheckState(j)
              If S = 1 Then
                  .Parameters.AddWithValue("@field1", 1)
              ElseIf S = 0 Then
                  .Parameters.AddWithValue("@field1", 0)
              End If
          Next j
      End With
      DBCmd.ExecuteNonQuery()
      DBCmd.Dispose()
  </code></pre>
  
  <p>However, during the execution, I get the error: "Parameter '@field1' has already been defined."</p>
  
  <p>How should I deal with this problem?
  Also, is the statement CB_Students.Items.Item(j) used correctly to give my sql string the student code in the ComboBox?</p>
  
  <p>Update 1:</p>
  
  <p>Inquiry is an integer. I also tried the following code:</p>
  
  <pre><code> For j = 0 To CheckedListBox1.Items.Count - 1
          S = CheckedListBox1.GetItemCheckState(j)
          If S = 1 Then
              With DBCmd
                  .Parameters.AddWithValue("@field1", 1)
                  .ExecuteNonQuery()
                  .Dispose()
              End With
          ElseIf S = 0 Then
              With DBCmd
                  .Parameters.AddWithValue("@field1", 0)
                  .ExecuteNonQuery()
                  .Dispose()
              End With
          End If
      Next j
  </code></pre>
  
  <p>But again, as in my response to Never_Mind, I get the following error at the first DBCmd.ExecuteNonQuery() line: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''4' =1 WHERE Code='6'' at line 1".</p>
  
  <p>The values seem to be correct. Day number is 4, Field1 is 1 and Code is 6. I don't see what the problem is.</p>
  
  <p>Update 2:</p>
  
  <p>Below is the code for populating the CheckedListBox and the ComboBox, which is in the form load. C_Code is the Class Code, another field in the student table. ClassID is a public integer variable saved in the previous form. This form appears using the .ShowDialog method.</p>
  
  <pre><code>sqlstr = "SELECT * FROM Student WHERE C_Code = '" &amp; ClassID &amp; "'"
  DBCmd = New MySql.Data.MySqlClient.MySqlCommand(sqlstr, DBConn)
  DBDR = DBCmd.ExecuteReader
      While (DBDR.Read())
          Std_Name = DBDR("F_Name") &amp; " " &amp; DBDR("L_Name")
          CheckedListBox1.Items.Add(Std_Name)
          CB_Students.Items.Add(DBDR("Code"))
      End While
      DBCmd.Dispose()
      DBDR.Close()
  </code></pre>
  
  <p>Also, Code is integer.</p>
  
  <p>Update 3:</p>
  
  <p>I have changed Inquiry's datatype into String and removed the single quotes.</p>
  
  <p>Anyhow, I was thinking maybe I could make different field numbers. Something like this:</p>
  
  <pre><code>Dim j, S, k As Integer
      sqlstr = "UPDATE Student SET D" &amp; Inquiry &amp; " =@field" &amp; k &amp; " WHERE Code='" &amp; CB_Students.Items.Item(j) &amp; "' "
  DBCmd = New MySql.Data.MySqlClient.MySqlCommand(sqlstr, DBConn)
  For j = 0 To CheckedListBox1.Items.Count - 1
          For k = 1 To CheckedListBox1.Items.Count
              S = CheckedListBox1.GetItemCheckState(j)
              If S = 1 Then
                  With DBCmd
                      .Parameters.AddWithValue("@field" &amp; k, 1)
                      .ExecuteNonQuery()
                      .Dispose()
                  End With
              ElseIf S = 0 Then
                  With DBCmd
                      .Parameters.AddWithValue("@field" &amp; k, 0)
                      .ExecuteNonQuery()
                      .Dispose()
                  End With
              End If
          Next k
      Next j
  </code></pre>
  
  <p>But this doesn't work. I think there should be something wrong with the way I've concatenated @field and k while adding through the command. How can I get it to work?</p>
  
  <p>I would really appreciate the help!</p>