Cascading Combo box in VB.Net 1

[polnichek]

I want to populate a combo box with the results of a selection from a preceeding combo box. My front end is in Visual Studio 2005 and my database is SqlServer 2005. To use an example from the 'pubs' database I would like to slelect An "Author Name" from one list, and then in the next combo box choose from a list of books that Author has written. I can do this in MS Access but I don't know how to do it in VB.Net. Any help is appreciated.

Polnichek

 

[RiverGuy]

One way is to use a DataView as the data source for your second combo box. Change this DataView's .RowFilter property to reflect the selected AuthorID when the selected Author is changed. Here's an example

    Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim MyDataSet As New DataSet
        Dim Authors As New DataTable
        Dim Books As New DataTable
        Dim BooksView As New DataView
        Dim Combo1 As New ComboBox
        Dim Combo2 As New ComboBox

        With Combo1
            .Location = New Point(12, 20)
            .Name = "Combo1"
            .Width = 200
            Me.Controls.Add(Combo1)
            AddHandler Combo1.SelectedIndexChanged, AddressOf Me.Combo1SelectedIndexChanged
        End With

        With Combo2
            .Location = New Point(12, 60)
            .Name = "Combo2"
            .Width = 200
            Me.Controls.Add(Combo2)
        End With

        With Authors
            .TableName = "Authors"
            .Columns.Add("AuthorID", System.Type.GetType("System.Int32"))
            .Columns.Add("AuthorName", System.Type.GetType("System.String"))
            .Rows.Add(New Object() {1, "Author1"})
            .Rows.Add(New Object() {2, "Author2"})
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With Books
            .TableName = "books"
            .Columns.Add("BookID", System.Type.GetType("System.Int32"))
            .Columns.Add("AuthorID", System.Type.GetType("System.Int32"))
            .Columns.Add("BookName", System.Type.GetType("System.String"))
            .Rows.Add(New Object() {1, 1, "Author 1's first book"})
            .Rows.Add(New Object() {2, 1, "Author 1's second book"})
            .Rows.Add(New Object() {3, 2, "Author 2's first book"})
            .Rows.Add(New Object() {4, 2, "Author 2's second book"})
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With BooksView
            .Table = Books
        End With

        MyDataSet.Tables.Add(Authors)
        MyDataSet.Tables.Add(Books)


        With Combo1
            .DisplayMember = "AuthorName"
            .ValueMember = "AuthorID"
            .DataSource = Authors
        End With

        With Combo2
            .DisplayMember = "BookName"
            .ValueMember = "BookID"
            .DataSource = BooksView
        End With
        Call Me.SetCombo2(Combo1)
    End Sub
    Private Sub SetCombo2(ByVal sender As Object)
        Dim combo1 As ComboBox = sender
        Dim combo2 As ComboBox = Me.Controls("Combo2")
        Dim dv As DataView = combo2.DataSource
        Try
            dv.RowFilter = "AuthorID = " & CType(combo1.Items(combo1.SelectedIndex), DataRowView).Item("AuthorID")
        Catch ex As Exception
            If Not dv Is Nothing Then dv.RowFilter = "AuthorID = -1"
        End Try
    End Sub
    Private Sub Combo1SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        Call Me.SetCombo2(sender)
    End Sub