Traverse a Many-to-Many relationship

31 Jul 2006 1:26 PM

Michael D. Reed

I have two tables in an Access database with a Many-to-Many relationship,
there is a connection table. The tables are [Product Line], [Factors], and
the connection table is [Product Line performance Factors] this is a standard
Many-to-Many relationship in that the connect table only has two foreign key
columns one for each of the other table primary keys, a very straightforward
setup.

The above is setup in a data set in a Data Set called â€œProductDataSetâ€ in a
C# program. I have a record (row) in the [Product Line]. See code below:
ProductDataSet.ProductLineRow productLineRow;
productLineRow =
(ProductDataSet.ProductLineRow)modelRow.GetParentRow("Product LineModel");
The â€œmodelRowâ€ I from a parent table to the product table that the row was
choose through a control.

The question is how do I get the set of rows form the [Factors] table
knowing the row for the [Product Lint] table; how do I traverse the
relationship? There is a method attached to the [Product Line] table row
called:
productLineRow.GetProduct_Line_performance_FactorsRows()
I tried to use it as such:
ProductDataSet.FactorsDataTable [] factorsTable;
factorsTable = (ProductDataSet.FactorsDataTable)productLineRow.GetProduct_Line_performance_FactorsRows();
However, this is not working it seams to want to return an array. An array
of what?

This is should be a straightforward operation that is easily accomplished in
SQL.
PARAMETERS ProductLineID Long;
SELECT [Product Line].[Product Line ID], Factors.[Factors Name],
Factors.[Factor Coefficent A0], Factors.[Factor Coefficent A1],
Factors.[Factor Coefficent A2]
FROM [Product Line] INNER JOIN (Factors INNER JOIN [Product Line performance
Factors] ON Factors.[Factors ID] = [Product Line performance
Factors].[Factors ID]) ON [Product Line].[Product Line ID] = [Product Line
performance Factors].[Product Line ID]
WHERE ((([Product Line].[Product Line ID])=[ProductLineID]));

I have on row in on table I should be able to easily get a table back of the
rows that it refers to. Do I have to revert to a separate quire to perform
this operation?
Thank you,

--
Mike Reed

1 Aug 2006 5:30 AM

Kevin Yu [MSFT]

Hi Mike,

I posted a reply in microsoft.public.vb.database newsgroup to this issue.
Please pickup the answer there. Thank you!

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)