vb 2008 get listbox items

[kumsay]

hello, I am having a problem with getting items from a listbox. I have a form with 3 listboxes and a button. lstSymptoms is populated with symptoms from the database. When a symptom is selected, it is then listed in lstSelected. Now when diagnose button is clicked, it should get the symptoms from lstSelected and diagnose from the database table the illness where this symptoms is present, then display the result to lstIllness. My database table's structure is: f_id, illness, symptoms. So far here's my code:

Call Connect()

        Dim dt As New DataTable
        Dim cmd As New MySqlCommand
        Try
            lstIllness.Items.Clear()
            cmd.Connection = myConn
            cmd.CommandText = "select ill from symptoms where sym = @symp"
            cmd.Parameters.AddWithValue("symp", lstSelected.Items)
            myReader = myCmd.ExecuteReader
            If (myReader.Read()) Then
                myReader.Close()
                myAdptr.SelectCommand = cmd
                myAdptr.Fill(dt)
                lstIllness.DisplayMember = "ill"
                lstIllness.ValueMember = "ill"

                For Each row As DataRow In dt.Rows
                    lstIllness.Items.Add(row("ill"))
                Next
                Dim builder As New StringBuilder()
                builder.Append("select distinct ill from symptoms where ")
                For y As Integer = 0 To lstSelected.Items.Count - 1
                    Dim parameterName As String = "@symp" & y.ToString()
                    If y <> 0 Then
                        builder.Append("and ")
                    End If
                    builder.Append(parameterName)
                    builder.Append(" in (select sym from symptoms where ill = i.ill) ")
                    cmd.Parameters.AddWithValue(parameterName, lstSelected.Items(y))
                Next
                cmd.CommandText = builder.ToString()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        cmd = Nothing
        myReader = Nothing
        myConn.Close()
        Call Disconnect()

It has no error but when I click the diagnose button, nothing is happening. Please, can anybody tell me the problem with my code? I really need to finish this today, please help me. Thanks in advance, God bless

[Rabbit]

What does the final SQL string look like?

[kumsay]

I'm sorry sir I don't get you (not familiar with the terms), do you mean the result?
What I'm trying to do there is when diagnose button is clicked it will list in the lstIllness the illnesses that has a symptom present in the lstSelected. My table looks like this:
f_id | illness | symptoms
1 | fever | fever
2 | fever | cold
3 | fever | hot temperature
4 | fever | headache
5 | cold | cold
so if lstSelected have headache and cold, then lstIllness will list fever and cold.

[Rabbit]

You're using SQL in your code but you don't know what SQL is? Where did you get the code from?

[kumsay]

I know what is SQL sir, its just that I'm just beginning to learn this things..I'm really sorry. I found the site where I got that code: http://stackoverflow.com/questions/1...95822#14695822. I added another listbox which is the lstSelected and made some modifications in the code. I thought its okay to do that, but obviously I was wrong and I don't know what to do. Can you please help me? :(

[Mikkeee]

kumsay, I looked at your code but there are a lot of issues with it. It looks to me like you're attempting to interact with a database but lack some basic programming logic. My intent here isn't to criticize you but you should probably start with a more basic project. Here are a few issues I can see right away.

• Closing your connection at the end but not opening it anywhere.
• Mismatched parameter name between commandtext and adding the parameter value.
• Adding a collection of list box items to your parameter value.
• Opening a reader and closing it for no apparent reason.
• Building a StringBuilder, assigning it to the CommandText, and never using it.