Binding data from Multiple tables on singular from

user_18676724 0
Viewed 122

I have an food prep form(Kai). Within it, I have bound data from the Kai table using a listbox. When I press the up and down buttons, the text fields displays the row data from the the KAI table on the form. I want to display another EventName field from the EVENT table. Basically to show the associated event with the particular Meal. My challenge is that I need it lookup the meal via the EventID and display the corresponding EventName form the event table. At the same time, this needs to function as I click on different meals on the form listbox. Screenshots and code below. I hope this makes sense..

Kai Form enter image description here

public void BindControls() //Assign and bind form fields with data source
        { 
            txtKaiID.DataBindings.Add("Text", DM.dsKaioordinate1, "KAI.KaiID");
            txtEvent.DataBindings.Add("Text", DM.dsKaioordinate1, "EVENT.EventID"); //This is where the issue is
            txtKaiName.DataBindings.Add("Text", DM.dsKaioordinate1, "KAI.KaiName");
            txtPreparation.DataBindings.Add("Text", DM.dsKaioordinate1, ("KAI.PreparationRequired"));
            txtPreparationTime.DataBindings.Add("Text", DM.dsKaioordinate1, "KAI.PreparationMinutes");
            txtServingQuantity.DataBindings.Add("Text", DM.dsKaioordinate1, "KAI.ServeQuantity");
            lstKaiMaintenance.DataSource = DM.dsKaioordinate1;
            lstKaiMaintenance.DisplayMember = "KAI.KaiName";
            lstKaiMaintenance.ValueMember = "KAI.KaiName";
            currencyManager = (CurrencyManager)this.BindingContext[DM.dsKaioordinate1, "KAI"]; //// Specify the CurrencyManager for the DataTable.                          
}

Relationships enter image description here

Kai Table
enter image description here

Event Table
enter image description here

forms binding vb.net .net c#
1 Answers

You can use data-binding to select a child record automatically when a parent record is selected but I'm not aware of any way to select a parent record automatically when a child record is selected, which is what you want to do. That means that you will have to select the parent record manually. That's no big deal though. It just means handling an event, getting an ID and selecting the corresponding parent record. It's a few lines of code. All the data will still be bound.

Firstly, don't be using BindingContext or CurrencyManager directly. There's been no need since .NET 2.0 and the introduction of the BindingSource. You should add two BindingSources to your form in the designer, bind the two DataTables to them and then bind the BindingSources to the controls. You can then handle the CurrentChanged event of the child BindingSource, get the foreign key value (EventID) from the child record and have the parent BindingSource select the parent record with that primary key.

Code example:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim data = GetData()

    parentBindingSource.DataSource = data.Tables("Parent")
    childBindingSource.DataSource = data.Tables("Child")

    With childNameListBox
        .DisplayMember = "Name"
        .ValueMember = "ChildId"
        .DataSource = childBindingSource
    End With

    childDescriptionTextBox.DataBindings.Add("Text", childBindingSource, "Description")
    parentNameTextBox.DataBindings.Add("Text", parentBindingSource, "Name")
End Sub

Private Function GetData() As DataSet
    Dim data As New DataSet
    Dim parentTable = data.Tables.Add("Parent")
    Dim childTable = data.Tables.Add("Child")

    With parentTable.Columns
        parentTable.PrimaryKey = { .Add("ParentId", GetType(Integer))}
        .Add("Name", GetType(String))
    End With

    With childTable.Columns
        childTable.PrimaryKey = { .Add("ChildId", GetType(Integer))}
        .Add("ParentId", GetType(Integer))
        .Add("Name", GetType(String))
        .Add("Description", GetType(String))
    End With

    data.Relations.Add("ParentChild", parentTable.Columns("ParentId"), childTable.Columns("ParentId"))

    With parentTable.Rows
        .Add(1, "Parent1")
        .Add(2, "Parent2")
        .Add(3, "Parent3")
    End With

    With childTable.Rows
        .Add(1, 1, "Child1A", "First Child of Parent1")
        .Add(2, 1, "Child1B", "Second Child of Parent1")
        .Add(3, 1, "Child1C", "Third Child of Parent1")
        .Add(4, 2, "Child2A", "First Child of Parent2")
        .Add(5, 2, "Child2B", "Second Child of Parent2")
        .Add(6, 2, "Child2C", "Third Child of Parent2")
        .Add(7, 3, "Child3A", "First Child of Parent3")
        .Add(8, 3, "Child3B", "Second Child of Parent3")
        .Add(9, 3, "Child3C", "Third Child of Parent3")
    End With

    Return data
End Function

Private Sub childBindingSource_CurrentChanged(sender As Object, e As EventArgs) Handles childBindingSource.CurrentChanged
    Dim childRow = DirectCast(childBindingSource.Current, DataRowView)
    Dim parentId = CInt(childRow("ParentId"))

    parentBindingSource.Position = parentBindingSource.Find("ParentId", parentId)
End Sub
user_183874010
Related