Solved

Create Dataset Relations between Queries

Posted on 2009-05-15
28
650 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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