Click here to Skip to main content
Click here to Skip to main content

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.

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] Pinmemberniki_nilu25-Feb-08 20:29 
QuestionASP.NET webform? PinmemberBBWS1O18-May-05 17:22 
GeneralDoesn't work either... Pinmembermikasa5-Nov-04 9:42 
GeneralRe: Doesn't work either... Pinmembermikasa5-Nov-04 10:00 
GeneralRe: Doesn't work either... [modified] PinmemberPakico19-Apr-07 1:25 
GeneralOnly one record returned Pinmembernells113-Sep-04 14:57 
GeneralRe: Only one record returned Pinmembermikasa5-Nov-04 11:49 
AnswerRe: Only one record returned PinmemberJosef Meile21-Sep-05 21:51 
Generalvariables not used Pinmemberkaroum23-Aug-04 0:36 
GeneralSyntax Not based on .NET Framework version 1.1 PinmemberJose Xavier21-Apr-04 0:53 
GeneralRe: Syntax Not based on .NET Framework version 1.1 Pinmemberss2art21-Apr-04 14: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 | Mobile
Web02 | 2.8.141029.1 | Last Updated 21 Apr 2004
Article Copyright 2004 by ss2art
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid