Cascading Combo box in VB.Net
Cascading Combo box in VB.Net
(OP)
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
Talk To Other Members
RE: Cascading Combo box in VB.Net
CODE
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