Have one combo box populate another based on selection

[bchaney]

I am currently stuck in one part of my application. I have two combo boxes that i am having the users select from. The first combo box is simply bound to a datatable. that is working properly. The second one i need to populate off another datatable after sorting out what the first one is requesting. Basically it's choosing a line number on the first box and then selecting the machine associated with that line number. The code i have currently is as follows:

Private Sub linecbo_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles linecbo.SelectedIndexChanged
        Dim cline As String
        Dim mycon As SqlCeConnection
        Dim mycmd As New SqlCeCommand
        Dim myDA As New SqlCeDataAdapter
        Dim myDS As New DataSet
        Dim myDT As New DataTable

        cline = linecbo.SelectedText
        mycon = GetConnect()
        mycon.Open()
        mycmd = mycon.CreateCommand
        mycmd.CommandText = "SELECT Machine, LineNumber FROM machine WHERE LineNumber = '" & Trim(cline) & " ' "
        myDA.SelectCommand = mycmd
        myDA.Fill(myDS, "machine")
        myDT = myDS.Tables("machine")

        machinecbo.DataSource = myDT
        machinecbo.DisplayMember = "Machine"
        machinecbo.ValueMember = "LineNumber"

    End Sub

The only thing i can think of is that i'm only seeing the first row of the database. i need to see all rows associated with the lines. also when i select other lines from the first combo box my second combo box doesn't update even though i have this code in the index change event of the first combo box. shouldn't this always run whenever the index changes?

Thanks

[Teme64]

shouldn't this always run whenever the index changes?

Yes, it should and it will run.

In your code change the line 9 to use SelectedValue property:

cline = linecbo.SelectedValue

Also, do you store LineNumber as a numeric value or as a textual value in your DB? If LineNumber field is of numeric type (i.e. INT/INTEGER in DB), change the SQL command:

mycmd.CommandText = "SELECT Machine, LineNumber FROM machine WHERE LineNumber = " & CInt(cline) & " "

HTH

[bchaney]

The LineNumber is stored in a DB as a text value. EX: "Line 1". And thanks for the code help. I'm going to try this as soon as i get time at work. I'll let you know what i find out with the changes.

[bchaney]

It works fine now with the select command as is. I didn't change the command because of the comment you made about if it was a numeric value. I appreciate your help.

[Teme64]

Hi! Nice to hear that you got answer to your problem. Could you please mark the thread as solved. Thank you!