How to bind Child Records to Parent Records

The Attached Screen Shot depicts a maintenance form for two tables having a one to many relationship.  The grid in upper right corner is bound to the same Parent table that the fields in the main form are bound to.  

My problem is that I can not get the Child records to link up with the Parent recs on the main form.  The navigation buttons on bottom are linked to the Parent table.

However, If I click on different Parent records in the grid in upper right corner, the Child records link up as expected.  I want the navigation buttons to behave in this same manner.  What do I have to do to make this happen?  Are there other suggestions to improve this form as I will be making several forms similar to it.   I have embedded some code for your reference. I suspect the issue may be in SetdataRel()

I use Visual Studio 2005
Public Class frmExcelSheetMaintenance
    Dim con As New SqlConnection(My.Settings.BRDWorkslatesConnectionString)
    Dim ds As New DataSet
    Dim daParent As SqlClient.SqlDataAdapter
    Dim daChild As SqlClient.SqlDataAdapter
    Dim sql As String
    Dim myTable As DataTable
    Private myCurrencyManager As CurrencyManager
.
.
.
Private Sub frmExcelSheetMaintenance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        con.Open()
 
        sql = "SELECT * FROM tblExcelSheets"
        daParent = New SqlClient.SqlDataAdapter(sql, con)
        daParent.FillSchema(ds, SchemaType.Mapped, "Parent")
        daParent.Fill(ds, "Parent")
 
        sql = "SELECT * FROM tblExcelSheetFields"
        daChild = New SqlClient.SqlDataAdapter(sql, con)
        daChild.FillSchema(ds, SchemaType.Mapped, "Child")
        daChild.Fill(ds, "Child")
 
        con.Close()
 
        BindControl(ds.Tables("Parent"))
 
        dgvChild.DataSource = ds.Tables("Child")
 
        ds.Tables("Parent").Columns("SheetID").AutoIncrement = True
        ds.Tables("Parent").Columns("SheetID").AutoIncrementSeed = -1
 
        ds.Tables("Child").Columns("FieldID").AutoIncrement = True
 
        SetdataRel()
 
        FormatChildGridView()
 
        MoveFirst(myCurrencyManager) 'Initializes txtRecordPosition
    End Sub
    Private Sub BindControl(ByVal myTable As DataTable)
        Select Case myTable.ToString
 
            Case ds.Tables("Parent").ToString
 
                Me.SheetIDTextBox.DataBindings.Add("text", myTable, "SheetID")
 
                Me.FindComboBox1.DataSource = ds.Tables("Parent")
                Me.FindComboBox1.DisplayMember = ds.Tables("Parent").Columns("SheetName").ToString
                Me.FindComboBox1.ValueMember = ds.Tables("Parent").Columns("SheetID").ToString
 
                Me.WorkBookTextBox.DataBindings.Add("text", myTable, "WorkBook")
                Me.SheetNameTextBox.DataBindings.Add("text", myTable, "SheetName")
                Me.StoredProcedureTextBox.DataBindings.Add("text", myTable, "StoredProcedure")
                Me.FunctionTextBox.DataBindings.Add("text", myTable, "Function")
                Me.SheetOrderTextBox.DataBindings.Add("text", myTable, "SheetOrder")
 
                Me.SheetActiveCheckBox.DataBindings.Add("Checked", myTable, "SheetActive")
                Me.SheetVisibleCheckBox.DataBindings.Add("Checked", myTable, "SheetVisible")
 
                ' Specify the CurrencyManager for the DataTable.
                myCurrencyManager = CType(Me.BindingContext(myTable), CurrencyManager)
 
                ' Set the initial Position of the control.
                myCurrencyManager.Position = 0
 
            Case ds.Tables("Child").ToString
 
        End Select
    End Sub
 
    Public Sub SetdataRel()
 
        ds.Relations.Add(New DataRelation(relationName:="relForeignKeyID", _
                                parentColumn:=ds.Tables("Parent").Columns("SheetID"), _
                                childColumn:=ds.Tables("Child").Columns("SheetID"), _
                                createConstraints:=False))
 
        ' Set the Suppliers grid
        dgvParent.DataSource = ds
        dgvParent.DataMember = "Parent"
 
        ' Set the Products grid
        dgvChild.DataSource = ds
        dgvChild.DataMember = "Parent.relForeignKeyID"
 
    End Sub

Open in new window

frmExcelSheetMaintenance.bmp
KentDBerryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vb_jonasCommented:
Hi! You are having 2 bindingcontexts in your form, thats why the children grid dont sync with the form. Change to this:
    Public Sub SetdataRel()
 
        ds.Relations.Add(New DataRelation(relationName:="relForeignKeyID", _
                                parentColumn:=ds.Tables("Parent").Columns("SheetID"), _
                                childColumn:=ds.Tables("Child").Columns("SheetID"), _
                                createConstraints:=False))
 
        ' Set the Suppliers grid
        dgvParent.DataSource = ds.tables("parent")
        dgvParent.DataMember = ""
 
        ' Set the Products grid
        dgvChild.DataSource = ds.tables("parent")
        dgvChild.DataMember = "relForeignKeyID"
 
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KentDBerryAuthor Commented:
Works Great.  Thank you very much!

Kent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.