Combo box to display items from parent combo box

Gini · June 18th, 2008, 12:30 AM

When I select an item from the first combo box, the second combo box should be populated with items related to the first combo box selection. I am developing a system with a database, and the datasource is 'dsInventory'. This is where all the information to be displayed is retrieved. And there are binding sources to bind the datasource items to the combo boxes.

My question is how do I make the second combo box display ONLY sub values of each value of the first combo box.

But now the problem that I face is that all the items from the first combo box display the same sub items in the second combo box when it is selected. Ex :

First Combo Box selections :

COUVETURE
CREAM
DOUGH
BISCUITS
PM
CC , etc

Second Combo box :

If 'PM' from the first combo box is selected, then the items that should display are :

B
C
L
P
W

Now...I choose any item from the first combo box. The same list of items of the second combo box (B,C,L,P,W) display for all the items from the first combo box.

The idea is to just display the items related to the 1st combo box.

The software I have used in VB.net 2008 and Sql Server 2000.

Below is the coding for the section I want working. The bold section of the code is where the data source is linked to the table adapters.

Code:

Imports System.Windows.Forms.DataGrid
Imports System.Data.SqlClient

Public Class InventoryMainForm
    Private CatCodeDesc As String
    Private CatCode As String
    Private WarehouseLocation As String
    Private Warehouse As String
    Private UOMCode As String
    Private subcatcode As String
    Private subcat1code As String
    Private subcat2code As String
    Private subcat3code As String
    Private AddNew As Boolean
    Dim DQcheck As New SqlCommand
    Dim DHcheck As New SqlDataAdapter
    Dim DScheck As New DataSet
    Dim Dvcheck As New DataView

Private Sub InventoryMainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Me.CountryTableAdapter.Fill(Me.DsSales.country)
            Me.Inv_itemsubcategoryTableAdapter.Fill(Me.dsInventory.inv_itemsubcategory)
            Me.Inv_itemlocTableAdapter.Fill(Me.dsInventory.inv_itemloc)
            Me.Inv_itemuomTableAdapter.Fill(Me.dsInventory.inv_itemuom)
            Me.Inv_itemcategoryTableAdapter.Fill(Me.dsInventory.inv_itemcategory)
            Me.Inv_itemCat3TableAdapter.Fill(Me.dsInventory.inv_itemCat3)
            Me.Inv_itemCat4TableAdapter.Fill(Me.dsInventory.inv_itemCat4)
            Me.Inv_itemCat5TableAdapter.Fill(Me.dsInventory.inv_itemCat5)
        Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text + " FrmLoad", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub

 '=========Inventory Item Sub Category===========
    Private Sub subcatBindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorAddNewItem.Click
        subcatcode = cmbsubcatcode.SelectedValue
        cmbsubcatcode.Visible = False
        txtsubcat.Visible = True
        cmbitemcat.Text = ""
        txtsubcat.Text = ""
        txtsubcatdesc.Text = ""
        subcat(False)
        AddNew = True
        cmbitemcat.Focus()
    End Sub

    Private Sub subcat(ByVal check As Boolean)

        dgsubcategory.Enabled = check
        BindingNavigatorMoveFirstItem1.Enabled = check
        BindingNavigatorMoveLastItem1.Enabled = check
        BindingNavigatorMoveNextItem1.Enabled = check
        BindingNavigatorMovePreviousItem1.Enabled = check
        BindingNavigatorCountItem1.Enabled = check
        BindingNavigatorPositionItem1.Enabled = check

        subcatBindingNavigatorRefre****em.Visible = check
        subcatBindingNavigatorAddNewItem.Visible = check
        subcatBindingNavigatorEditItem.Visible = check
        cmbsubcatcode.Visible = check
        txtitemcat.Visible = check
        If check = False Then
            check = True
            subcatBindingNavigatorDeleteItem.Text = "Cancel"
        Else
            check = False
            subcatBindingNavigatorDeleteItem.Text = "Delete"
        End If

        subcatBindingNavigatorSaveItem.Visible = check
        txtsubcat.Visible = check
        cmbitemcat.Visible = check
        txtsubcatdesc.Enabled = check
    End Sub

    Private Sub subcatBindingNavigatorEditItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorEditItem.Click
        If dgsubcategory.RowCount > 0 Then
            subcatcode = cmbsubcatcode.SelectedValue
            subcat(False)
            txtsubcat.Enabled = False
            cmbitemcat.Enabled = False
            cmbitemcat.Focus()
        Else
            MessageBox.Show("No record to edit.", Me.Text + " - Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub subcatBindingNavigatorDeleteItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorDeleteItem.Click
        Try
            If dgsubcategory.RowCount > 0 Then
                If subcatBindingNavigatorDeleteItem.Text = "Delete" Then
                    If MessageBox.Show("Are you sure to delete this record?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.Yes Then
                        gSQLstr = "SELECT COUNT(iim_subcat) FROM inv_itemmaster WHERE  iim_subcat = " & cSQL(cmbsubcatcode.Text) & ""
                        If gSQLcon.State = ConnectionState.Closed Then
                            gSQLcon.Open()
                        End If
                        DQcheck.Connection = gSQLcon
                        DQcheck.CommandText = gSQLstr
                        DHcheck.SelectCommand = DQcheck
                        DHcheck.Fill(DScheck, ("subcat_list"))
                        Dvcheck.Table = DScheck.Tables("subcat_list")
                        If Dvcheck.Table.Rows(0).Item(0) > 0 Then
                            MessageBox.Show("Record cannot be deleted!", Me.Text + " - Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Else
                            InvitemsubcategoryBindingSource.RemoveCurrent()
                            Inv_itemsubcategoryTableAdapter.Update(dsInventory)
                            MessageBox.Show("Record has been deleted.", "Delete", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        End If
                        DScheck.Clear()
                        gSQLcon.Close()
                    End If
                Else
                    If AddNew = True Then
                        AddNew = False
                    End If
                    subcat(True)
                    txtsubcatdesc.Enabled = False
                    refreshsubcat()
                    cmbsubcatcode.SelectedValue = subcatcode

                End If
            Else
                MessageBox.Show("No record to delete.", Me.Text + " - Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text + " - Item Category Level 2(Delete)", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub

    Private Sub subcatBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorSaveItem.Click
        Dim strmsg As String = ""
        Try
            If MessageBox.Show("Save Item?", "Save", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.Yes Then
                If AddNew = True Then
                    If txtsubcat.Text.Trim = "" Then
                        strmsg = strmsg & "Required field - Category Code." & vbCrLf
                    Else
                        gSQLstr = "SELECT COUNT(iis_code) FROM inv_itemsubcategory WHERE  iis_code = " & cSQL(txtsubcat.Text) & ""
                        If gSQLcon.State = ConnectionState.Closed Then
                            gSQLcon.Open()
                        End If
                        DQcheck.Connection = gSQLcon
                        DQcheck.CommandText = gSQLstr
                        DHcheck.SelectCommand = DQcheck
                        DHcheck.Fill(DScheck, ("subcat_list"))
                        Dvcheck.Table = DScheck.Tables("subcat_list")


                        If Dvcheck.Table.Rows(0).Item(0) > 0 Then
                            strmsg = strmsg & "Required field - Category Code already exits, please assign another Category Code!" & vbCrLf
                        End If
                        DScheck.Clear()
                        gSQLcon.Close()
                    End If
                End If
                If cmbitemcat.Text.Trim = "" Then
                    strmsg = strmsg & "Required field - Level 1 Category." & vbCrLf
                End If
                If txtsubcatdesc.Text.Trim = "" Then
                    strmsg = strmsg & "Required field - Description." & vbCrLf
                End If
                If strmsg = "" Then
                    If AddNew = True Then
                        dsInventory.inv_itemsubcategory.Addinv_itemsubcategoryRow(Trim(txtsubcat.Text), txtsubcatdesc.Text, cmbitemcat.Text)
                        Inv_itemsubcategoryTableAdapter.Update(dsInventory.inv_itemsubcategory)
                        Inv_itemsubcategoryTableAdapter.Fill(dsInventory.inv_itemsubcategory)
                        AddNew = False
                    Else
                        InvitemsubcategoryBindingSource.EndEdit()
                        Inv_itemsubcategoryTableAdapter.Update(dsInventory.inv_itemsubcategory)
                    End If
                    MessageBox.Show("Record saved", "Save", MessageBoxButtons.OK, MessageBoxIcon.None)
                    subcat(True)
                    cmbsubcatcode.Enabled = True
                    cmbsubcatcode.SelectedValue = subcatcode
                Else
                    MessageBox.Show(strmsg, Me.Text + " -Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
            End If
        Catch ex As Exception
            If ex.Message.Contains("Column 'iis_code, iis_itemcode' is constrained to be unique. ") = True Then
                MessageBox.Show("The code already exits, please assign another code!", Me.Text + " - Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            Else
                MessageBox.Show(ex.Message, Me.Text + " - Item Category Level 2(Save)", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End Try
    End Sub

    Private Sub subcatBindingNavigatorRefre****em_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorRefre****em.Click
        refreshsubcat()
    End Sub

    Private Sub refreshsubcat()
        Try
            Inv_itemsubcategoryTableAdapter.Fill(dsInventory.inv_itemsubcategory)
        Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text + " - Item Category Level 2(Refresh)", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub

    Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
        Me.Close()
    End Sub

Please help me solve this problem.

Thanks.