Question Parent/Child table's in SQL SERVER?

cwnonthehill

Member
Joined
Nov 5, 2012
Messages
20
Programming Experience
1-3
Hey guys, I'm new to the forum, so thanks in advance for your time and help!!

I will do my best to explain my situation...

I have two SQL Tables and am using VB to represent a user interface for adding/editing this data... Below are screenshots of my two tables

tblCollection
===========
ss2.jpg

and tblCollectionDetails
===================
ss3.jpg

Note - tblCollectionDetailshas an FK relation to tblCollection by RecordID

This is my Only VB Form
===================
ss1.jpg

As you can see, i have a toolbar to cycle through the records from tblCollection by RecordID. Each RecordID has details that correspond from tblCollectionDetails, those details will be displayed in the DataGridView based on RecordID. I have sucessfully been able to get/populate top half of the VB form with data from tblCollection. the problem I am having is how to get/populate the DataGridView with ONLY corresponding details in tblCollectionDetails based on the RecordID from tblCollection.

Note - Kind of what this reminds me of is a Dependant Table in MS Access, where each row in a table has the + sign, and when the + sign is clicked, it displayed the corresponding data from another table. What is this called in SQL?

My question is, what is the best way to go about this? Do I somehow create a parent/child relationship in SQL? Or will I do this with VB? How?

THANKS! i know its confusing and i will be happy to explain anything!
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,295
Location
Sydney, Australia
Programming Experience
10+
Create a DataSet and populate two DataTables in it with the data from thw two database tables. Create a DataRelation in the DataSet corresponding to your foreign key. If you use the Data Source wizard to create a typed DataSet then that will all be done for you.

Add two BindingSources. For the first, the DataSource will be the DataSet and the DataMember will be the name of the parent table. For the second, the DataSource will be the first BindingSource and the DataMember will be the name of the DataRelation. Bind the first to your TextBoxes, etc, and bind the second to your grid. The rest will occur automatically.
 

cwnonthehill

Member
Joined
Nov 5, 2012
Messages
20
Programming Experience
1-3
thanks jmcilhinney, that put a lot of things i had already learned into a sort of organized set of operations i needed to see, but the main thing i was missing was GetChildRows from the DataRelation. once i figured that out it all fell into place! thanks again, problem solved!
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,295
Location
Sydney, Australia
Programming Experience
10+
If you bind the BindingSources correctly then you don't need to call GetChildRows yourself. I wasn't in a position to provide this link before because I was posting on a phone but here's one I prepared earlier:

Master/Detail (Parent/Child) Data-binding
 

cwnonthehill

Member
Joined
Nov 5, 2012
Messages
20
Programming Experience
1-3
that is a great thread! i'll play with your example some on my next project. thanks for the help!
 
Top Bottom