Click here to Skip to main content
12,820,914 members (32,348 online)
Click here to Skip to main content
Add your own
alternative version


33 bookmarked
Posted 15 Apr 2004

Databinding with Cross Reference table (many to many)

, 20 Apr 2004
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.


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
        conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; "_
               & "Data Source=" & "D:\_MySource\FundRaiser\FundRaising.mdb")
        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")


    End Try
    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

    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

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, _
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.


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


About the Author

Web Developer
United States United States
No Biography provided

You may also be interested in...

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    Praise Praise    Rant Rant    Admin Admin   

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170308.1 | Last Updated 21 Apr 2004
Article Copyright 2004 by ss2art
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid