get values of selected listbox items to another listbox

[kumsay]

hello, I have a form with 2 listboxes. Listbox1 is populated with symptoms. If a symptom is selected, listbox2 will show the related illnesses having that symptom. Here's my working code:

Call Connect()
        With Me
            Dim dt As New DataTable
            STRSQL = "select illness from diagnose where symptom_name = '" & lstSymptoms.SelectedItem & "'"
            Try
                lstrelated.Items.Clear()
                myCmd.Connection = myConn
                myCmd.CommandText = STRSQL
                reader = myCmd.ExecuteReader
                If (reader.Read()) Then
                    reader.Close()
                    adptr.SelectCommand = myCmd
                    adptr.Fill(dt)
                    lstrelated.DisplayMember = "illness"
                    lstrelated.ValueMember = "illness"
                    For Each row As DataRow In dt.Rows
                        lstrelated.Items.Add(row("illness"))
                    Next
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End With
 

It works perfectly, but what I really wanted is that, for example, when user select symptom1 it will show the related illnesses containing symptom1, then if user select another symptom (ex. symptom3) it will show illnesses containing symptom 1 and 3 and so on. How can I do this? Please help me, I have to finish this today. God bless..

[Frinavale]

Use the SQL In Operator in your SQL query. This will allow you to select rows that match anything "in" a list.

Aside from that, you should really use Parameters with your SQL commands.

-Frinny