get selected combobox item to display its values to listbox

[kumsay]

hello, I need some some help regarding my project. I have a form with a combobox, and listbox. The combobox is populated with illnesses stored in the database. Now, when user select an illness in the combobox, the symptoms should be listed in the listbox. Here's how my table looks like:
f_id | illness | symptoms
1 | fever | dizziness
2 | fever | cold
3 | fever | hot temperature
4 | asthma | dizziness
5 | asthma | difficulty in breathing
6 | asthma | pale
so if user select fever from combobox, listbox will have dizziness, cold, hot temperature. I've already tried something but its not working:

Dim dtr As MySqlDataReader
        Dim cmd As New MySqlCommand
        With Me
            Call Connect()
            cmd.Connection = myConn
            cmd.CommandText = STRSQL
 
            STRSQL = "select sym from symptoms where ill = @rec"
            cmd.Parameters.AddWithValue("rec", cmbRecord.Text)
 
            dtr = cmd.ExecuteReader
            Dim symp As New List(Of String)
            While dtr.Read()
                symp.Add(dtr("symptoms"))
            End While
            lstSymp.DataSource = symp
            dtr = Nothing
            cmd = Nothing
            myConn.Close()
            Call Disconnect()
        End With
 

Please help me correct the code. Thanks in advance.

[zmbd]

The main bit of advise I have for you:

Your database doesn't sound/appear to be normalized from the data you've posted; thus your queries for the list box are going to be so much harder to code.


Now, because I've not done anything with the dot-net stuff the best I can offer you is some information on the database design and hopefully some VBA that you can modify or at least use as inspiration for your VB code:

The database theory will apply to all RDMS just the implementation will vary at tad from one to the next:
A Tutorial for Access
Database Normalization and Table Structures.
Once you get your database normalized, you'll see how much easier it will be to write the SQL and set it as the record source for your list box.
See right now, "Fever" for example has three id's associated with it... if you had a normalized database you'd only have one.


I think the cascade will be more what you're looking at modifying for use in your listbox - you should be able to adapt these to VB.Net fairly easily I would hope.
Form Filter and Cascade
From the Bytes > Sitemap > Microsoft Access / VBA Insights
42.Cascaded Form Filtering
156.Cascading Combo/List Boxes
168.Example Filtering on a Form.

[kumsay]

Thanks for the advise sir, i'll take a look at it. Anyway my problem is solved now, I reviewed all my code on the class and realized I placed the code I gave above in the wrong event. I placed it on the lstSymp_SelectedIndexChanged event instead of putting it on the cmbRecord_SelectedIndexChanged event. The code above is working properly. Sorry for bothering ^_^v

[zmbd]

Sorry for bothering ^_^v

Absolutely no need to apologize!
In fact, your original post and follow-up may help someone else with the same or similar question.
I'm glad that the solution turned out to be an "easy" one... those are the best kind! :)