Linking DataSets

[MplsCpl]

I'm going to try to be as succinct as possible when asking this
question, so bear with me.

Let's say I have two tables in a DB: 'salespeople ' and 'contacts'.
'salespeople' has a primary key field "id", which is the foreign key to
'contacts', which is the table that lists all contacts of the
salespeople. To be brief, here's a pseudo-schema:

table salespeople:
int id
....

table contacts:
int contactId,
int salesId (a froeign key to the 'salespeople' table
....

My question is, can I create two DataSets from a DataAdapter and when
the position of the 'salespeople' table is advanced, the data in the
'contacts' table is automatically refreshed to reflect only the
contacts which are associated with the current salesId from the
'salespeople' table?

The point being I want the 'contacts' table in the dataset to reflect
only the contacts that are related to the current salesperson, as
indicated by the current record in the 'salespeople' table.
Mike

 

[Marina]

You should use only 1 dataset, and have multiple datatables in it. You can
then set up a DataRelation between 2 of the tables to refelect the foreign
key.

 

[MplsCpl]

I understand that what you suggest is the way that I would enforce a
constraint dynamically in a DataSet. What I want is the data in my
'contacts' table to be updated in the dataset based on what row of the
'salespeople' table is selected. For example, assume I already have a
DataAdapter and a DataSet defined, I want to be able to do the
following in code:

---
dataAdapter.Fill(dataSet);

//... here I would work with the data from the first row of
"salespeople" and the
// corresponding rows in 'contacts', where 'contacts' is only filled
with rows
// related to the salesperson at the current position

this.BindingContext[dataSet].Position += 1;

// Now I want to work with the second row in 'salespeople', and I want
the
// 'contacts' DataTable to be automatically populated with contacts
related to
// the second salesperson, which may be a totally different set of
contacts
// than what I had before I changed the Position

 

[Marina]

You want the dataset to automatically go out to the database and get this
data for you? That is not going to happen.

 

[cecil]

Mike, You need to handle the CurrencManagers CurrentChanged event to
get the contacts you need.
Example from the Northwind database:

Private WithEvents cm As CurrencyManager

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
daCustomers.Fill(CustomerOrders1.Customers)
dg.DataSource = CustomerOrders1.Customers
cm = CType(Me.BindingContext(CustomerOrders1.Customers),
CurrencyManager)
End Sub

Private Sub cm_CurrentChanged(ByVal sender As Object, ByVal e As
System.EventArgs) Handles cm.CurrentChanged
daOrders.SelectCommand.Parameters("@CustomerId").Value = _
CustomerOrders1.Customers(cm.Position).CustomerID
daOrders.Fill(CustomerOrders1.Orders)
End Sub

I have not completely tested this but it gets you where you want to go.
One gotcha that jumps out at me is the DataAdapter's default behavior
is to Accept changes when you call fill, you might want to change this
if you have made any changes to the table that have not been updated.
Hope this helps

Cecil Howell MCSD, MCT

 

[user]

You should use only 1 dataset, and have multiple datatables in it. You can
then set up a DataRelation between 2 of the tables to refelect the foreign
key.

....and then you will want to use the GetChildRows method of the DataRow object.