• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 688
  • Last Modified:

Create Dataset Relations between Queries

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
Sheritlw
Asked:
Sheritlw
  • 16
  • 12
1 Solution
 
rgn2121Commented:
Dim Relation1 as New DataRelation("NameYourRelation",ds.Tables("Parent").Columns("ColumnName"), ds.Tables("Child").Columns("ColumnName"),ContraintValue)
then...
ds.Relations.Add(Relation1)
0
 
SheritlwAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rgn2121Commented:
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
 
SheritlwAuthor Commented:
I just need to create a link between query 1 and query 2, query 2 and query 3 etc.
Thanks
0
 
rgn2121Commented:

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
 
rgn2121Commented:
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
 
rgn2121Commented:
You create your links between the datatables...
0
 
SheritlwAuthor Commented:
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
 
rgn2121Commented:
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
 
SheritlwAuthor Commented:
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
 
rgn2121Commented:
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
 
rgn2121Commented:
So...once your DataSet is filled, it consists of 4 Tables...Cars, Tracks, Races and Sessions...
0
 
SheritlwAuthor Commented:
Ok, that makes more since.  Why would I get an error on the 3rd creation of the datarelation?

Thanks
0
 
rgn2121Commented:
There is no table named RaceSessions.... You named the table Sessions
0
 
rgn2121Commented:
Dim RacesSessionsRelation As New DataRelation("RacesSessions", ds.Tables("Races").Columns("RaceID"), ds.Tables("RaceSessions").Columns("RaceID"), True)

Shouldn't that just be Sessions?
0
 
SheritlwAuthor Commented:
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
 
SheritlwAuthor Commented:
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
 
rgn2121Commented:
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
 
SheritlwAuthor Commented:
I double-checked that to make sure.
Do you want a screen shot of my queries?
0
 
rgn2121Commented:
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
 
rgn2121Commented:
You double checked the columns or that the RaceID's in Sessions also exists in Races?  Or both?
0
 
SheritlwAuthor Commented:
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
 
rgn2121Commented:
<<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
 
SheritlwAuthor Commented:
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
 
SheritlwAuthor Commented:
Excellent!
0
 
rgn2121Commented:
Good deal... Happy to help!
0
 
SheritlwAuthor Commented:
Are you available to answer another question, but related to an SQL Statement to an MS Access database?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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