Question Relationship between datatables

rctaubert

Member
Joined
Aug 22, 2004
Messages
24
Programming Experience
10+
I really need some help with this one. I am using VS2008 Standard, Access 2007 and VB.net.

I have an Access db with a Membership table, a DuesPayment table and a table

linking the two together.

tblMembers lnktblMembersToPayment tblDues
________________________________________________________________
MemberID* MemberID* DuesPaymentID
Name DuesPaymentID* YearDuesPaid
Address Amount
etc. etc.

1) In VS I have a single form (frmMembership). I used the Data Sources window to create a single datasource containing the tables tblMembers, tblDuesPayment

and lktblMemToPay. from the 'Data Sources' window I drug tblMembers, as text boxes, to the form. I drug tblDuesPayment to the form as a DataGridView.

I right-clicked 'dsMembership...Edit in Dataset Designer' and checked that the relations between the three tables was as shown above.

I left all other settings as they were created by VS.

When I execute the app I get the member information as expected and can use the TblMembersBindingNavigator to step through the members.

The DataGridView, instead of showing dues payment information for the member showing on the form, shows ALL of the dues information for ALL members does not exhibit any relationship between tblMembers and tblDuesPayment.

2) I also tried creating a dataset for tblMembers only. Then I used 'dsMembership...Edit in DatasetDesigner' window to create a TableAdapter for tblDuesPayment and lktblMemToPay. I got the same results.

3) I tried various other combinations, like creating TableAdaptors for tblMembers and a TableAdaptor for a combination of lktblMemToPay & tblDuesPayment.

I have very little hair on the top of my head from trying to pull it out over this problem.

Any help or suggestions would be greatly appreciated.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,548
Location
Sydney, Australia
Programming Experience
10+
First up, please post your questions in the forum that most closely relates to the topic, not just the first one you come to. This thread has been moved from VS.NET General, which is for general question about the VS IDE.

As for the question, the problem is that you have simply bound your grid to a DataTable and that's it. The DataTable contains all the records so you see all the records.

When you have a 1:many relationship between two tables it is possible to bind your data such that the child data is filtered automatically. In that case you bind a parent BindingSource to the DataSet and specify the parent DataTable as the DataMember, then you bind a child BindingSource to the parent BindingSource and specify the DataRelation as the DataMember. Now, when you select a parent record, the relation only exposes the child records related to that so that's all you see in the child grid. You don't have a 1:many relationship though, so you can NOT do that.

You're going to have to do the filtering manually. The good thing is that you basically already have everything setup ready to go. All you need is one event handler. You need to handle the CurrentChanged event of the parent BindingSource. In that event handler you get the ID from the current parent record and use that to filter the child BindingSource:
VB.NET:
Private Sub parentBindingSource_CurrentChanged(ByVal sender As Object, _
                                               ByVal e As EventArgs) Handles parentBindingSource.CurrentChanged
    Dim parentRecord As DataRowView = TryCast(Me.parentBindingSource.Current, DataRowView)

    If parentRecord IsNot Nothing Then
        Me.childBindingSource.Filter = "ParentID = " & CInt(parentRecord("ID"))
    End If
End Sub
 

rctaubert

Member
Joined
Aug 22, 2004
Messages
24
Programming Experience
10+
Thank you for your response. I will try this out in the morning.

I really didn't see this as an IDE problem and none of the other categories seem appropriate either. Sorry.

Trying to find the right category for some of these forums is like trying to find the right response to the telephone answering machines. None of the buttons they want you to push sound like what you want. The only one I truely understand is 'Press 1 for English'.

Thank you again.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,548
Location
Sydney, Australia
Programming Experience
10+
I really didn't see this as an IDE problem
Exactly, so it should be anywhere in the Visual Studio .NET section.
none of the other categories seem appropriate either.
If it's regarding the VB.NET language and it doesn't belong anywhere more specific, e.g. ASP.NET or Mobile, then it belongs in the VB.NET section. It's obviously a WinForms app so Windows Forms is relavent. It also has to do with data, although it's not the database access part specifically, so Windows Forms or WinForms Data Access would have done as well as each other.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Trying to find the right category for some of these forums is like trying to find the right response to the telephone answering machines..

I agree with this sentiment is as far as the Visual STUDIO section of forums should NOT be at the top of the page, they should be at the bottom. People will naturally look for what they want among the first set of things they review, and looking at a long list of stuff just makes people get bored and pick the best option encountered so far

Given the number of non -visual studio questions in VS, i think moving it further down the p-age would stop all that

Similarly, we get so many questions in Data Access that should be in ADO.net simply because it's badly named. people are accessing a database, but they don't know they are using ADO.NET or what ADO.NET is, so they pick the more obvious forum.
 

rctaubert

Member
Joined
Aug 22, 2004
Messages
24
Programming Experience
10+
you bind a parent BindingSource to the DataSet and specify the parent DataTable as the DataMember said:
I really don't understand what you are talking about. Are you talking about right-clicking 'dsMembership...Edit in Dataset Designer' and choosing 'Add relation'????

If so, I have been doing this all along to no avail.

If not I am clueless.

I inserted your code, VS complained that 'parentBindingSource requires a 'WithEvents' variable and that 'parentBindingSource' and 'childBindingSource' are not members of the form.

In the frmMembershipDesigner.vb window I added this:

Friend WithEvents parentBindingSource As BindingSource

It made the error messages for 'parentBindingSource' go away.

Outside of your code I added this:

Dim childBindingSource As BindingSource = TblDuesPaymentBindingSource

It made the complaint about the childBindingSource go away.

Unfortuneatly, that didn't work any better so obviously I am not doing the right things.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
I really don't understand what you are talking about. Are you talking about right-clicking 'dsMembership...Edit in Dataset Designer' and choosing 'Add relation'????
That's part of it :)

Follow the DW2 link in my signature, section Creating a Simple Data App

At the end of the tutorial you will have a small app that has a customers/orders parent/child pattern, that loads and saves data - it's a good starting example

From htere you can look at how everything is set up on the forms and in the dataset designer to enable it all to work



The theory is:
Data bound controls bind through a BindingSource, to a table. A bindingsource is a device that maintains knowledge of position and current item in a block of data. In a dataset designer, if a DataRelation exists between two tables, then the parent bindingsource will expose that relation as a source of data for a child bindingsource to attach to. The child binding source uses the information on the current row of the parent, to filter its known rows down to just those that are the children of the currently selected parent row. There are 2 DataTables, Customers and Orders. There is a DataRelation that specifies Customer as the parent and Orders as the child. A Customer has Orders in a 1:many relationship.
A datagrrid on a form binds to a bindingsource. The bindingsource binds to the customers table. The customers table is full of customers
Another datagrid binds to a second bindingsource. This bindingsource binds to the Customers_Orders datarelation exposed by the first bindingsource. The orders table is full of all orders for all customers. When customer John Smith is highlighted in the customers datagridview the current Customer is John Smith. The orders bindingsource gains knowledge that the current customer is john smith and only the rows of orders belonging to john smith are shown in the orders datagridview by virtue of the fact tha tthe data relation and the child bindingsource work to filter the rows of every order, down to just the rows for John SMith
.

There is a sticky in the ADO.NET section that goes into greater detail
 

rctaubert

Member
Joined
Aug 22, 2004
Messages
24
Programming Experience
10+
Thank you for your reply.

There are several articles in your reference which I will have to read, thank you again.


There is a DataRelation that specifies Customer as the parent and Orders as the child. [/QUOTE said:
In creating a DataRelation, is the act of placing the Customer table on the left and the Orders table on the right what specifies which is the parent and which is the child or is there something else I need to do.
 

rctaubert

Member
Joined
Aug 22, 2004
Messages
24
Programming Experience
10+
I have finally got it through my thick skull.

It took reading all three of the following articles in the area you pointed me to:
Walkthrough: Saving Data to a Database (Multiple Tables)
Walkthrough: Displaying Related Data on a Form in a Windows Application
Walkthrough: Creating a Relationship between Data Tables

I went through the "Saving Data to a Database (Multiple Tables) which was what I was trying to do. There were several things in the instructions that did not work. In fact I had written you a different message asking about those things.

While waiting for a reply from you I came across the next article (Displaying Related Data) which got me a step further but still no cigar.

Finally, reading "Creating a Relationship between Data Tables" completed the picture. I think I can get to the next step now.

It would have gone a LOT faster if the articles had given links to the next relevant article instead of just alluding to them.

Anyway, I would never have done it without you pointing me in the right direction. There are several more of the articles I need to read before I'm finished.

Thanks again.
 
Last edited:

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
In creating a DataRelation, is the act of placing the Customer table on the left and the Orders table on the right what specifies which is the parent and which is the child or is there something else I need to do.

No, that's it. The labels are there, ut i'll agree that they arent obvious

ps; the very first example of Creating a Simple Data App includes use of a relation, I think.. It's quite a featured example for a primer
 
Top Bottom