Solved

Create Dataset Relations between Queries

Posted on 2009-05-15
28
661 Views
Last Modified: 2012-06-21
I have a  4 level hierarchy grid where I fill 4 different dataadapters with queries from a MS Access Database.  I then create my dataset using these adapters.

I now need to know what the syntax is to Add a relationship between these queries.
Each query consists of a few different tables.

Thanks
Function FillHiearchy() As DataTable
        Dim ds As New DataSet
 
        If DatabaseToUse() = BizObjs.DBType.Access Then
 
            Dim sTracks As String = String.Empty
            Dim sCars As String = String.Empty
            Dim sSessions As String = String.Empty
            Dim sRaces As String = String.Empty
 
            sTracks = "Select * from qTracks"
            sCars = "Select * from qCars"
            sSessions = "Select * from qSessionsLaps"
            sRaces = "Select * from qRaces"
 
            Dim dc As New OleDb.OleDbConnection(ConnectionString)
            'Dim cmd As New OleDb.OleDbCommand(str, dc)
            Dim dTr As New OleDb.OleDbDataAdapter(sTracks, dc)
            Dim dCa As New OleDb.OleDbDataAdapter(sCars, dc)
            Dim dSe As New OleDb.OleDbDataAdapter(sSessions, dc)
            Dim dRa As New OleDb.OleDbDataAdapter(sRaces, dc)
 
 
            Try
 
                dCa.Fill(ds, "Cars") 'parent
                dTr.Fill(ds, "Tracks") 'child
                dRa.Fill(ds, "Races") 'child
                dSe.Fill(ds, "Sessions") 'child
 
                ds.Relations.Add(??????????
                dc.Close()
 
 
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                dc.Close()
 
            End Try
        End If
 
        Return GenerateDataTable(ds)
 
    End Function

Open in new window

0
Comment
Question by:Sheritlw
  • 16
  • 12
28 Comments
 
LVL 12

Expert Comment

by:rgn2121
ID: 24393229
Dim Relation1 as New DataRelation("NameYourRelation",ds.Tables("Parent").Columns("ColumnName"), ds.Tables("Child").Columns("ColumnName"),ContraintValue)
then...
ds.Relations.Add(Relation1)
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24393235
0
 

Author Comment

by:Sheritlw
ID: 24393264
That is clear for relating two queries, but I need to relate 4
Like Query 1 column with query 2 column, query 2 column with query 3 column, query 3 column with query 4 column.
Thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 12

Expert Comment

by:rgn2121
ID: 24393286
Are you saying DataTable1.Columns(1) = DataTable2.Columns(2)
and DataTable(2).Columns(2)=DataTable3.Columns(3)???
Or you just need to create a link between table1 and table 2, table 2 and table 3, table 3 and table 4, etc..
 
 
0
 

Author Comment

by:Sheritlw
ID: 24393301
I just need to create a link between query 1 and query 2, query 2 and query 3 etc.
Thanks
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24393521

Dim carsTracksRelation as New DataRelation("CarsToTracks",ds.Tables("Cars").Columns("MyCar"), ds.Tables("Tracks").Columns("MyCar"),True)
 
Dim carsTracksRelation as New DataRelation("CarsToTracks",ds.Tables("Cars").Columns("MyCar"), ds.Tables("Races").Columns("MyCar"),True)
 
Dim carsTracksRelation as New DataRelation("CarsToTracks",ds.Tables("Cars").Columns("MyCar"), ds.Tables("Sessions").Columns("MyCar"),True)

Open in new window

0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24393529
Sorry...you would want different names for your relations... then youjust add them to the dataset...
ds.Relations.Add(carsTracksRelation)
ds.Relations.Add(carsTracksRelation2)
ds.Relations.Add(carsTracksRelation3)
 
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24393710
You create your links between the datatables...
0
 

Author Comment

by:Sheritlw
ID: 24400870
That looks good, but will there be a conflict since the queries are named something else and not the name of the table?
Thanks
0
 
LVL 12

Accepted Solution

by:
rgn2121 earned 500 total points
ID: 24401107
Looks to me like you have 4 DataTables in your DataSet... Cars, Tracks, Races and Sessions.  That is the only thing that is named inside the DataSet other than the names of the Columns.  Once you create the relations, then they have names.  I am not sure what you mean by the queries being named...
Are you talking about your queries as in sCars....?
I have posted a sample of what our code should look like below...

Function FillHiearchy() As DataTable
        Dim ds As New DataSet
 
        If DatabaseToUse() = BizObjs.DBType.Access Then
 
            Dim sTracks As String = String.Empty
            Dim sCars As String = String.Empty
            Dim sSessions As String = String.Empty
            Dim sRaces As String = String.Empty
 
            sTracks = "Select * from qTracks"
            sCars = "Select * from qCars"
            sSessions = "Select * from qSessionsLaps"
            sRaces = "Select * from qRaces"
 
            Dim dc As New OleDb.OleDbConnection(ConnectionString)
            'Dim cmd As New OleDb.OleDbCommand(str, dc)
            Dim dTr As New OleDb.OleDbDataAdapter(sTracks, dc)
            Dim dCa As New OleDb.OleDbDataAdapter(sCars, dc)
            Dim dSe As New OleDb.OleDbDataAdapter(sSessions, dc)
            Dim dRa As New OleDb.OleDbDataAdapter(sRaces, dc)
 
 
            Try
 
                dCa.Fill(ds, "Cars") 'parent
                dTr.Fill(ds, "Tracks") 'child
                dRa.Fill(ds, "Races") 'child
                dSe.Fill(ds, "Sessions") 'child
 
                'Create Relationships between tables
                Dim carsTracksRelation as New DataRelation("CarsToTracks",ds.Tables("Cars").Columns("CarID"), ds.Tables("Tracks").Columns("CarID"),True)
 
                Dim tracksRacesRelation as New DataRelation("TracksToRaces",ds.Tables("Tracks").Columns("TrackID"), ds.Tables("Races").Columns("TrackID"),True)
 
                Dim racesSessionsRelation as New DataRelation("RacesToSessions",ds.Tables("Races").Columns("RaceID"), ds.Tables("Sessions").Columns("RaceID"),True)
                'Add the Relationships to the DataSet
                ds.Relations.Add(carsTracksRelation)
                ds.Relations.Add(tracksRacesRelation)
                ds.Relations.Add(racesSessionsRelation)
                dc.Close()
 
 
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                dc.Close()
 
            End Try
        End If
 
        Return GenerateDataTable(ds)
 
    End Function

Open in new window

0
 

Author Comment

by:Sheritlw
ID: 24401131
There are actually multiple tables in each of the queries.
Below is what I have so far...
I get an error on the last creation of the relationships...  
This line                 Dim RacesSessionsRelation As New DataRelation("RacesSessions", ds.Tables("Races").Columns("RaceID"), ds.Tables("RaceSessions").Columns("RaceID"), True)

The query that holds the RaceSessions.RaceID has 4 other tables in it.
   

Public Function FillHiearchy() As DataTable
        Dim ds As New DataSet

        If DatabaseToUse() = BizObjs.DBType.Access Then

            Dim sTracks As String = String.Empty
            Dim sCars As String = String.Empty
            Dim sSessions As String = String.Empty
            Dim sRaces As String = String.Empty

            sTracks = "Select * from qTracks"
            sCars = "Select * from qCars"
            sSessions = "Select * from qSessionsLaps"
            sRaces = "Select * from qRaces"

            Dim dc As New OleDb.OleDbConnection(ConnectionString)
            Dim dCa As New OleDb.OleDbDataAdapter(sCars, dc)
            'Dim cmd As New OleDb.OleDbCommand(str, dc)
            Dim dTr As New OleDb.OleDbDataAdapter(sTracks, dc)
            Dim dSe As New OleDb.OleDbDataAdapter(sSessions, dc)
            Dim dRa As New OleDb.OleDbDataAdapter(sRaces, dc)

            Try

                dCa.Fill(ds, "Cars") 'parent
                dTr.Fill(ds, "Tracks") 'child
                dRa.Fill(ds, "Races") 'child
                dSe.Fill(ds, "Sessions") 'child

                Dim CarsRacesRelation As New DataRelation("CarsRaces", ds.Tables("Cars").Columns("CarID"), ds.Tables("Races").Columns("CarID"), True)
                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("RaceSessions").Columns("RaceID"), True)


                ds.Relations.Add(CarsRacesRelation)
                ds.Relations.Add(TracksRacesRelation)
                ds.Relations.Add(RacesSessionsRelation)


                ' ds.Relations.Add(
                dc.Close()


            Catch ex As Exception
                MessageBox.Show(ex.Message)
                dc.Close()

            End Try
        End If

        Return GenerateDataTable(ds)

    End Function
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24401144
Okay...I think I see what you are saying.  Your query is accessing 4 tables to get the data correct?  So the SELECT * FROM qRaces, is pulling all the records from a query that queries 4 tables for it's data??
Let me say this, I have numerous queries in an app that the query itself is pulling from 4-6 Oracle tables.  When that data comes back to me, it is stored in ONE table.  That DataTable is what I setup my relationships on.
Does that help...?  I am a little confused...Once the data is pulled in it is storeed in the table that you setup in the code section below....
 

                dCa.Fill(ds, "Cars") 'parent
                dTr.Fill(ds, "Tracks") 'child
                dRa.Fill(ds, "Races") 'child
                dSe.Fill(ds, "Sessions") 'child

Open in new window

0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24401145
So...once your DataSet is filled, it consists of 4 Tables...Cars, Tracks, Races and Sessions...
0
 

Author Comment

by:Sheritlw
ID: 24401149
Ok, that makes more since.  Why would I get an error on the 3rd creation of the datarelation?

Thanks
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24401156
There is no table named RaceSessions.... You named the table Sessions
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24401158
Dim RacesSessionsRelation As New DataRelation("RacesSessions", ds.Tables("Races").Columns("RaceID"), ds.Tables("RaceSessions").Columns("RaceID"), True)

Shouldn't that just be Sessions?
0
 

Author Comment

by:Sheritlw
ID: 24401159
I see the last error :-( embarrassing... had it named RaceSessions instead of Sessions.  I am getting another error, will check it and get back to you in a couple of minutes.
Thanks
0
 

Author Comment

by:Sheritlw
ID: 24401180
I now get the following error...
This constraint cannot be enabled as not all values have corresponding parent values
on this line  ds.Relations.Add(RacesSessionsRelation)
Any ideas?
Thanks
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24401190
I would make sure that the columns you specified in each table are accurate.  Could it be possible that there is a RaceID in the Sessions table that does not exist in the Races table?
0
 

Author Comment

by:Sheritlw
ID: 24401208
I double-checked that to make sure.
Do you want a screen shot of my queries?
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24401221
I am not sure if that would help or not...How many records are we talking about in each table (Races and Session).  Seems that the error points to the fact that there is an ID, in this case RaceID that exists in your sessions table, but not in your Races table.
If it is a lot of records then run an unmatched query on  the results from those 2 queries.
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24401222
You double checked the columns or that the RaceID's in Sessions also exists in Races?  Or both?
0
 

Author Comment

by:Sheritlw
ID: 24401225
Yes, the sessions and races both have the race id.
The only tables/queries that do not have a relationship between them are the Cars and Tracks queries.
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24401233
<<Yes, the sessions and races both have the race id. >>
Okay, but for every RaceID in the Sessions table is there a match in the Race table?
0
 

Author Comment

by:Sheritlw
ID: 24401268
I checked the query and there wasn't a raceid for each record in the sessions query.
So I changed it and it worked!  You are great!
Thank you, Thank you
0
 

Author Closing Comment

by:Sheritlw
ID: 31581829
Excellent!
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 24401303
Good deal... Happy to help!
0
 

Author Comment

by:Sheritlw
ID: 24401339
Are you available to answer another question, but related to an SQL Statement to an MS Access database?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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