Create DataRelation between multiple columns

Posted on 2009-05-17
Last Modified: 2012-05-07
I have a fpSpread/Grid with 5 level  hiearchy view.
One of the levels needs to have a relationship between two of the levels.
The hiearchy is...

--> Races
-----> Sessions
---------> Lap Times

The parent tables Races and Sessions, need to be related to the child laps table according to the raceid and sessionid of the Laps table.

How can a create a relationship where the table Sessions.SessionID  is relate to Laps.Sessionid and Races.RaceID is related to Sessions.RaceID

dTr.Fill(ds, "Tracks") 'child
                dRa.Fill(ds, "Races") 'child
                dSe.Fill(ds, "Sessions") 'child
                dLa.Fill(ds, "Laps") 'child
                dRe.Fill(ds, "Results") 'child
                Dim TracksRacesRelation As New DataRelation("TracksRaces", ds.Tables("Tracks").Columns("TrackID"), ds.Tables("Races").Columns("TrackID"), True)
                Dim RacesSessionsRelation As New DataRelation("RacesSessions", ds.Tables("Races").Columns("RaceID"), ds.Tables("Sessions").Columns("RaceID"), True)
                Dim SessionsLapsRelation As New DataRelation("SessionsLaps", ds.Tables("Sessions").Columns("SessionTypeID"), ds.Tables("Laps").Columns("SessionTypeID"), True)
                Dim RacesLapsRelation As New DataRelation("RacesLaps", ds.Tables("Races").Columns("RaceID"), ds.Tables("Laps").Columns("RaceID"), True)
                Dim SessionsResultsRelation As New DataRelation("SessionsResults", ds.Tables("Sessions").Columns("SessionTypeID"), ds.Tables("Results").Columns("SessionTypeID"), True)
                Dim SessionsRaceRelation As New DataRelation("SessionsRace", ds.Tables("Sessions").Columns("RaceID"), ds.Tables("Results").Columns("RaceID"), True)
                ' ds.Relations.Add(

Open in new window

Question by:Sheritlw
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 13

Expert Comment

ID: 24409160
You have to have a look to the Relations property of the DataSet class :
You can google with that will find a lot of examples.
LVL 13

Expert Comment

ID: 24409207
Oups sorry, I havn't finished...
So I said : then you'll jump to the DataRelationCollection doc and see that you cannot have 1 relationship for what you wanna do !
But what is wrong with "RacesSessions" and the following ? Or I miss the question...
Dim R1 As New DataRelation("R1", ds.Tables("Sessions").Columns("Sessionid"), ds.Tables("Laps").Columns("Sessionid"), True)

Open in new window


Author Comment

ID: 24409215
I have read about relations, but since I'm new to VB.Net, I am looking for an example.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


Author Comment

ID: 24409223
The problem is the both the Races and Sessions tables are connected to the laps table.
So I need to pull from the laps table where Races.raceid = laps.raceid and sessions.sessionid = Laps.Sessionid
LVL 13

Expert Comment

ID: 24409230
What do you mean by "I need to pull from the laps table" ?

Author Comment

ID: 24409305
If I were writing an sql statement for the laps level, I would write it like
SELECT LapID, RaceID, SessionID, LapTime from Laps where LapTimes.RaceID = Races.RaceID and LapTimes.SessionID = Sessions.SessionID

I need to pull only those records that match the RaceID from the Races table and SessionID from the Sessions table.

Any example showing me how to create a relationship with a total of 4 columns, 2 from 1 table and 2 from another would be great.  I can figure it out from there.

Dim SessionsRaceRelation As New DataRelation("SessionsRace", ds.Tables("Laps").Columns("RaceID"), ds.Tables("Races").Columns("RaceID") ****** AND ***** ds.Tables("Laps").Columns("SessionID"), ds.Tables("Sessions").Columns("SessionID") , True)
LVL 13

Expert Comment

ID: 24409459
I said it earlier : a relation CANNOT concern MORE THAN 2 columns.
Also, probably you miss something about relationships : it is not going to update or select the columns for you. That's why I sent you the links to the doc.
A relation between a colmn A and a column B will make column B to be updated when updating column A. But it is not going to change anything else in the rows. And, a relation will prevent unwilling deletion if well used.
Actually, I did say, I have a problem with the meaning of "pull" !!! I don't undestand what it mean at all.
Nevertheless, I can try to guess.
Probably you can make the deal with a stored procedure or a view on the DB level : imagine you create a view this way:
In the DB there are 3 tables like this:
T1 with following columns : ID, c1, c2
T2 with following columns : ID, c1, c2
T3 with following columns : ID, c1, c2
And then I create the view:
SELECT T1.c1, T2.c2
          T3 ON T2.ID = T3.ID

Now I can fill up a table of a dataset with this view.
If you give more details about your tables and explain further what you want to do we can write something which matches your DB.
So if you want to update rows where you have condition like T1.c1 = T2.c1 AND T3.c1 = T4.c1I don't thing that the relations between table will help.

Accepted Solution

Sheritlw earned 0 total points
ID: 24409507
I found the solution to having multiple column Relationships and it works like a champ.

                Dim parentColumns As DataColumn() = New DataColumn() {ds.Tables(2).Columns("RaceID"), ds.Tables(2).Columns("SessionTypeID")}
                Dim childColumns As DataColumn() = New DataColumn() {ds.Tables(3).Columns("RaceID"), ds.Tables(3).Columns("SessionTypeID")}
                Dim RacesLapsRelation As DataRelation = New DataRelation("relationName", parentColumns, childColumns)


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question