master-detail DataGrid's for many-to-many relationship

[Dennis McCarthy]

My database contains a STUDENTS table, a COURSES table,
and an ENROLLMENTS table. The ENROLLMENTS table
represents the many-to-many relationship between students
and courses.

I would like to have a master-detail pair of DataGrid's.
The master grid contains students, and the detail grid
contains the courses in which the selected student is
enrolled.

Master-detail grids are easy when there is a parent-child
relationship between two tables. However, in my case the
parent child relationship is between the STUDENTS and
ENROLLMENTS tables (and the COURSES and ENROLLMENTS).

How can I get a pair of DataGrid's to act as master-detail
for this many-to-many relationship?

Thanks,
Dennis

 

[Kevin Sun [MS]]

You may be able to create a dataview to collect the records of the
enrollments and course for each student. In the dataview, every student has
many related records, then you can try to use the datagrid based on the
dataview.

The datagrid cannot address a many-to-many relationship.

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| Content-Class: urn:content-classes:message
| From: "Dennis McCarthy" <[email protected]>
| Sender: "Dennis McCarthy" <[email protected]>
| Subject: master-detail DataGrid's for many-to-many relationship
| Date: Wed, 13 Aug 2003 08:06:11 -0700
| Lines: 20
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNhrG6gn1rxyl+PRAqfHXbJuNTyBA==
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:58265
| NNTP-Posting-Host: TK2MSFTNGXA06 10.40.1.53
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| My database contains a STUDENTS table, a COURSES table,
| and an ENROLLMENTS table. The ENROLLMENTS table
| represents the many-to-many relationship between students
| and courses.
|
| I would like to have a master-detail pair of DataGrid's.
| The master grid contains students, and the detail grid
| contains the courses in which the selected student is
| enrolled.
|
| Master-detail grids are easy when there is a parent-child
| relationship between two tables. However, in my case the
| parent child relationship is between the STUDENTS and
| ENROLLMENTS tables (and the COURSES and ENROLLMENTS).
|
| How can I get a pair of DataGrid's to act as master-detail
| for this many-to-many relationship?
|
| Thanks,
| Dennis
|

 

[Dennis McCarthy]

Here's how I got this to work:

1) Read the STUDENTS, COURSES and ENROLLMENTS tables into
a DataSet.

2) Add two DataRelations to the DataSet. One has STUDENTS
as the parent and ENROLLMENTS as the child. The other has
COURSES as the parent and ENROLLMENTS as the child.

3) Add computed DataColumn's to the ENROLLMENTS DataTable
in the DataSet. These columns correspond to columns in
the COURSES table. Their expression is along the lines
of "Parent(COURSE_ENROLLMENTS).COURSE_NAME".

4) Bind the master grid to the STUDENTS DataTable in the
DataSet.

5) Bind the detail grid to the ENROLLMENTS DataTable in
the DataSet. It will appear as if the detail grid is
bound to the COURSES table, since the ENROLLMENTS
DataTable contains the COURSES columns.

The master grid will display all students. The detail
grid will display only the courses in which the student
(row selected in the master grid) is enrolled.

Dennis