Solved

Create DataRelation between multiple columns

Posted on 2009-05-17
8
2,524 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now