Filter on child row column

[Guest]

Hi

I know that to perform a DataView.RowFilter based on a child relation, an
aggregation must be performed.

But I have a datagrid bound to a DataView (filtered by various columns) of
my parent table on which Insert/Update/Delete operations are performed. The
parent table has a relationship with it's child on a one to many basis. One
of my required filter columns is however in the child table.

My tables are in a strongly typed dataset and trips back to the sql server
are not permitted.

What would be the recommended approach to solving this problem?
Any help would be most appreciated.

 

[Guest]

Hi

As a follow up, I have now got something working.

I have created new expression datacolumns in the child table and pull the
required parent columns data into these.

Depending upon the filters used the dataview corresponding to either the
parent or child table is used to bind to the datagrid. Of course several rows
of the same parent data are displayed when multiple child rows are found in
the search but this is OK in my application. A couple of things to note
though:
1. The datagrid needs all the table and column styles re-applying when
switching the binding. Handy in this case however as it provides the
opportunity to change the columns displayed.
2. When using the datadapter.update on the child table, if there is a select
back at the end of an update or insert stored procedure then an exception is
thrown because the dataadapter tries to change the ReadOnly property of the
expression columns to False, which it cannot do. So for now I am returning
output parameters instead.
3. Need to obtain the relevant parent table IDs, if the datagrid is bound to
the child dataview in order for Update and Delete.

Not sure if this is a preferred way to do this, but it is working OK.

On point 2 above, is this fixed in ADO.NET 2.0?

 

[Kevin Yu [MSFT]]

Hi Terry,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to filter parent rows
according to the column value on the child rows. If there is any
misunderstanding, please feel free to let me know.

As far as I can see, creating an expression column in the parent table to
get the child table information is preferred.

For point 2, since .NET framework 2.0 hasn't been released, we're not quite
sure this behavior will be changed. If you have good suggestions for our
products, please feel free to send them to (e-mail address removed). Your
suggestions will be highly appreciated. Thank you for you feedback.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Hi Kevin

As far as I can see, creating an expression column in the parent table to
get the child table information is preferred.

Can this be done if the parent-child relationship is a one-many? I thought
that the expression column in the parent table refering to any child columns
had to be an aggregate function and that the data itself was not obtainable.
How can this be done?

 

[Guest]

Hi

The headache continues...

Using my proposed design, if I insert a new parent with new children at the
same time i.e:
dataadapter.update(ds,dtparent)
dataadapter.update(ds,dtchild)
ds.acceptchanges
and the child table has expression columns referencing a relationship with
the parent, ADO.NET throws up the following error on
dataadapter.update(ds,dtparent).

"There is no Original data to access"

Searching the net always seems to lead me to a BUG Q316416.
It seems that whatever avenue I go down terminates in inoperable
functionality. My project is now seriously behind schedule, since it was not
foreseen that such a simple architecture of a single parent and child table
could cause such trouble.

If anyone knows of a better (and quick!) workaround for my original problem
then please let me know. Any ideas would be most welcome.

 

[Kevin Yu [MSFT]]

Hi Terry,

Yes, as you know, if an expression column is added to the parent table, it
can only refer to an aggregate function. I think you can do some summary to
generate the column value and set the filter.

According to your code and exception message, since you have a calculation
column in a table, I think the "There is no Original data to access"
exception might be the same as the following KB article

http://support.microsoft.com/Default.aspx?kbid=839889

You can try to contact Microsoft PSS for the hotfix. The contact
information can be obtained from the KB article.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Hi Kevin

Thanks, that kb article look like it addresses the issue.

I think instead though, I will adopt a 2 stage approach to my structure,
i.e. load a look-up table containing summary info across the multi tables.
Then just use the typed dataset with relations for the add/modify/delete. It
requires another trip to the database each time though but I am at the stage
where it just needs to work now.

Thanks for your help.

 

[Kevin Yu [MSFT]]

You're welcome, Terry.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."