Representing a grand-child Data-table Relationship in two DataGridViews

Question

0.00/5 (No votes)

See more:

Hi

I have several SQL Server (Express Ed) data tables - some of which have parent-child relationships like "Organisations" > "Projects" where the Organisation_ID features as a core data field in the "Projects" data table.

I'm representing these in two DataGridViews (one above the other on the screen) using a relationship like this:

VB

Private Sub GetOrganisationProjectData()
        
        Try
            
            Dim connection As New SqlConnection(connectionString)
            'Connection string is defined elsewhere of course

            ' Create a DataSet. 
            Dim data As New DataSet()
            data.Locale = System.Globalization.CultureInfo.InvariantCulture

            ' Add data from the Organisations table to the DataSet. 

            Dim masterDataAdapter As _
                New SqlDataAdapter("select * from dbo.Organisations", connection)
            masterDataAdapter.Fill(data, "Organisations")

            ' Add data from the Projects table to the DataSet. 

            Dim detailsDataAdapter As _
                New SqlDataAdapter("select * from dbo.Projects", connection)
            detailsDataAdapter.Fill(data, "Projects")

            ' Establish the relationship between the two tables. 

            Dim relation As New DataRelation("OrganisationProjects", _
                data.Tables("Organisations").Columns("Organisation_ID"), _
                data.Tables("Projects").Columns("Organisation_ID"))
            data.Relations.Add(relation)

            ' Bind the master data connector to the Customers table.

            masterBindingSource.DataSource = data
            masterBindingSource.DataMember = "Organisations"

            ' Bind the details data connector to the master data connector, 
            ' using the DataRelation name to filter the information in the  
            ' details table based on the current row in the master table. 

            detailsBindingSource.DataSource = masterBindingSource
            detailsBindingSource.DataMember = "OrganisationProjects"

        Catch ex As SqlException

            MsgBox(ex)

        End Try

        'blah blah blah (screen titles and tidy up stuff in here)

End Sub

This gives me a DataGridView at the top with a list of organisations and when I click on that it shows me all of that organisation's projects.

That's easy enough - but I also have some data tables that have grandchild relationships to the Organisation table - e.g. Organisation to Project, Project to Deliverable. I would like to have the bottom table show me all of the deliverables for the Organisation (sorted by Project ID for example).

How do I set up the relationship and the queries to reflect that? Any suggestions would be appreciated. (Oh - and excuse my code if it's a bit amateurish - I'm an amateur :-) )

Posted 18-Jun-13 14:50pm

Woogie2

Updated 18-Jun-13 16:29pm

_Damian S_

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Damian S_ · Accepted Answer · 2013-06-18T16:34:00

Solution 1

If you are wanting to show all deliverables, regardless of the project chosen, you could, but you'd probably be better to have the deliverables show for the selected project.

To show all deliverables (regardless of project selected), use a stored procedure something like this:

SQL

select FIELDLIST from Organisations
left join Projects on Organisations.Organisation_ID = Projects.Organisation_ID 
left join Deliverables on Projects.Project_ID = Deliverables.Project_ID
where Organisations.Organisation_ID = @P_Organisation_ID

Where you pass the parameter @P_Organisation_ID the value of the selected Organisation.

To show deliverables for a selected project, do the same, but use a different where clause.

SQL

select FIELDLIST from Organisations
left join Projects on Organisations.Organisation_ID = Projects.Organisation_ID 
left join Deliverables on Projects.Project_ID = Deliverables.Project_ID
where Projects.Project_ID = @P_Project_ID

Passing the selected Project as the parameter.

Hope this puts you on the right path.

Posted 18-Jun-13 16:34pm

_Damian S_

Comments

Woogie2 18-Jun-13 23:53pm

Yeah thanks, I do want to show all deliverables for an organisation. I know that sounds odd but later I'll arrange some sorts and filters and stuff (allowing analysis around different deliverable types)

I think that's on the right track but the query I need is for the bottom data table so perhaps it's just joining the projects and deliverables tables by Project_ID. So maybe it's

select FIELDLIST from Projects left join Deliverables on Projects.Project_ID = Deliverables.Project_ID ??

Could be that simple - the "Relationship" (in the VB Code) might then be across the Organisation_ID.

OK - sorry for thinking out loud but if it works (or otherwise) I'll come back - then it will be here for reference at least.

Cheers!

_Damian S_ 18-Jun-13 23:59pm

Yes, that would work. Link via Organisation_ID and Projects.Organisation_ID.

Woogie2 19-Jun-13 1:13am

Brilliant Damian! You're officially a legend :-)

I know this seems simple now but I was stuck on it for weeks (coming back to it once a day and just couldn't think through it).

In the end the code looks like

Try

Dim connection As New SqlConnection(connectionString)

' Create a DataSet.
Dim dsOrganisationDels As New DataSet()
dsOrganisationDels.Locale = System.Globalization.CultureInfo.InvariantCulture

' Add data from the Organisations table to the DataSet.
Dim masterDataAdapter As _
New SqlDataAdapter("select * from dbo.Organisations", connection)
masterDataAdapter.Fill(dsOrganisationDels, "Organisations")

' Add data from the Deliverables table to the DataSet.
Dim detailsDataAdapter As _
New SqlDataAdapter("select * from dbo.Projects left join dbo.Deliverables on dbo.Projects.Project_ID = dbo.Deliverables .Project_ID", connection)
detailsDataAdapter.Fill(dsOrganisationDels, "Deliverables")

' Establish a relationship between the two tables.
Dim relation As New DataRelation("OrganisationDels", _
dsOrganisationDels.Tables("Organisations").Columns("Organisation_ID"), _
dsOrganisationDels.Tables("Deliverables").Columns("Organisation_ID"))
dsOrganisationDels.Relations.Add(relation)

' Bind the master data connector to the Organisations table.
masterBindingSource.DataSource = dsOrganisationDels
masterBindingSource.DataMember = "Organisations"

' Bind the details data connector to the master data connector,
' using the DataRelation name to filter the information in the
' details table based on the current row in the master table.
detailsBindingSource.DataSource = masterBindingSource
detailsBindingSource.DataMember = "OrganisationDels"
Catch ex As SqlException
MsgBox(ex)
End Try

I'll change the field list now to only give me dleiverable related fields (it gave me all of them)

Thanks - your answer helped me think this through properly. Must now fix up that exception catcher :-)

Cheers

_Damian S_ 19-Jun-13 1:17am

No worries... job well done!!