Need to create link between DataSet tables to show matching records

[mike11d11]

My program has to tables in it. One is the Accounts table with all
personal information and then there is also my Memos table with all the
notes for each account. My program displays one record at a time for
each account in the dataset table. I now want to show only the notes
in the memo file that match with the Accounts table current Account #.
I have done some similar to this in Access with a subform linked by
account # to account # and it worked great, just need the same ability
in this application.

 

[RobinS]

How does your program show the data? Is it in a grid?

You could do a master/detail grid where the top shows the
Accounts and the bottom shows the Notes that go with the
currently-selected Account. Is that what you're aiming for?
Or are you just trying to get the memo information out
of the DataSet?

Robin S.

 

[mike11d11]

Right now I have my Account Detail table showing each field on my form
in individual text boxes. I would probably be good to have the notes
part show up in some sort of DataGrid at the bottom, so they can add
additional notes to that record. Any ideas on how I can do this would
be great?

 

[mike11d11]

Yes and I only want the notes for the current account showing up in the
Memos Grid at the bottom. And it has to have the functionality of
adding additional notes for the current account.

 

[RobinS]

Is the data for the account bound? So you have a bunch
of textboxes and that info is bound, and then you can
show a grid down below for the notes?

Are you using SQLServer as your data source?

Robin S.

 

[mike11d11]

Yes data in the text boxes are bound to the WorkList table. My data
source is SQL 2000 server.

 

[RobinS]

Okay, generally, you have one dataset with both of the
tables in it. Then you need to set up a binding source,
and set the data member and data source
of your controls accordingly. Here's an example.

I don't know how you are creating your dataset. I am
going to assume you created it with the DataSetDesigner?
Or are you creating it on your own? If you're creating
it on your own, that's a different ball of wax, because
you have to handle the relations on your own.

I have a dataset that has two tables: Customers and Orders.
(This runs against Northwind, so if you have that
installed, you can try this out.)

I set up a strongly-typed dataset using the Data Set
Designer with both of those tables in it, and there is
a field called CustomerID in the Orders table that links
to the CustomerID table in Customers. So Orders have a
Customer as a parent. The data relation between
the customer and the orders is defined as FK_Orders_Customers.

This is in Form_Load:

'First, populate the dataset.
Dim nwData As CustomersDataSet = CustomersDataSet.GetCustomers()

'Set the data source for the binding source to the dataset.
CustomersBindingSource.DataSource = nwData
'Add this so it knows which table to use .
CustomersBindingSource.DataMember = "Customers"
'Set the data source for the grid to the customers binding source.
CustomersDataGridView.DataSource = CustomersBindingSource

'Add the binding for the child; set its binding source to the same
' one as the parent.
Customers_OrdersBindingSource.DataSource = CustomersBindingSource
'Set the datamember to the foreign key joining the two tables.
' You can see this on your CustomersDataSet diagram.
Customers_OrdersBindingSource.DataMember = "FK_Orders_Customers"
'Set the data source on the child grid to points at its binding source.
OrdersDataGridView.DataSource = Customers_OrdersBindingSource

AddTextBoxDataBindings()
AddComboBoxDataBindings()

Here are the routines that bind the textboxes and combo box
in case you want them.

Private Sub AddTextBoxDataBindings()
'Bind each text box to the appropriate field in the
' CustomersBindingSource
CustomerIDTextBox.DataBindings.Add("Text", _
CustomersBindingSource, "CustomerID")
ContactNameTextBox.DataBindings.Add("Text", _
CustomersBindingSource, "ContactName")
CompanyNameTextBox.DataBindings.Add("Text", _
CustomersBindingSource, "CompanyName")
ContactPhoneTextBox.DataBindings.Add("Text", _
CustomersBindingSource, "Phone")
End Sub

Private Sub AddComboBoxDataBindings()
ContactsComboBox.DataSource = CustomersBindingSource
ContactsComboBox.DisplayMember = "ContactName"
ContactsComboBox.ValueMember = "CustomerID"
End Sub

Hope that provides enough info. This information
came out of Brian Noyes's book on Data Binding.

If you want to do updates, let them change the data in the
grid, then call the Update method on your table adapter:

CustomersTableAdapter.Update(ds)

I think that should work. Good luck.

Robin S.