How to Databind a combobox to a child table, while showing values from the associated parent table?

Boruch Tkatch 141 Reputation points
2023-11-28T21:50:17.5833333+00:00

I am trying to databind a combobox to one table while showing data from another table. The other table has all the possible values. Here is a sample program:

Public Class Form1
    Private ReadOnly DataSet As New DataSet
    Private ReadOnly BindingSource As New BindingSource()

    Private ReadOnly Grid As New DataGridView()
    Private ReadOnly TB As New TextBox() With {.Location = New Point(Grid.Location.X, Grid.Location.Y + Grid.Height)}
    Private ReadOnly CB1 As New ComboBox() With {.Location = New Point(Grid.Location.X, TB.Location.Y + TB.Height)}
    Private ReadOnly CB2 As New ComboBox() With {.Location = New Point(CB1.Location.X + CB1.Width, CB1.Location.Y)}
    Private ReadOnly CB3 As New ComboBox() With {.Location = New Point(CB1.Location.X, CB1.Location.Y + CB1.Height)}
    Private ReadOnly CB4 As New ComboBox() With {.Location = New Point(CB3.Location.X + CB3.Width, CB3.Location.Y)}

    Private Sub Form1_Load(Sender As Object, Arguments As EventArgs) Handles MyBase.Load
        Controls.AddRange({Grid, TB, CB1, CB2, CB3, CB4})
        Setup_Dataset()

        BindingSource.DataSource = DataSet

        Grid.DataSource = BindingSource
        Grid.DataMember = "Child"

        TB.DataBindings.Add("Text", BindingSource, "Child.Name")

        ' Bound, no dropdown.
        CB1.DataBindings.Add("Text", BindingSource, "Child.Parent")

        ' Bound, dropdown from child table
        CB2.DataBindings.Add("Text", BindingSource, "Child.Parent")
        CB2.DataSource = BindingSource
        CB2.DisplayMember = "Child.Parent"

        ' Bound, dropdown from parent table
        CB3.DataBindings.Add("Text", BindingSource, "Child.Parent")
        'CB3.DataSource = BindingSource
        CB3.DisplayMember = "Parent.Name"

        ' Bound, dropdown from parent table, new binding source
        CB4.DataBindings.Add("Text", BindingSource, "Child.Parent")
        'CB4.DataSource = New BindingSource(BindingSource, "Parent")
        CB4.DisplayMember = "Name"
    End Sub

    Private Sub Setup_Dataset()
        With DataSet
            .Tables.Add(New DataTable("Parent"))
            With .Tables("Parent")
                .Columns.Add("Name", GetType(String))
                .PrimaryKey = { .Columns("Name")}

                .Rows.Add("A")
                .Rows.Add("B")
            End With

            .Tables.Add(New DataTable("Child"))
            With .Tables("Child")
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Parent", GetType(String))
                .PrimaryKey = { .Columns("Name")}

                .Rows.Add({"AA", "B"})
                .Rows.Add({"BB", "A"})
                .Rows.Add({"CC", "A"})
            End With

            .Relations.Add(.Tables("Parent").Columns("Name"), .Tables("Child").Columns("Parent"))
        End With
    End Sub
End Class

Running this code will show a form:

Untitled.png

The grid shows the Child table. The textbox shows whatever name is, to show that databinding is working.
All comboboxes are databound to Child.Parent and should show its value.
The first combobox shows the value. The dropdown menu is empty, as expected.
The next combobox in the same row shows Child.Parent, and so it shows all values in the Child table. This is not what i want to do, but it shows the two parts working in tandem:

Untitled.png

The next row of comboboxes try to show the values from the Parent table. If DataSource is not set, it shows the bound column, but nothing in the drop down.
If the commented out lines setting DataSource are uncommented, the dropdowns show the values from the Parent table, but the displayed value is not the current value in child:
Untitled.png

The DataBinding itself works though, because if the next row is highlighted, it changes the value:Untitled.png

How do i DataBind a combobox to a child table and shows the values from the parent table?

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,652 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Jiachen Li-MSFT 28,781 Reputation points Microsoft Vendor
    2023-11-29T03:50:16.18+00:00

    Hi @Boruch Tkatch ,

    A more convenient way is to directly set the Parent as the data source for CB1 and control the selection through the DataGridView event.

        Private Sub Form1_Load(Sender As Object, Arguments As EventArgs) Handles MyBase.Load
            Controls.AddRange({Grid, TB, CB1, CB2, CB3, CB4})
            Setup_Dataset()
    
            Grid.DataSource = DataSet.Tables("Child")
    
            AddHandler Grid.CurrentCellChanged, AddressOf DataGridView1_CurrentCellChanged
    
            CB1.DisplayMember = "Name"
            CB1.DataSource = DataSet.Tables("Parent")
        End Sub
    
        Private Sub DataGridView1_CurrentCellChanged(sender As Object, e As EventArgs)
            If Grid.CurrentCell IsNot Nothing Then
                Dim currentRow = Grid.Rows(Grid.CurrentCell.RowIndex)
                Dim parentValue As String = currentRow.Cells("Parent").Value.ToString()
                CB1.SelectedValue = parentValue
            End If
        End Sub
    

    Best Regards.

    Jiachen Li


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Jiachen Li-MSFT 28,781 Reputation points Microsoft Vendor
    2023-11-30T05:03:20.3033333+00:00

    Hi @Boruch Tkatch ,

    You can try using ComboBoxColumn in the datagridview.

        Private Sub Form1_Load(Sender As Object, Arguments As EventArgs) Handles MyBase.Load
            Controls.AddRange({Grid, TB, CB1, CB2, CB3, CB4})
            Setup_Dataset()
    
            Grid.DataSource = DataSet.Tables("Child")
            Dim parentComboBoxColumn As New DataGridViewComboBoxColumn()
            parentComboBoxColumn.HeaderText = "Parent"
            parentComboBoxColumn.DataPropertyName = "Parent"
            parentComboBoxColumn.DataSource = DataSet.Tables("Parent")
            parentComboBoxColumn.DisplayMember = "Name"
            parentComboBoxColumn.ValueMember = "Name"
            Grid.Columns.Add(parentComboBoxColumn)
            Grid.Columns("Parent").Visible = False
        End Sub
    

    Best Regards.

    Jiachen Li


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.