Linking DataSets

9 Dec 2004 6:40 PM

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
---
http://hole19.compurhythms.com

9 Dec 2004 6:54 PM

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.

<Mpls***@yahoo.com> wrote in message
Show quote

news:1102617632.670619.107160@z14g2000cwz.googlegroups.com...
> 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
> ---
> http://hole19.compurhythms.com
>

9 Dec 2004 7:06 PM

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
---

Hope that clears things up

Mike

9 Dec 2004 7:09 PM

Marina

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

<Mpls***@yahoo.com> wrote in message
Show quote

news:1102619211.875531.234250@z14g2000cwz.googlegroups.com...
> 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
> ---
>
> Hope that clears things up
>
> Mike
>

9 Dec 2004 9:05 PM

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

12 Dec 2004 2:24 PM

user

Marina wrote:

> 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.

--
Software is like sex: it's better when it's free -- [Linus Torvalds]

Fabio Marini - A+, RHCT, MCDBA, MCAD.NET
To reply: news [at] mamakin1976 [dot] plus [dot] com