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
  • 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.
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.


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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity adjust Screen Resolution 6 58
Close form "before" open 3 40
Error handling in site 5 28
Get distinct values from excel or comm seperated file 4 25
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 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