473,320 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

insert and update database table rows using textboxes

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
2 3899
kumsay
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
94 64KB
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

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

Similar topics

0
by: Marko Poutiainen | last post by:
Situation: We had to make our SQLServer 2000 database multi-lingual. That is, certain things (such as product names) in the database should be shown in the language the user is using (Finnish,...
5
by: skipc | last post by:
Hi, I am stuck... I've got a popup window that displays a list in table format with links on the bottom to navigate the list <prev> 1 2 3 ... <next> When I demo'd to the users... they...
1
by: Sharon | last post by:
Hello All, Is it possible to update Sql Table through DataGrid. I have a DataGrid which is being populated through a stored procedure, all i wanted to do is to update one field...
2
by: veenaaecom | last post by:
Hi, I have a table A , table B and table C. Table B is kind of a bridge to table A and table C (i.e it has common columns that bridges table A and table C) I have a colun in table A whose value is...
1
by: gomathinayagam | last post by:
hai, am only beginer in c#... i am trying to connect database with webform. using a technique that the fields of the table and the controls in a form are named same...then i try get the controls...
10
by: gagonm | last post by:
Hi I m looking for some sample example (Code) for my following scenario I have a Grid View which populates data from database say from Product Tables using dataset and dataadapter This...
3
by: planethax | last post by:
I have multiple arrays that I need to insert/update database and I am not sure how to start, I think I need to use the "foreach" statement and also not sure whether or not to serialize and...
1
by: visweswaran2830 | last post by:
Hi, I want to insert href into tabel cell using javascript. Note should support all browser. I know to insert, but it support only in IE, I checked in firefox, it does not support so please...
0
by: kumardharanik | last post by:
Hi Friends, The below code is a sample code for insert, update and delete using datagrid but i need to convert the entire code for datagridview.. Plsss help me.. using System; using...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.