VB 2008 how to insert and update database table ro

Question

I'm doing a part of my system which is updating and/or inserting data into database. I have a form with a combobox and 10 textboxes. Here, the combobox if filled with illnesses from the diagnose table in my database.

Diagnose table's structure is: f_id, illness, symptoms, so an illness can have many symptoms.

If an item is selected from the combobox, the symptoms will be displayed on the textboxes. I already got this working. My problem is that I should make it able to insert or update the rows in the table. I really don't have any idea how to do this. So far, here's my code:

Call Connect()
        If Duplicate() = False Then

            STRSQL = "insert into diagnose values ('', @ill, @sym0), ('', @ill, @sym1), ('', @ill, @sym2), ('', @ill, @sym3), ('', @ill, @sym4), ('', @ill, @sym5), ('', @ill, @sym6), ('', @ill, @sym7), ('', @ill, @sym8), ('', @ill, @sym9)"
            Using myCmd = New MySqlCommand(STRSQL, myConn)
                myConn.Open()
                myCmd.Parameters.AddWithValue("ill", cmbRecord.Text)
                myCmd.Parameters.AddWithValue("sym0", symp0.Text)
                myCmd.Parameters.AddWithValue("sym1", symp1.Text)
                myCmd.Parameters.AddWithValue("sym2", symp2.Text)
                myCmd.Parameters.AddWithValue("sym3", symp3.Text)
                myCmd.Parameters.AddWithValue("sym4", symp4.Text)
                myCmd.Parameters.AddWithValue("sym5", symp5.Text)
                myCmd.Parameters.AddWithValue("sym6", symp6.Text)
                myCmd.Parameters.AddWithValue("sym7", symp7.Text)
                myCmd.Parameters.AddWithValue("sym8", symp8.Text)
                myCmd.Parameters.AddWithValue("sym9", symp9.Text)
                myCmd.ExecuteNonQuery()
            End Using
            MsgBox("Record Added")
            myConn.Close()
        Else
            STRSQL = "Update diagnose set first_aid = @ill, sname = @symp where first_aid = @ill"
            Using myCmd = New MySqlCommand(STRSQL, myConn)
                myConn.Open()
                myCmd.Parameters.AddWithValue("ill", cmbRecord.Text)
                myCmd.Parameters.AddWithValue("sym", symp0.Text)
                myCmd.Parameters.AddWithValue("sym", symp1.Text)
                myCmd.Parameters.AddWithValue("sym", symp2.Text)
                myCmd.Parameters.AddWithValue("sym", symp3.Text)
                myCmd.Parameters.AddWithValue("sym", symp4.Text)
                myCmd.Parameters.AddWithValue("sym", symp5.Text)
                myCmd.Parameters.AddWithValue("sym", symp6.Text)
                myCmd.Parameters.AddWithValue("sym", symp7.Text)
                myCmd.Parameters.AddWithValue("sym", symp8.Text)
                myCmd.Parameters.AddWithValue("sym", symp9.Text)
                myCmd.ExecuteNonQuery()
            End Using
            MsgBox("Record Updated")
            myConn.Close()
        End If

I already have a module that connect my project to mysql db (myConn). What I can't do is that I can't update any row in the diagnose table. I also can't add rows. The diagnose table look like this(example):

f_id | illness |  symptom
1   |  fever   |  fever
2   |  fever   |  hot temperature
3   |  fever   |  dizziness
4   |  fever   |  headache

so in that case, say I chose fever in the combobox then it will display the symptoms on the 4 textboxes. If the user made changes, the Duplicate() function checks if the combobox value already have a record. If true then it will update. Say the user added another symptom, so if save button is clicked, fever will add another row with the added symptom.

If false, then a new record will be added, which means new row or rows will be added in the table dependeng on the number of symptoms inputted on the textboxes. So, say 'cold' is to be added as a new record and I entered 2 symptoms, this means that I used 2 of the 10 textboxes in the form, then 2 rows will be added on the table.

Answer 1

You are trying to Insert multiple rows in the same query, which is not possible. you need to have separate queries for each row inserted. Loop through the array of textboxes (from you previous question) to insert :

Dim arrayOfTextboxes() As TextBox = {symp0, ... put all textboxes here..., symp9}
myConn.Open()

If Duplicate() = False Then

    STRSQL = "insert into diagnose values (@ill, @sym)"

    For i = 0 To arrayOfTextboxes.Count - 1

       If String.IsNullOrEmpty(arrayOfTextboxes(i).Text) Then Continue For

       Using myCmd = New MySqlCommand(STRSQL, myConn)
            myCmd.Parameters.AddWithValue("@ill", cmbRecord.Text)
            myCmd.Parameters.AddWithValue("@sym", arrayOfTextboxes(i).Text)              
            myCmd.ExecuteNonQuery()
        End Using

    Next          

        MsgBox("Record Added")

Else

      // I don't get what you want to do in the update part yet.

End If

myConn.Close()