By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,417 Members | 904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,417 IT Pros & Developers. It's quick & easy.

insert and update database table rows using textboxes

P: 25
Hi there, I'm having a problem with updating and/or inserting rows in a table in my database. I have a form with a combobox, 10 textboxes, and save button. 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. Here's my code for reading the illness and symptoms:
Expand|Select|Wrap|Line Numbers
  1. Call Connect()
  2.             Dim str As String
  3.             str = "Select sname from diagnose where first_aid = @ill"
  4.             cmd.Parameters.AddWithValue("ill", cmbRecord.Text)
  5.             cmd.Connection = myConn
  6.             cmd.CommandText = str
  7.             dtr = cmd.ExecuteReader
  8.  
  9.             Dim symptoms As New List(Of String)
  10.             While dtr.Read()
  11.                 symptoms.Add(dtr("sname"))
  12.             End While
  13.             'set available symptoms
  14.             Dim arrayOfTextboxes() As TextBox = {symp0, symp1, symp2, symp3, symp4, symp5, symp6, symp7, symp8, symp9}
  15.             Dim i As Integer = 0
  16.             For i = 0 To symptoms.Count - 1
  17.                 arrayOfTextboxes(i).Text = symptoms(i)
  18.             Next
  19.             'clear other textboxes
  20.             For j = i To UBound(arrayOfTextboxes)
  21.                 arrayOfTextboxes(j).Text = String.Empty
  22.             Next
  23.             myConn.Close()
  24.  
and here's my code for inserting or updating. I get error "Parameter 'ill' has already been defined."
Expand|Select|Wrap|Line Numbers
  1. Call Connect()
  2.             If Duplicate() = False Then
  3.                 Dim dt As New DataTable("diagnose")
  4.                 Dim row As DataRow
  5.                 Dim arrayOfTextboxes() As TextBox = {symp0, symp1, symp2, symp3, symp4, symp5, symp6, symp7, symp8, symp9}
  6.                 Dim symptoms As New List(Of String)
  7.                 STRSQL = "insert into diagnose values (@ill, @sym)"
  8.                 Using myCmd = New MySqlCommand(STRSQL, myConn)
  9.                     myConn.Open()
  10.  
  11.                     Dim i As Integer = 0
  12.                     For i = 0 To arrayOfTextboxes.Count - 1
  13.                         If String.IsNullOrEmpty(arrayOfTextboxes(i).Text) Then Continue For
  14.                         If arrayOfTextboxes(i).Text <> "" Then
  15.                             myCmd.Parameters.AddWithValue("ill", cmbRecord.Text)
  16.                             myCmd.Parameters.AddWithValue("sym", arrayOfTextboxes(i).Text)
  17.                             row = dt.NewRow()
  18.                             myCmd.ExecuteNonQuery()
  19.                         End If
  20.                     Next
  21.                     MsgBox("Record Added")
  22.                     myConn.Close()
  23.                 End Using
  24.  
  25.                 'Else
  26.                 '    STRSQL = "Update diagnose set first_aid = @ill, sname = @symp where first_aid = @ill"
  27.                 '    Using myCmd = New MySqlCommand(STRSQL, myConn)
  28.                 '        myConn.Open()
  29.                 '        myCmd.Parameters.AddWithValue("ill", cmbRecord.Text)
  30.                 '        myCmd.Parameters.AddWithValue("sym", symp0.Text)
  31.                 '        myCmd.Parameters.AddWithValue("sym", symp1.Text)
  32.                 '        myCmd.Parameters.AddWithValue("sym", symp2.Text)
  33.                 '        myCmd.Parameters.AddWithValue("sym", symp3.Text)
  34.                 '        myCmd.Parameters.AddWithValue("sym", symp4.Text)
  35.                 '        myCmd.Parameters.AddWithValue("sym", symp5.Text)
  36.                 '        myCmd.Parameters.AddWithValue("sym", symp6.Text)
  37.                 '        myCmd.Parameters.AddWithValue("sym", symp7.Text)
  38.                 '        myCmd.Parameters.AddWithValue("sym", symp8.Text)
  39.                 '        myCmd.Parameters.AddWithValue("sym", symp9.Text)
  40.                 '        myCmd.ExecuteNonQuery()
  41.                 '    End Using
  42.                 '    MsgBox("Record Updated")
  43.                 '    myConn.Close()
  44.             End If
  45.  
The codes marked as comment is my code for updating, but that's not working so I set it aside for the mean time. The table looks like this:
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. I already spent a day trying to make this work but failed. Please help me make this work. Any help will be greatly appreciated. Thanks in advance, Godbless
Feb 17 '13 #1
Share this Question
Share on Google+
2 Replies


P: 25
ok I'm getting closer..I can add a row in the database now but only 1. Say I entered cold as new illness then filled the 2 textboxes with symptoms, it only stores the symptom on the first textbox. So it only add 1 row with 1 symptom on the table. Also, I still get the "Parameter 'ill' has already been defined". Anybody have an idea?
Feb 17 '13 #2

Mikkeee
P: 94
Kumsay, it looks like you keep adding to your parameter collection. You need to either add the parameters to your command before your loop and just update the values OR you need to clear them before you use AddWithValue.
Feb 17 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.