vb2008 retrieve value from combobox selected item and display to multiple textboxes

Question

I wanted to ask how to get combobox's selected value then display it to multiple textboxes. The combobox is populated with illnesses from the database. Now when an illness is selected from combobox, the symptoms of that illness should be displayed in many textboxes. Currently I have 10 textboxes for the symptoms. The table structure is id, illness, symptoms. Here's my code:

Dim mycmd As New MySqlCommand
Dim dtr As MySqlDataReader
Call Connect()
        Dim str As String
        str = "Select symptoms from diagnose where illness = @ill"
        mycmd.Parameters.AddWithValue("ill", cmbRecord.Text)
        mycmd.CommandText = str
        dtr = mycmd.ExecuteReader
        While dtr.Read()
            symp0.Text = dtr("symptoms")
            symp1.Text = dtr("symptoms")
            symp2.Text = dtr("symptoms")
            symp3.Text = dtr("symptoms")
            symp4.Text = dtr("symptoms")
            symp5.Text = dtr("symptoms")
            symp6.Text = dtr("symptoms")
            symp7.Text = dtr("symptoms")
            symp8.Text = dtr("symptoms")
            symp9.Text = dtr("symptoms")
        End While
myConn.Close()

when an illness is selected from the combobox the symptoms should display on those textboxes. Say the selected illness has only 4 symptoms in the table, then symp0 to symp3 textbox will show the symptoms one by one and leaving the remaining textboxes blank.

The problem is that when an illness is selected, those textboxes displays only the last symptom of that illness stored in the database.

Example: fever. In the table, it has 4 symptoms: cold, hot temperature, headache, dizziness. If fever is selected, only dizziness is displayed from symp0 to symp9 textboxes.

You seriously need to reconsider your design, if you have to write code like this many times: sympX.Text, where X=0:1:9 (0 through 9). — Neolisk
@Neolisk - I'm sorry sir, I'm just a newbie and still learning..I can't figure out what do you mean. How will I use that "sympX.Text, where X=0:1:9 (0 through 9)"?..did you mean I should replace those symp0.Text through symp9.Text with sympX.Text and declare a x as integer, then write x=0:1:9 ? — VBeginner
It means you should at least have an array of Textboxes, with 10 elements, before you even go for working with databases. You would then have a loop to iterate through all of them, and only one assignment operation. Your code becomes clean and easy to read/maintain. Also read my answer - was too big for a comment. — Neolisk

Neolisk Neolisk · Accepted Answer · 2013-02-14T02:05:20

dtr("symptoms") gets a value of column "symptoms" from the current row. Between assignment to Textboxes #1 through #10 you do not advance reader to the next row, this is why you are seeing the same value in all textboxes.

Now to answer your question why it's the last symptom in all textboxes. By looping through rows with dtr.Read(), you re-assign values of Textboxes every time, and it happens until the last one is assigned. At which point no subsequent assignments are made, so the last value remains on the screen.

What you are looking for is probably something like this:

Dim symptoms As New List(Of String)
While dtr.Read()
  symptoms.Add(dtr("symptoms"))     
End While

'set available symptoms
Dim arrayOfTextboxes() As TextBox = {symp0, ... put all textboxes here..., 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

This is assuming you have less than 10 symptoms to display. For any number of symptoms, you could have used a ListBox by setting its DataSource to symptoms. Your code becomes more compact:

Dim symptoms As New List(Of String)
While dtr.Read()
  symptoms.Add(dtr("symptoms"))     
End While
YourListBoxWithSymptoms.DataSource = symptoms

vb2008 retrieve value from combobox selected item and display to multiple textboxes

1 Answers