Click here to Skip to main content
11,495,743 members (46,758 online)
Click here to Skip to main content

Databinding with Cross Reference table (many to many)

, 20 Apr 2004 86.5K 33
Rate this:
Please Sign up or sign in to vote.
This article shows how to databind master and details DataGrids (Winforms) when using a cross reference table.

Introduction

I have seen many posts that have gone unanswered about how to databind a master table to a details table when there is a third table that holds the cross references between them.

Here is an example of a database with this structure:

Sample screenshot

Here we go:

First, we need to fill a DataSet.

Public Function FillDataSet() as DataSet
    Dim daNames As OleDbDataAdapter
    Dim daPhoneNumbers As OleDbDataAdapter
    Dim daNamePhone As OleDbDataAdapter
    Dim cmd As OleDbCommand
    Dim reader As OleDbDataReader
    Try
        conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; "_
               & "Data Source=" & "D:\_MySource\FundRaiser\FundRaising.mdb")
        conn.Open()
        ds = New DataSet

        daNames = New OleDbDataAdapter
        Dim s As OleDbCommandBuilder = New OleDbCommandBuilder(daNames)
        ds = GetDataSet(ds, conn, daNames, _
             "SELECT * FROM tbl_Names", "tbl_Names")

        daPhoneNumbers = New OleDbDataAdapter
        ds = GetDataSet(ds, conn, daPhoneNumbers, _
             "SELECT * FROM tbl_PhoneNumbers", "tbl_PhoneNumbers")

        daNamePhone = New OleDbDataAdapter
        ds = GetDataSet(ds, conn, daNamePhone, _
             "SELECT * FROM tbl_Name_Phone", "tbl_Name_Phone")

    Catch

    End Try
    conn.Close()
    Return ds
End Function

Public Function GetDataSet(ByVal ds As DataSet, _
  ByVal conn As OleDbConnection, _
  ByVal Adapter As OleDbDataAdapter, _
  ByVal query As String, ByVal TableName As String) As DataSet
    Adapter.SelectCommand = New OleDbCommand(query, conn)
    Adapter.Fill(ds, TableName)
    Return ds
End Function

Next, we need to create the data relationships between the tables.

Public Sub CreateRelations(byref ds as DataSet)
    Dim parentCol As DataColumn
    Dim childCol As DataColumn
    Dim rel As DataRelation

    parentCol = ds.Tables("tbl_Names").Columns("NameID")
    childCol = ds.Tables("tbl_Name_Phone").Columns("NameID")
    ' Create DataRelation.
    rel = New DataRelation("NamePhone", parentCol, childCol)
    ' Add the relation to the DataSet.
    rel.Nested = True
    ds.Relations.Add(rel)

    parentCol = ds.Tables("tbl_Name_Phone").Columns("PhoneNumberID")
    childCol = ds.Tables("tbl_PhoneNumbers").Columns("PhoneNumberID")
    ' Create DataRelation.
    rel = New DataRelation("NamePhone_PhoneNumbers", parentCol, childCol)
    ' Add the relation to the DataSet.
    rel.Nested = True
    ds.Relations.Add(rel)

End Sub

Now, we need to bind the tables to the DataGrids! This is the not so obvious part!!

Private Sub BindDataSet(ByRef ds As DataSet)
    Call dgNames.SetDataBinding(ds, "tbl_Names")
    Call dgPhoneNumbers.SetDataBinding(ds, _
         "tbl_Names.NamePhone.NamePhone_PhoneNumbers")
End Sub

That's it... The key is to use the relationships when binding the second table!!!

I hope my article was helpful. A complete sample will be available soon.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

ss2art
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
Generalhelp [modified] Pin
niki_nilu25-Feb-08 21:29
memberniki_nilu25-Feb-08 21:29 
QuestionASP.NET webform? Pin
BBWS1O18-May-05 18:22
memberBBWS1O18-May-05 18:22 
GeneralDoesn't work either... Pin
mikasa5-Nov-04 10:42
membermikasa5-Nov-04 10:42 
GeneralRe: Doesn't work either... Pin
mikasa5-Nov-04 11:00
membermikasa5-Nov-04 11:00 
GeneralRe: Doesn't work either... [modified] Pin
Pakico19-Apr-07 2:25
memberPakico19-Apr-07 2:25 
GeneralOnly one record returned Pin
nells113-Sep-04 15:57
membernells113-Sep-04 15:57 
GeneralRe: Only one record returned Pin
mikasa5-Nov-04 12:49
membermikasa5-Nov-04 12:49 
AnswerRe: Only one record returned Pin
Josef Meile21-Sep-05 22:51
memberJosef Meile21-Sep-05 22:51 
Generalvariables not used Pin
karoum23-Aug-04 1:36
memberkaroum23-Aug-04 1:36 
GeneralSyntax Not based on .NET Framework version 1.1 Pin
Jose Xavier21-Apr-04 1:53
memberJose Xavier21-Apr-04 1:53 
GeneralRe: Syntax Not based on .NET Framework version 1.1 Pin
ss2art21-Apr-04 15:24
memberss2art21-Apr-04 15:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 21 Apr 2004
Article Copyright 2004 by ss2art
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid