Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3013
  • Last Modified:

Create DataRelation between multiple columns

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
Sheritlw
Asked:
Sheritlw
  • 4
  • 4
1 Solution
 
kaylanreilorCommented:
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
 
kaylanreilorCommented:
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
 
SheritlwAuthor Commented:
I have read about relations, but since I'm new to VB.Net, I am looking for an example.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SheritlwAuthor Commented:
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
 
kaylanreilorCommented:
What do you mean by "I need to pull from the laps table" ?
0
 
SheritlwAuthor Commented:
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
 
kaylanreilorCommented:
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
 
SheritlwAuthor Commented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now