• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

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
0
KentDBerry
Asked:
KentDBerry
1 Solution
 
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
 
KentDBerryAuthor Commented:
Works Great.  Thank you very much!

Kent
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now