Solved

Create DataRelation between multiple columns

Posted on 2009-05-17
8
2,710 Views
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...

Tracks
--> 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

Thanks
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(TracksRacesRelation)
                ds.Relations.Add(RacesSessionsRelation)
                ds.Relations.Add(RacesLapsRelation)
                ds.Relations.Add(SessionsLapsRelation)
                ds.Relations.Add(SessionsResultsRelation)
                ds.Relations.Add(SessionsRaceRelation)
                ' ds.Relations.Add(
                dc.Close()

Open in new window

0
Comment
Question by:Sheritlw
[X]
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
8 Comments
 
LVL 13

Expert Comment

by:kaylanreilor
ID: 24409160
You have to have a look to the Relations property of the DataSet class : http://msdn.microsoft.com/en-us/library/system.data.dataset.relations(VS.80).aspx
You can google with that will find a lot of examples.
0
 
LVL 13

Expert Comment

by:kaylanreilor
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

0
 

Author Comment

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

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:Sheritlw
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
0
 
LVL 13

Expert Comment

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

Author Comment

by:Sheritlw
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)
0
 
LVL 13

Expert Comment

by:kaylanreilor
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:
CREATE myView AS
SELECT T1.c1, T2.c2
FROM T1 INNER JOIN
     T2 ON T1.ID = T2.ID INNER JOIN
          T3 ON T2.ID = T3.ID
WHERE T3.c2 IS NOT NULL

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.
0
 

Accepted Solution

by:
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)

0

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

Suggested Solutions

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…

710 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