Databind combobox to child table, show values from parent table

[chacham]

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?

 

[chacham]

Assuming there is no way to use DataBinding in this case, the solution is to do it manually:

1. Add a CurrentCellChanged handler to the DataGridView that sets the ComboBox's SelectedValue.
2. Add a SelectedIndexChanged handler to the ComboBox that sets the DataGridView's CurrentRow's value for the appropriate cell.
3. Set the initial SelectedValue of the ComboBox to the value in the DataGridView's value in the appropriate cell.

For example:

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

    Private ReadOnly Grid As New DataGridView()
    Private ReadOnly CB As New ComboBox() With {.Location = New Point(Grid.Location.X, Grid.Location.Y + Grid.Height)}

    Private Sub Form1_Load(Sender As Object, Arguments As EventArgs) Handles MyBase.Load
        Controls.AddRange({Grid, CB})
        Setup_Dataset()

        BindingSource.DataSource = DataSet

        Grid.DataSource = BindingSource
        Grid.DataMember = "Child"
        AddHandler Grid.CurrentCellChanged, Sub() If Not Grid.CurrentRow.IsNewRow Then CB.SelectedValue = Grid.Rows(Grid.CurrentRow.Index).Cells("Parent").Value.ToString

        CB.DataSource = BindingSource
        CB.DisplayMember = "Parent.Name"
        CB.ValueMember = "Name"
        CB.SelectedValue = Grid.Rows(Grid.CurrentCell.RowIndex).Cells("Parent").Value.ToString
        AddHandler CB.SelectedIndexChanged, Sub() Grid.CurrentRow.Cells("Parent").Value = CB.SelectedValue
    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