How to populate combox based upon selection of another combobox

[Iamkenny]

Hello. I'm new to programming and I'm using vb.net 2010 and I'm stuck with this problem. So here are the details, I have 2 tables in my database, a product table and a category table. In my product table I have product no, product description and category no. In the category table i have category no and category desciption. So this is where Ive been stuck for the entire day, I want my combox(product) to populate based on what is in the combobox(category). For example I have "Chair" in my combobox(category), then what would appear in the combobox(product) would be "sofas", "dining chair" etc.

'the part below is where the program will read the category description from my database


    Sub fillcomboDesc()
            Dim fillcatDesc As MySqlCommand = New MySqlCommand("Select catDesc from category;", connection)
            Dim reader1 As MySqlDataReader

            Try
                reader1 = fillcatDesc.ExecuteReader
                While (reader1.Read())
                    cmbDescrip.Items.Add(reader1.GetString(0))

                End While
                fillcatDesc.Dispose()
                reader1.Close()
                Return

            Catch ex As Exception

            End Try
        End Sub


'the part below is where the program will display category description based on my choice

            Private Sub cmbDescrip_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDescrip.SelectedIndexChanged
                Dim cmbcatDescCommand1 As MySqlCommand = New MySqlCommand("Select * from category where catDesc=@field2;", connection)
                Dim reader1 As MySqlDataReader

                Try
                    With cmbcatDescCommand1
                        .Parameters.AddWithValue("@field2", cmbDescrip.Text)
                    End With

                    reader1 = cmbcatDescCommand1.ExecuteReader()
                    If (reader1.Read()) Then
                        txtcatNo.Text = (reader1("catNo"))
                    End If

                    cmbcatDescCommand1.Dispose()
                    reader1.Close()

                Catch ex As Exception

                End Try
            End Sub



'the part below here is supposed to be where my combobox(product) and combobox(category) should connect


        Private Sub cmbProduct_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbProduct.SelectedIndexChanged
            Dim cmbProductCom1 As New MySqlCommand
            Dim newvalue As String
            Dim dataset As New DataSet
            Dim table As New DataTable
            Dim adapter As New MySqlDataAdapter

            connection.ConnectionString = sqlMainConnector

            newvalue = cmbProduct.SelectedValue

            cmbProductCom1.CommandText = "select productDesc from product where catno=" & newvalue & ";"

            adapter.selectcommand = cmbProductCom1
            adapter.Fill(dataset, "product")
            table = dataset.Tables("product")

            cmbDescrip.DataSource = table
            cmbDescrip.DisplayMember = "productDesc"
            cmbDescrip.ValueMember = "catno"

        End Sub

Im sorry if it's all messed up. So guys could you help. Great help would really be appreciated.

[wen_cai]

What is the problem you encounter?

cmbDescrip is the combo box where you select and depending on its value will change cmbProduct combo box right? You mention category combo box but I do not see it but I do see description combo box thus the assumption.

   Private Sub cmbDescrip_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDescrip.SelectedIndexChanged
        Dim cmbcatDescCommand1 As MySqlCommand = New MySqlCommand("Select * from category where catDesc=@field2;", connection)
        Dim reader1 As MySqlDataReader
        Try
            With cmbcatDescCommand1
                .Parameters.AddWithValue("@field2", cmbDescrip.Text)
            End With
            reader1 = cmbcatDescCommand1.ExecuteReader()
            If (reader1.Read()) Then
                txtcatNo.Text = (reader1("catNo"))
            End If
            cmbcatDescCommand1.Dispose()
            reader1.Close()
        Catch ex As Exception
        End Try
    End Sub

In that if statement you shoot populate your cmbProduct.

cmbProduct.add(reader1("catNo").ToString())

[poojavb]

write the following code in ur product combobox selection changeevent as below

    Dim conn As New SqlConnection(ConnectionString)
    Dim category As String
    category = cboCategory.Text
    Dim strSQL As String = "SELECT pt.productidID,pt.ProductName FROM ProductTable pt, categoryTable ct where ct.CategoryName='" & category & "' and pt.categoryid=ct.categoryid"
    Dim da As New SqlDataAdapter(strSQL, conn)
    Dim ds As New DataSet
    da.Fill(ds, "ProductTable")
    With cboDoctorID
        .DataSource = ds.Tables("ProductTable")
        .DisplayMember = "ProductnameName"
        .ValueMember = "productidID"
        '.SelectedIndex = 0
    End With

[Mai Phương]

what is da?