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

Question

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:

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:

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:

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

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

Answer 1

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.

Answer 2

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.