How do you databind a textbox to a column in a related table?

Question

How do you databind a textbox to a column in a related table?

Boruch Tkatch 141

I am trying to learn how to bind a control to a related table and show the corresponding record. In the following example, 2 tables, A and B, each have one column and the 2 columns are related. A DataGridView shows table B, one TextBox shows the corresponding column in B, the other TextBox is meant to show the corresponding column in A, but it just shows the first record in A instead.

Public Class Form1
    Private ReadOnly DataSet As New DataSet

    Private ReadOnly Grid As New DataGridView
    Private ReadOnly B_A As New TextBox With {.Location = New Point(Grid.Location.X, Grid.Location.Y + Grid.Size.Height)}
    Private ReadOnly A_Id As New TextBox With {.Location = New Point(B_A.Location.X + B_A.Size.Width, B_A.Location.Y)}

    Private D As New BindingSource() With {.DataSource = DataSet}

    Private Sub Form1_Load(Sender As Object, Arguments As EventArgs) Handles MyBase.Load
        Setup_DataSet()

        A_Id.DataBindings.Add("Text", D, "A.Id")
        B_A.DataBindings.Add("Text", D, "B.A")

        Grid.DataSource = D
        Grid.DataMember = "B"

        Controls.AddRange({Grid, A_Id, B_A})
    End Sub

    Private Sub Setup_DataSet()
        DataSet.Tables.Add(New DataTable("A"))
        DataSet.Tables.Add(New DataTable("B"))

        DataSet.Tables("A").Columns.Add("Id", GetType(Integer))
        DataSet.Tables("B").Columns.Add("A", GetType(Integer))

        DataSet.Tables("A").Rows.Add(1)
        DataSet.Tables("A").Rows.Add(2)
        DataSet.Tables("A").Rows.Add(3)

        DataSet.Tables("B").Rows.Add(2)
        DataSet.Tables("B").Rows.Add(3)

        DataSet.Tables("A").PrimaryKey = {DataSet.Tables("A").Columns("Id")}
        DataSet.Relations.Add(New DataRelation("B_A", DataSet.Tables("A").Columns("Id"), DataSet.Tables("B").Columns("A")))
    End Sub
End Class

User's image

The initial display shows 2 and 3 in the first TextBox, corresponding to the selected record. However, the second TextBox just shows 1, which is the first record in A.

Is it possible to bind the second TextBox to show the corresponding column in A?

Anonymous

2023-11-06T20:52:18.53+00:00

Delete - redundant
Boruch Tkatch 141 Reputation points

2023-11-06T20:55:18.7133333+00:00

Delete - redundant

Boruch Tkatch 141

Okay, the way to relate a table is to use the same BindingSource but make the DataMember the DataRelation. To demonstrate, here is the same code except the DataGridView shows A, not B:

Public Class Form1
    Private ReadOnly DataSet As New DataSet

    Private ReadOnly Grid As New DataGridView
    Private ReadOnly B_A As New TextBox With {.Location = New Point(Grid.Location.X, Grid.Location.Y + Grid.Size.Height)}
    Private ReadOnly A_Id As New TextBox With {.Location = New Point(B_A.Location.X + B_A.Size.Width, B_A.Location.Y)}

    Private BS_A, BS_B As BindingSource

    Private Sub Form1_Load(Sender As Object, Arguments As EventArgs) Handles MyBase.Load
        Setup_DataSet()

        BS_A = New BindingSource() With {.DataSource = DataSet, .DataMember = "A"}
        BS_B = New BindingSource() With {.DataSource = BS_A, .DataMember = "B_A"}

        Grid.DataSource = BS_A

        A_Id.DataBindings.Add("Text", BS_A, "Id")
        B_A.DataBindings.Add("Text", BS_B, "A")

        Controls.AddRange({Grid, A_Id, B_A})
    End Sub

    Private Sub Setup_DataSet()
        DataSet.Tables.Add(New DataTable("A"))
        DataSet.Tables.Add(New DataTable("B"))

        DataSet.Tables("A").Columns.Add("Id", GetType(Integer))
        DataSet.Tables("B").Columns.Add("A", GetType(Integer))

        DataSet.Tables("A").Rows.Add(1)
        DataSet.Tables("A").Rows.Add(2)
        DataSet.Tables("A").Rows.Add(3)

        DataSet.Tables("B").Rows.Add(2)
        DataSet.Tables("B").Rows.Add(3)

        DataSet.Tables("A").PrimaryKey = {DataSet.Tables("A").Columns("Id")}
        DataSet.Relations.Add(New DataRelation("B_A", DataSet.Tables("A").Columns("Id"), DataSet.Tables("B").Columns("A")))
    End Sub
End Class

The only issue here is that the first BindingSource must be set to the parent table. This is fine for a Parent/Child relationship, where you want to choose the Parent in order to show all its children. However, when the parent is a lookup (e.g., Status), you want the driving table (and hence the first BindingSource) to be the child. But, referring to the parent via the relationship with the child, generates an error: DataMember property x cannot be found on the DataSource:

User's image

Boruch Tkatch 141

With help from ChatGPT 3.5, it i added this, to update the field manually:

    Private Sub Form1_Load(Sender As Object, Arguments As EventArgs) Handles MyBase.Load
        Setup_DataSet()

        Grid.DataSource = New BindingSource(DataSet, "B")

        B_A.DataBindings.Add("Text", Grid.DataSource, "A")

        Controls.AddRange({Grid, A_Id, B_A})

        AddHandler Grid.SelectionChanged, Sub()
                                              Dim Parent_Row As DataRow
                                              Dim Current_Row = CType(Grid.DataSource.Current, DataRowView)

                                              If Current_Row IsNot Nothing Then
                                                  Parent_Row = Current_Row.Row.GetParentRow("B_A")
                                                  If Parent_Row IsNot Nothing Then
                                                      A_Id.Text = Parent_Row("Id")
                                                  Else
                                                      A_Id.Text = Nothing
                                                  End If
                                              End If
                                          End Sub
    End Sub
    ```

1 answer

Your answer

Boruch Tkatch 141 Reputation points

2023-11-06T20:55:18.7133333+00:00

Delete - redundant
Boruch Tkatch 141 Reputation points

2023-11-06T22:09:45.2366667+00:00

With help from ChatGPT 3.5, it i added this, to update the field manually:

Private Sub Form1_Load(Sender As Object, Arguments As EventArgs) Handles MyBase.Load Setup_DataSet() Grid.DataSource = New BindingSource(DataSet, "B") B_A.DataBindings.Add("Text", Grid.DataSource, "A") Controls.AddRange({Grid, A_Id, B_A}) AddHandler Grid.SelectionChanged, Sub() Dim Parent_Row As DataRow Dim Current_Row = CType(Grid.DataSource.Current, DataRowView) If Current_Row IsNot Nothing Then Parent_Row = Current_Row.Row.GetParentRow("B_A") If Parent_Row IsNot Nothing Then A_Id.Text = Parent_Row("Id") Else A_Id.Text = Nothing End If End If End Sub End Sub ```

Answer 1

Boruch Tkatch 141

I'll answer my own question. The reason the code does not work is because the DataBinding in the related table needs to be the DataRelation, not the column. Also, the DataRelation works from Parent to Child, not the other way around.

To achieve the goal being aimed for though, see the comments for one way to update it manually.

Share via

How do you databind a textbox to a column in a related table?

1 answer

Your answer