Filtering Dataset Child Table using DataRelation and Select method

[Jesus Lara]

Could anybody tell me PLEASE why this woulnt'd work???

I got a DataSet (DS) with 4 tables, two of them have a Relation. The
name of the tables are: "CarteraClientesSIC" (Clients) and "accounts".

This is how I'm setting up the relation:

DS.Relations.Add("ClientesCuentas",
DS.Tables("CarteraClientesSIC").Columns("SIC"),
DS.Tables("accounts").Columns("SIC"), False)

Then, I want to filter the child rows using the
DS.Tables("CarteraClientesSIC").Select method and bind the result to
my datagrid (dgCarteraClientes)like this:

dgCarteraCtes.DataSource = DS.Tables("CarteraClientesSIC").Select("SIC
= '3215315' AND [child].aplicacionClav='ST'")

That's exactly what the ado.net quickstart tutorial says:
myRowArray = dsCustomers.Select("ContactName like 'Kelly%' AND
[child].OrderDate = '7/26/68'")

(dunno where they got the select method directly from the dataset tho)

The error message is this:
"Cannot find column [child.aplicacionClav]. "
But, the column it is there, when I debug the web app I get its values
accessing directly the "accounts" table.. please help

PS. I'm wroking on Visual Studio .Net B2

Chuy Lara

[David Sceppa]

Chuy,

The Select method returns an array of DataRow objects, a
structure that you cannot bind to a DataGrid. Try using a
DataView object instead.

Dim vue As New DataView(DS.Tables("accounts"))
vue.RowVilter = "SIC = '3215315' AND aplicacionClav='ST'"
MyDataGrid.DataSource = vue

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2002 Microsoft Corporation. All rights reserved.

[Jesus Lara]

Thanks for the reply David.

Yes, it was wrong trying to bind a datarow array to a datagrid. But,
what I actually was trying to acomplish was to apply a filter to a child
table FROM the parent table and see if the parent would auto-filter.

I think I didn't explain myself correctly, I'll try harder this time:

This is what I got:
1- A DataSet with several tables
2- Two of them (Clients & Accounts) are related with the column "SIC"

DS.Relations.Add("ClientesCuentas",
DS.Tables("CarteraClientesSIC").Columns("SIC"),
DS.Tables("accounts").Columns("SIC"), False)

3- In the aspx I got two DataGrids.. a parent DataGrid
(dgCarteraClientes) with a template column and another Datagrid
(dgAccounts) inside it.

4- Filtering and sorting the parent table is easy because I just create
a dataview and then bind it to the parent DataGrid. (BUT, what I really
need is to filter both tables and work only with the results)

5- Here is how I fill the sub-datagrid:
In the aspx:

'.. DataGrid dgAccounts inside template column
DataSource='<%# ObtenerProductosSIC(DataBinder.Eval(Container.DataItem,
"SIC")) %>'
'... BoundColumns, etc, etc..

In the .vb:
'With this function I get only the accounts for a specific client
Function ObtenerProductosSIC(ByVal SIC As String) As DataView
Dim dwProductosCtes As New DataView()
dwProductosCtes = DS.Tables("accounts").DefaultView
dwProductosCtes.RowFilter = "SIC =" & SIC

ObtenerProductosSIC = dwProductosCtes
End Function

The problem is that I don't know how to filter the child table so that
the parent table gets "auto-filtered" before binding the parent
datagrid. In other words, I don't want to see the parent records when
there is no information available for them in the child table.

I do know the relation is the way to do that, it's just that I can't
find a code sample anywhere.

I'll appreciate a code snippet for this :)
Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jesus Lara]

Thanks for the reply David.

Yes, it was wrong trying to bind a datarow array to a datagrid. But,
what I actually was trying to acomplish was to apply a filter to a child
table FROM the parent table and see if the parent would auto-filter.

I think I didn't explain myself correctly, I'll try harder this time:

This is what I got:
1- A DataSet with several tables
2- Two of them (Clients & Accounts) are related with the column "SIC"

DS.Relations.Add("ClientesCuentas",

DS.Tables("CarteraClientesSIC").Columns("SIC"),
DS.Tables("accounts").Columns("SIC"), False)

3- In the aspx I got two DataGrids.. a parent DataGrid

(dgCarteraClientes) with a template column and another Datagrid
(dgAccounts) inside it.

4- Filtering and sorting the parent table is easy because I just create
a dataview and then bind it to the parent DataGrid. (BUT, what I really
need is to filter both tables and work only with the results)

5- Here is how I fill the sub-datagrid:
In the aspx:

'.. DataGrid dgAccounts inside template column

datasource='<%# ObtenerProductosSIC(DataBinder.Eval(Container.DataItem,

"SIC")) %>'
'... BoundColumns, etc, etc..

In the .vb:
'With this function I get only the accounts for a specific client
Function ObtenerProductosSIC(ByVal SIC As String) As DataView
Dim dwProductosCtes As New DataView()
dwProductosCtes = DS.Tables("accounts").DefaultView
dwProductosCtes.RowFilter = "SIC =" & SIC

ObtenerProductosSIC = dwProductosCtes
End Function

The problem is that I don't know how to filter the child table so that
the parent table gets "auto-filtered" before binding the parent
datagrid. In other words, I don't want to see the parent records when
there is no information available for them in the child table.

I do know the relation is the way to do that, it's just that I can't
find a code sample anywhere.

I'll appreciate a code snippet for this :)
Thanks!

dav...@online.microsoft.com (David Sceppa) wrote in message news:<zh6EIIZtBHA.1848@cpmsftngxa07>...

> --

[David Sceppa]

The simplest way to create a DataView that contains only the
child rows for a particular parent row is to use the
CreateChildView method on the DataRowView object. The Data Form
Wizard uses this method in parent/child Web forms. That's
probably the best working piece of sample code.

I don't quite understand what you mean about filtering the
parent table. If you want to bind a DataGrid to a single row,
you could always use a DataView and set the RowFilter to view
just that one row based on its key value(s).

[Jesus Lara]

The problem is that I got many filters in the child table and the parent
table as well. So, I need to show only the accounts AND clients that
fulfill those requirements.

If I filter the parent table and the child table separately, I get rows
in the parent table with no accounts because they might not be in the
filtered dataview.

Here is an example:

Clients Table (2 columns: Name and SIC):
----------------------------------------
Name: Peter
SIC: 12345

Name: Matt
SIC: 54321

Accounts Table (3 columns: SIC, Account, Product)
--------------------------------------------------
SIC: 12345
Account: 88888888
Product: IM

SIC: 12345
Account: 99999999
Product: MD

SIC: 54321
Account: 11111111
Product: MD

What I'm doing right now is filtering the parent table by Name and SIC
then, in the sub-Datagrid I filter the child table so that I return the
accounts corresponding to the client. But, If I want to see only the
products "IM" I also need some kind of "GetParentRows" method so that I
don't get to see in my parent table client "Matt" because he doesn't
have that kind of product.

Thanks

[David Sceppa]

There is a GetParentRow method on the DataRow object. You
could also apply a filter to a DataView for the parent level in
your hierarchy.

[Jesus Lara]

Oh, you're right!
So, I'd have to filter the child's dataview, create comma separated
string containing the different SICs (Master Table's IDs), and use
something like:

dvParent.rowfilter="SIC IN {" & strSICs & " }"

I'll see if it works..

Thanks for your help David.

Chuy

[David Sceppa]

The CreateChildView method of the DataRowView object will
create a DataView that contains just the child rows. If you need
to apply additional filters, then setting the RowFilter property
is the way to go.