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: -
Call Connect()
-
Dim str As String
-
str = "Select sname from diagnose where first_aid = @ill"
-
cmd.Parameters.AddWithValue("ill", cmbRecord.Text)
-
cmd.Connection = myConn
-
cmd.CommandText = str
-
dtr = cmd.ExecuteReader
-
-
Dim symptoms As New List(Of String)
-
While dtr.Read()
-
symptoms.Add(dtr("sname"))
-
End While
-
'set available symptoms
-
Dim arrayOfTextboxes() As TextBox = {symp0, symp1, symp2, symp3, symp4, symp5, symp6, symp7, symp8, symp9}
-
Dim i As Integer = 0
-
For i = 0 To symptoms.Count - 1
-
arrayOfTextboxes(i).Text = symptoms(i)
-
Next
-
'clear other textboxes
-
For j = i To UBound(arrayOfTextboxes)
-
arrayOfTextboxes(j).Text = String.Empty
-
Next
-
myConn.Close()
-
and here's my code for inserting or updating. I get error "Parameter 'ill' has already been defined." -
Call Connect()
-
If Duplicate() = False Then
-
Dim dt As New DataTable("diagnose")
-
Dim row As DataRow
-
Dim arrayOfTextboxes() As TextBox = {symp0, symp1, symp2, symp3, symp4, symp5, symp6, symp7, symp8, symp9}
-
Dim symptoms As New List(Of String)
-
STRSQL = "insert into diagnose values (@ill, @sym)"
-
Using myCmd = New MySqlCommand(STRSQL, myConn)
-
myConn.Open()
-
-
Dim i As Integer = 0
-
For i = 0 To arrayOfTextboxes.Count - 1
-
If String.IsNullOrEmpty(arrayOfTextboxes(i).Text) Then Continue For
-
If arrayOfTextboxes(i).Text <> "" Then
-
myCmd.Parameters.AddWithValue("ill", cmbRecord.Text)
-
myCmd.Parameters.AddWithValue("sym", arrayOfTextboxes(i).Text)
-
row = dt.NewRow()
-
myCmd.ExecuteNonQuery()
-
End If
-
Next
-
MsgBox("Record Added")
-
myConn.Close()
-
End Using
-
-
'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
-
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
2 3899
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?
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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...
|
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...
| |