none
Add Combo Box Selected value to Data Table RRS feed

  • Question

  • Hallo

    I have 3 cascading combo boxes and try to add their SelectedValue to my Datatable by button click. I can add a new row to my datatable but the combobox selected values are not updated/ added to my datatable.   If i add a second row i loose my values set in the firstrow. Could you please tell me what is the correct procedure to set the combobox selected values to datatable? and also move the bindingsource position to newly added row.

    Create dataset

    Public Data_Set As DataSet = New DataSet("Project") Public tbl_Equipment As DataTable = New DataTable("Equipment") Sub Create_DS() With DataSet .Tables.Add(tbl_Equipment) With tbl_Equipment .Columns.Add(dtID, GetType(String)) .Columns.Add(dtName, GetType(String)) .Columns.Add(dtEQID, Type.GetType("System.Int32")) .Columns(dtEQID).AutoIncrement = True .Columns(dtEQID).AutoIncrementSeed = 1 .Columns(dtEQID).AutoIncrementStep = 1 .PrimaryKey = New DataColumn() { .Columns(dtEQID)} .Columns.Add(New DataColumn(dtEqp, Type.GetType("System.String"))) .Columns.Add(New DataColumn(dtTyp, Type.GetType("System.String"))) .Columns.Add(New DataColumn(dtMat, Type.GetType("System.String")))

    End with End with End sub

    Add Databindings

    Public EQ_BS As New BindingSource

    sub data_bindings()

     EQ_BS.DataSource = tbl_Equipment

                With form1.VT_lbx_eqlist
                    .ValueMember = dtEQID
                    .DisplayMember = dtName
                    .DataSource = EQ_BS
                End With

    With form1 .vt_cbx_eq.DataBindings.Add("SelectedValue", EQ_BS, dtEqp, True, DataSourceUpdateMode.OnPropertyChanged) .vt_cbx_mat.DataBindings.Add("SelectedValue", EQ_BS, dtMat, True, DataSourceUpdateMode.OnPropertyChanged) .vt_cbx_type.DataBindings.Add("SelectedValue", EQ_BS, dtTyp, True, DataSourceUpdateMode.OnPropertyChanged) End with AddHandler EQ_BS.BindingComplete, AddressOf BindingComplete End sub

     Sub BindingComplete(ByVal sender As Object, ByVal e As BindingCompleteEventArgs)
          
            If e.BindingCompleteContext = BindingCompleteContext.DataSourceUpdate AndAlso e.Exception Is Nothing Then e.Binding.BindingManagerBase.EndCurrentEdit()

     End Sub

    Add new row by button click

        Sub Add_Equip_DT(Optional ByVal pos As Integer = vbEmpty)
    
            EQ_BS.EndEdit()
    
            Dim tmpEQP As String
            Dim tmpTyP As String
            Dim tmpMAT As String
    
            With Form1
                .vt_cbx_eq.SelectedIndex = 0
                tmpEQP = .vt_cbx_eq.SelectedValue
    
                .vt_cbx_type.SelectedIndex = 0
                tmpTyP = .vt_cbx_type.SelectedValue
    
                .vt_cbx_mat.SelectedIndex = 0
                tmpMAT = .vt_cbx_mat.SelectedValue
    
            End With
    
            Dim nRow = DirectCast(EQ_BS.AddNew(), DataRowView)
            nRow(dtEquipment) = tmpEQP
            nRow(dtType) = tmpTyP
            nRow(dtMaterial) = tmpMAT
            nRow(dtName) = "New Equipment"
            EQ_BS.EndEdit()
    
            '//Set the BS to newly adeed row position 
            With Form1
                EQ_BS.Position = EQ_BS.Find("ID", nRow.Row.ItemArray(1))
            End With
    

    Thanks

    Friday, July 26, 2019 7:06 PM

Answers

  • Hi,
    try this demo:

    Public Class Form1
      Private cb1 As New ComboBox With {.Top = 50, .Width = 150, .DisplayMember = "Master"}
      Private cb2 As New ComboBox With {.Top = 100, .Width = 150, .DisplayMember = "Child"}
      Private cb3 As New ComboBox With {.Top = 150, .Width = 150, .DisplayMember = "GrandChild"}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {cb3, cb2, cb1})
        Dim bs1 As New BindingSource With {.DataSource = GetDataSet(), .DataMember = "Tab1"}
        cb1.DataSource = bs1
        Dim bs2 As New BindingSource With {.DataSource = bs1, .DataMember = "Rel1"}
        cb2.DataSource = bs2
        Dim bs3 As New BindingSource With {.DataSource = bs2, .DataMember = "Rel2"}
        cb3.DataSource = bs3
      End Sub
    
      Private Function GetDataSet() As DataSet
        Dim ds As New DataSet
        With ds
          With .Tables.Add("Tab1")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("Master", GetType(String))
            End With
          End With
          With .Tables.Add("Tab2")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("FKMaster", GetType(Integer))
              .Add("Child", GetType(String))
            End With
          End With
          With .Tables.Add("Tab3")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("FKChild", GetType(Integer))
              .Add("GrandChild", GetType(String))
            End With
          End With
          For i = 0 To 9
            .Tables("Tab1").Rows.Add(i, $"Master {i + 1}")
            For k = i * 10 To i * 10 + 9
              .Tables("Tab2").Rows.Add(k, i, $"Child {i + 1}-{k + 1}")
              For l = k * 10 To k * 10 + 9
                .Tables("Tab3").Rows.Add(l, k, $"Grandchild {i + 1}-{k + 1}-{l + 1}")
              Next
            Next
          Next
          .Relations.Add("Rel1", .Tables("Tab1").Columns("ID"), .Tables("Tab2").Columns("FKMaster"))
          .Relations.Add("Rel2", .Tables("Tab2").Columns("ID"), .Tables("Tab3").Columns("FKChild"))
        End With
        Return ds
      End Function
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, July 29, 2019 8:00 PM

All replies

  • Hi,
    you can use BindingSource.MoveLast after adding new row like in this demo:

    Public Class Form37
    
      Private VT_lbx_eqlist As New ListBox With {.Dock = DockStyle.Bottom}
      Private vt_cbx_eq As New ComboBox With {.Dock = DockStyle.Left}
      Private vt_cbx_mat As New ComboBox With {.Dock = DockStyle.Left}
      Private vt_cbx_type As New ComboBox With {.Dock = DockStyle.Left}
      Private dgv As New DataGridView With {.Dock = DockStyle.Fill}
      Private WithEvents btn As New Button With {.Dock = DockStyle.Top, .Text = "new row"}
    
      Private Sub Form37_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {dgv, btn, vt_cbx_type, vt_cbx_mat, vt_cbx_eq, VT_lbx_eqlist})
        form1 = Me
        LoadComboBoxes()
        LoadDataGridView()
        data_bindings()
      End Sub
    
      Private Sub LoadComboBoxes()
        Dim l1 As New List(Of String)
        For i = 1 To 5
          l1.Add($"Equipment {i}")
        Next
        Me.vt_cbx_eq.DataSource = l1
        Dim l2 As New List(Of String)
        For i = 1 To 5
          l2.Add($"Material {i}")
        Next
        Me.vt_cbx_mat.DataSource = l2
        Dim l3 As New List(Of String)
        For i = 1 To 5
          l3.Add($"Type {i}")
        Next
        Me.vt_cbx_type.DataSource = l3
      End Sub
    
      Private Sub LoadDataGridView()
        Create_DS()
        With Data_Set.Tables(0)
          For i = 1 To 3
            Dim dr = .NewRow
            dr(dtID) = i
            dr(dtName) = $"Name {i}"
            .Rows.Add(dr)
          Next
          dgv.DataSource = .DefaultView
        End With
      End Sub
    
      Private Sub btn_Click(sender As Object, e As EventArgs) Handles btn.Click
        Add_Equip_DT()
      End Sub
    
      Private dtID As String = "ID"
      Private dtName As String = "Name"
      Private dtEQID As String = "EQID"
      Private dtEqp As String = "Eqp"
      Private dtTyp As String = "Typ"
      Private dtMat As String = "Mat"
      Private form1 As Form37
    
      Private dtEquipment As String = "Eqp"
      Private dtType As String = "Typ"
      Private dtMaterial As String = "Mat"
    
    
      Public Data_Set As DataSet = New DataSet("Project")
      Public tbl_Equipment As DataTable = New DataTable("Equipment")
    
      Sub Create_DS()
    
        With Data_Set
    
          .Tables.Add(tbl_Equipment)
          With tbl_Equipment
            .Columns.Add(dtID, GetType(String))
            .Columns.Add(dtName, GetType(String))
    
            .Columns.Add(dtEQID, Type.GetType("System.Int32"))
            .Columns(dtEQID).AutoIncrement = True
            .Columns(dtEQID).AutoIncrementSeed = 1
            .Columns(dtEQID).AutoIncrementStep = 1
            .PrimaryKey = New DataColumn() { .Columns(dtEQID)}
    
            .Columns.Add(New DataColumn(dtEqp, Type.GetType("System.String")))
            .Columns.Add(New DataColumn(dtTyp, Type.GetType("System.String")))
            .Columns.Add(New DataColumn(dtMat, Type.GetType("System.String")))
    
          End With
        End With
    
      End Sub
    
      Public EQ_BS As New BindingSource
      Sub data_bindings()
        EQ_BS.DataSource = tbl_Equipment
        With form1.VT_lbx_eqlist
          .ValueMember = dtEQID
          .DisplayMember = dtName
          .DataSource = EQ_BS
        End With
        With form1
          .vt_cbx_eq.DataBindings.Add("SelectedValue", EQ_BS, dtEqp, True, DataSourceUpdateMode.OnPropertyChanged)
          .vt_cbx_mat.DataBindings.Add("SelectedValue", EQ_BS, dtMat, True, DataSourceUpdateMode.OnPropertyChanged)
          .vt_cbx_type.DataBindings.Add("SelectedValue", EQ_BS, dtTyp, True, DataSourceUpdateMode.OnPropertyChanged)
        End With
        AddHandler EQ_BS.BindingComplete, AddressOf BindingComplete
      End Sub
    
      Sub BindingComplete(ByVal sender As Object, ByVal e As BindingCompleteEventArgs)
        If e.BindingCompleteContext = BindingCompleteContext.DataSourceUpdate AndAlso e.Exception Is Nothing Then e.Binding.BindingManagerBase.EndCurrentEdit()
      End Sub
    
      Sub Add_Equip_DT(Optional ByVal pos As Integer = vbEmpty)
    
        EQ_BS.EndEdit()
    
        Dim tmpEQP As String
        Dim tmpTyP As String
        Dim tmpMAT As String
    
        With form1
          .vt_cbx_eq.SelectedIndex = 0
          tmpEQP = .vt_cbx_eq.SelectedValue.ToString
    
          .vt_cbx_type.SelectedIndex = 0
          tmpTyP = .vt_cbx_type.SelectedValue.ToString
    
          .vt_cbx_mat.SelectedIndex = 0
          tmpMAT = .vt_cbx_mat.SelectedValue.ToString
    
        End With
    
        Dim nRow = DirectCast(EQ_BS.AddNew(), DataRowView)
        nRow(dtEquipment) = tmpEQP
        nRow(dtType) = tmpTyP
        nRow(dtMaterial) = tmpMAT
        nRow(dtName) = "New Equipment"
        EQ_BS.EndEdit()
    
        '//Set the BS to newly adeed row position 
        With form1
          EQ_BS.MoveLast()
        End With
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Sunday, July 28, 2019 9:48 AM
  • Hi,
    you can use BindingSource.MoveLast after adding new row like in this demo:

    Public Class Form37
    
      Private VT_lbx_eqlist As New ListBox With {.Dock = DockStyle.Bottom}
      Private vt_cbx_eq As New ComboBox With {.Dock = DockStyle.Left}
      Private vt_cbx_mat As New ComboBox With {.Dock = DockStyle.Left}
      Private vt_cbx_type As New ComboBox With {.Dock = DockStyle.Left}
      Private dgv As New DataGridView With {.Dock = DockStyle.Fill}
      Private WithEvents btn As New Button With {.Dock = DockStyle.Top, .Text = "new row"}
    
      Private Sub Form37_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {dgv, btn, vt_cbx_type, vt_cbx_mat, vt_cbx_eq, VT_lbx_eqlist})
        form1 = Me
        LoadComboBoxes()
        LoadDataGridView()
        data_bindings()
      End Sub
    
      Private Sub LoadComboBoxes()
        Dim l1 As New List(Of String)
        For i = 1 To 5
          l1.Add($"Equipment {i}")
        Next
        Me.vt_cbx_eq.DataSource = l1
        Dim l2 As New List(Of String)
        For i = 1 To 5
          l2.Add($"Material {i}")
        Next
        Me.vt_cbx_mat.DataSource = l2
        Dim l3 As New List(Of String)
        For i = 1 To 5
          l3.Add($"Type {i}")
        Next
        Me.vt_cbx_type.DataSource = l3
      End Sub
    
      Private Sub LoadDataGridView()
        Create_DS()
        With Data_Set.Tables(0)
          For i = 1 To 3
            Dim dr = .NewRow
            dr(dtID) = i
            dr(dtName) = $"Name {i}"
            .Rows.Add(dr)
          Next
          dgv.DataSource = .DefaultView
        End With
      End Sub
    
      Private Sub btn_Click(sender As Object, e As EventArgs) Handles btn.Click
        Add_Equip_DT()
      End Sub
    
      Private dtID As String = "ID"
      Private dtName As String = "Name"
      Private dtEQID As String = "EQID"
      Private dtEqp As String = "Eqp"
      Private dtTyp As String = "Typ"
      Private dtMat As String = "Mat"
      Private form1 As Form37
    
      Private dtEquipment As String = "Eqp"
      Private dtType As String = "Typ"
      Private dtMaterial As String = "Mat"
    
    
      Public Data_Set As DataSet = New DataSet("Project")
      Public tbl_Equipment As DataTable = New DataTable("Equipment")
    
      Sub Create_DS()
    
        With Data_Set
    
          .Tables.Add(tbl_Equipment)
          With tbl_Equipment
            .Columns.Add(dtID, GetType(String))
            .Columns.Add(dtName, GetType(String))
    
            .Columns.Add(dtEQID, Type.GetType("System.Int32"))
            .Columns(dtEQID).AutoIncrement = True
            .Columns(dtEQID).AutoIncrementSeed = 1
            .Columns(dtEQID).AutoIncrementStep = 1
            .PrimaryKey = New DataColumn() { .Columns(dtEQID)}
    
            .Columns.Add(New DataColumn(dtEqp, Type.GetType("System.String")))
            .Columns.Add(New DataColumn(dtTyp, Type.GetType("System.String")))
            .Columns.Add(New DataColumn(dtMat, Type.GetType("System.String")))
    
          End With
        End With
    
      End Sub
    
      Public EQ_BS As New BindingSource
      Sub data_bindings()
        EQ_BS.DataSource = tbl_Equipment
        With form1.VT_lbx_eqlist
          .ValueMember = dtEQID
          .DisplayMember = dtName
          .DataSource = EQ_BS
        End With
        With form1
          .vt_cbx_eq.DataBindings.Add("SelectedValue", EQ_BS, dtEqp, True, DataSourceUpdateMode.OnPropertyChanged)
          .vt_cbx_mat.DataBindings.Add("SelectedValue", EQ_BS, dtMat, True, DataSourceUpdateMode.OnPropertyChanged)
          .vt_cbx_type.DataBindings.Add("SelectedValue", EQ_BS, dtTyp, True, DataSourceUpdateMode.OnPropertyChanged)
        End With
        AddHandler EQ_BS.BindingComplete, AddressOf BindingComplete
      End Sub
    
      Sub BindingComplete(ByVal sender As Object, ByVal e As BindingCompleteEventArgs)
        If e.BindingCompleteContext = BindingCompleteContext.DataSourceUpdate AndAlso e.Exception Is Nothing Then e.Binding.BindingManagerBase.EndCurrentEdit()
      End Sub
    
      Sub Add_Equip_DT(Optional ByVal pos As Integer = vbEmpty)
    
        EQ_BS.EndEdit()
    
        Dim tmpEQP As String
        Dim tmpTyP As String
        Dim tmpMAT As String
    
        With form1
          .vt_cbx_eq.SelectedIndex = 0
          tmpEQP = .vt_cbx_eq.SelectedValue.ToString
    
          .vt_cbx_type.SelectedIndex = 0
          tmpTyP = .vt_cbx_type.SelectedValue.ToString
    
          .vt_cbx_mat.SelectedIndex = 0
          tmpMAT = .vt_cbx_mat.SelectedValue.ToString
    
        End With
    
        Dim nRow = DirectCast(EQ_BS.AddNew(), DataRowView)
        nRow(dtEquipment) = tmpEQP
        nRow(dtType) = tmpTyP
        nRow(dtMaterial) = tmpMAT
        nRow(dtName) = "New Equipment"
        EQ_BS.EndEdit()
    
        '//Set the BS to newly adeed row position 
        With form1
          EQ_BS.MoveLast()
        End With
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Thanks peter. EQ_BS.MoveLast() works fine.
    Sunday, July 28, 2019 6:59 PM
  • my problem is setting cascading combobox values to datatable. Here is my simplified example. Cascading combobox works. What i wanted to acheive here is , after adding a new row to datatable when i select Author in first comobox ,2nd combobox is automatically populated and first author book is set, but this value is not added to datatable and this is my problem. Could you please correct the problem?.

    Here is my code just copy paste and add datagridview1 and add button and test.

    Public Class Form1 Public DataSet As New DataSet Public tbl_Books As New DataTable Public BS As New BindingSource

    Private Sub Form1_Load(sender As Object, e As 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) With DataSet .Tables.Add(tbl_Books) With tbl_Books .Columns.Add("Book", GetType(String)) .Columns("Book").DefaultValue = DBNull.Value .Columns.Add("Author", GetType(String)) .Columns("Author").DefaultValue = DBNull.Value End With End With BS.DataSource = tbl_Books DataGridView1.DataSource = BS Combo1.DataBindings.Add("SelectedValue", BS, "Book", True, DataSourceUpdateMode.OnPropertyChanged) Combo2.DataBindings.Add("SelectedValue", BS, "Author", True, DataSourceUpdateMode.OnPropertyChanged) 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 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim nRow = DirectCast(BS.AddNew(), DataRowView) BS.EndEdit() End Sub End Class

    Thanks


    • Edited by Shan1986 Sunday, July 28, 2019 7:07 PM
    Sunday, July 28, 2019 7:06 PM
  • Hi,
    the simplest way is to use DataRelation in DataSet like in this demo:

    Public Class Form1
    
      Private Sub Form1_Load(sender As Object, e As 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)
    
        ' Set Relation
        MyDataSet.Relations.Add("Rel", Authors.Columns("AuthorID"), Books.Columns("AuthorID"))
    
        Dim bs As New BindingSource With {
          .DataSource = MyDataSet,
          .DataMember = "Authors"}
    
        With Combo1
          .DisplayMember = "AuthorName"
          .ValueMember = "AuthorID"
          .DataSource = bs
        End With
    
        With Combo2
          .DisplayMember = "Rel.BookName"
          .ValueMember = "Rel"
          .DataSource = bs
        End With
    
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, July 29, 2019 3:20 AM
  • Hi,
    the simplest way is to use DataRelation in DataSet like in this demo:

    Public Class Form1
    
      Private Sub Form1_Load(sender As Object, e As 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)
    
        ' Set Relation
        MyDataSet.Relations.Add("Rel", Authors.Columns("AuthorID"), Books.Columns("AuthorID"))
    
        Dim bs As New BindingSource With {
          .DataSource = MyDataSet,
          .DataMember = "Authors"}
    
        With Combo1
          .DisplayMember = "AuthorName"
          .ValueMember = "AuthorID"
          .DataSource = bs
        End With
    
        With Combo2
          .DisplayMember = "Rel.BookName"
          .ValueMember = "Rel"
          .DataSource = bs
        End With
    
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Hallo Peter, Thanks. I have 3 comboboxes and i have added two relations and how do i add them to a binding source?

    .Relations.Add("EQP2TYP", .Tables("EQ_tbl").Columns("eID"), .Tables("EQTYP_tbl").Columns("eID"))
    .Relations.Add("TYP2MAT",.Tables("EQTYP_tbl").Columns("tID"), .Tables("MAT_tbl").Columns("tID"))

    now how do i modify this line ?

    Dim bs As New BindingSource With {.DataSource = XML_DS, .DataMember = "EQ_tbl"}

    Thanks

    Monday, July 29, 2019 5:31 PM
  • Hi,
    try this demo:

    Public Class Form1
      Private cb1 As New ComboBox With {.Top = 50, .Width = 150, .DisplayMember = "Master"}
      Private cb2 As New ComboBox With {.Top = 100, .Width = 150, .DisplayMember = "Child"}
      Private cb3 As New ComboBox With {.Top = 150, .Width = 150, .DisplayMember = "GrandChild"}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {cb3, cb2, cb1})
        Dim bs1 As New BindingSource With {.DataSource = GetDataSet(), .DataMember = "Tab1"}
        cb1.DataSource = bs1
        Dim bs2 As New BindingSource With {.DataSource = bs1, .DataMember = "Rel1"}
        cb2.DataSource = bs2
        Dim bs3 As New BindingSource With {.DataSource = bs2, .DataMember = "Rel2"}
        cb3.DataSource = bs3
      End Sub
    
      Private Function GetDataSet() As DataSet
        Dim ds As New DataSet
        With ds
          With .Tables.Add("Tab1")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("Master", GetType(String))
            End With
          End With
          With .Tables.Add("Tab2")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("FKMaster", GetType(Integer))
              .Add("Child", GetType(String))
            End With
          End With
          With .Tables.Add("Tab3")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("FKChild", GetType(Integer))
              .Add("GrandChild", GetType(String))
            End With
          End With
          For i = 0 To 9
            .Tables("Tab1").Rows.Add(i, $"Master {i + 1}")
            For k = i * 10 To i * 10 + 9
              .Tables("Tab2").Rows.Add(k, i, $"Child {i + 1}-{k + 1}")
              For l = k * 10 To k * 10 + 9
                .Tables("Tab3").Rows.Add(l, k, $"Grandchild {i + 1}-{k + 1}-{l + 1}")
              Next
            Next
          Next
          .Relations.Add("Rel1", .Tables("Tab1").Columns("ID"), .Tables("Tab2").Columns("FKMaster"))
          .Relations.Add("Rel2", .Tables("Tab2").Columns("ID"), .Tables("Tab3").Columns("FKChild"))
        End With
        Return ds
      End Function
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, July 29, 2019 8:00 PM
  • Hi,
    try this demo:

    Public Class Form1
      Private cb1 As New ComboBox With {.Top = 50, .Width = 150, .DisplayMember = "Master"}
      Private cb2 As New ComboBox With {.Top = 100, .Width = 150, .DisplayMember = "Child"}
      Private cb3 As New ComboBox With {.Top = 150, .Width = 150, .DisplayMember = "GrandChild"}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {cb3, cb2, cb1})
        Dim bs1 As New BindingSource With {.DataSource = GetDataSet(), .DataMember = "Tab1"}
        cb1.DataSource = bs1
        Dim bs2 As New BindingSource With {.DataSource = bs1, .DataMember = "Rel1"}
        cb2.DataSource = bs2
        Dim bs3 As New BindingSource With {.DataSource = bs2, .DataMember = "Rel2"}
        cb3.DataSource = bs3
      End Sub
    
      Private Function GetDataSet() As DataSet
        Dim ds As New DataSet
        With ds
          With .Tables.Add("Tab1")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("Master", GetType(String))
            End With
          End With
          With .Tables.Add("Tab2")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("FKMaster", GetType(Integer))
              .Add("Child", GetType(String))
            End With
          End With
          With .Tables.Add("Tab3")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("FKChild", GetType(Integer))
              .Add("GrandChild", GetType(String))
            End With
          End With
          For i = 0 To 9
            .Tables("Tab1").Rows.Add(i, $"Master {i + 1}")
            For k = i * 10 To i * 10 + 9
              .Tables("Tab2").Rows.Add(k, i, $"Child {i + 1}-{k + 1}")
              For l = k * 10 To k * 10 + 9
                .Tables("Tab3").Rows.Add(l, k, $"Grandchild {i + 1}-{k + 1}-{l + 1}")
              Next
            Next
          Next
          .Relations.Add("Rel1", .Tables("Tab1").Columns("ID"), .Tables("Tab2").Columns("FKMaster"))
          .Relations.Add("Rel2", .Tables("Tab2").Columns("ID"), .Tables("Tab3").Columns("FKChild"))
        End With
        Return ds
      End Function
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Thanks Peter, by this way it works very well. 
    Tuesday, July 30, 2019 4:23 PM
  • Hi,
    try this demo:

    Public Class Form1
      Private cb1 As New ComboBox With {.Top = 50, .Width = 150, .DisplayMember = "Master"}
      Private cb2 As New ComboBox With {.Top = 100, .Width = 150, .DisplayMember = "Child"}
      Private cb3 As New ComboBox With {.Top = 150, .Width = 150, .DisplayMember = "GrandChild"}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {cb3, cb2, cb1})
        Dim bs1 As New BindingSource With {.DataSource = GetDataSet(), .DataMember = "Tab1"}
        cb1.DataSource = bs1
        Dim bs2 As New BindingSource With {.DataSource = bs1, .DataMember = "Rel1"}
        cb2.DataSource = bs2
        Dim bs3 As New BindingSource With {.DataSource = bs2, .DataMember = "Rel2"}
        cb3.DataSource = bs3
      End Sub
    
      Private Function GetDataSet() As DataSet
        Dim ds As New DataSet
        With ds
          With .Tables.Add("Tab1")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("Master", GetType(String))
            End With
          End With
          With .Tables.Add("Tab2")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("FKMaster", GetType(Integer))
              .Add("Child", GetType(String))
            End With
          End With
          With .Tables.Add("Tab3")
            With .Columns
              .Add("ID", GetType(Integer))
              .Add("FKChild", GetType(Integer))
              .Add("GrandChild", GetType(String))
            End With
          End With
          For i = 0 To 9
            .Tables("Tab1").Rows.Add(i, $"Master {i + 1}")
            For k = i * 10 To i * 10 + 9
              .Tables("Tab2").Rows.Add(k, i, $"Child {i + 1}-{k + 1}")
              For l = k * 10 To k * 10 + 9
                .Tables("Tab3").Rows.Add(l, k, $"Grandchild {i + 1}-{k + 1}-{l + 1}")
              Next
            Next
          Next
          .Relations.Add("Rel1", .Tables("Tab1").Columns("ID"), .Tables("Tab2").Columns("FKMaster"))
          .Relations.Add("Rel2", .Tables("Tab2").Columns("ID"), .Tables("Tab3").Columns("FKChild"))
        End With
        Return ds
      End Function
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Hallo Peter,  Is there a way to detect or fire a change event when 3rd combobox index changes? because when i change first or 2nd combobox , 3rd combobox index changes automatically ..but it does not fire a 3rd combo box change event. Any idea? Thanks.
    Tuesday, September 10, 2019 7:44 PM