?
Solved

Create Dataset Relations between Queries

Posted on 2009-05-15
28
Medium Priority
?
674 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
[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
  • 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
 

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

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

Independent Software Vendors: 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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 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