Aggregate column uses defaultview?

[benji]

Hi,

I have two tables related to eachother, and one table has an
expression-based column that states "SUM(Child.Price)". Am I able to adjust
the child table's default view, and expect that the result of the SUM will be
affected? Is there any extra action I need to take to trigger a "refresh"
after adjusting the default view?

Thanks...

-Ben

 

[Cor Ligthert[MVP]]

Benji,

It depends how you refresh your tables, as you create everytime new
datatables (or datasets), then you have to refresh (set) the datasource as
well. The datasource is set to a reference so with a new dataset
(datatable), that needs as well to set to that.

I am not sure if this answers your question, however in my idea is your
question a little bit of a puzzle because it can be about thousands of
different problems with a factor of solutions to that.

Cor

 

[benji]

Hi Cor,

Thanks for your response. I think a code sample might be helpful. Take a
look at the following:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
Dim mydatset As New Data.DataSet

Dim mytable1 As New Data.DataTable("Parent")
Dim mytable2 As New Data.DataTable("Child")
mydatset.Tables.Add(mytable1)
mydatset.Tables.Add(mytable2)

Dim mycol As New Data.DataColumn("Product", GetType(String))
Dim mycolchild As New Data.DataColumn("Product", GetType(String))
mytable1.Columns.Add(mycol)
mytable2.Columns.Add(mycolchild)
mytable2.Columns.Add("Price", GetType(Double))
mytable2.Columns.Add("PurchaseDate", GetType(DateTime))


Dim myrow = mytable2.NewRow()
Dim rowVals(2) As Object
rowVals(0) = "Telephone"
rowVals(1) = 50.5
rowVals(2) = DateTime.Parse("7/7/2007")


mytable2.Rows.Add(rowVals)

rowVals(0) = "Telephone"
rowVals(1) = 50.5
rowVals(2) = DateTime.Parse("7/7/2006")

mytable2.Rows.Add(rowVals)
mytable2.DefaultView.RowFilter = "PurchaseDate>#8/8/2006#"

mydatset.Relations.Add("MyRelation",
mydatset.Tables("Parent").Columns("Product"),
mydatset.Tables("Child").Columns("Product"), False)

mytable1.Columns.Add("MyExprCol", GetType(Double), "SUM(Child.Price)")


Dim newrowVals(0) As Object
newrowVals(0) = "Telephone"
mytable1.Rows.Add(newrowVals)


MyGrid.DataSource = mytable1
MyGrid.DataBind()

End Sub

The datagrid shows

Product MyExprCol
Telephone 101

Therefore it is ignoring the default filter. If it were minding the filter,
it would ignore the 7/7/2006 entry and show a sum of 50.5, rather than 101.
I've tried setting the filter in different spots in code. Is this expected?
How can I use a subset of the data for the aggregation?

Thanks...

-Ben

 

[Cor Ligthert[MVP]]

Benji,

I don't think the expression is that strong that it takes the defaultview.

This was what came into my mind (it is tested with your code and gave 50,5)

\\\
Dim mytable3 As DataTable = mytable2.DefaultView.ToTable
mytable3.TableName = "FilteredChild"
mydatset.Tables.Add(mytable3)

mydatset.Relations.Add("MyRelation",
mydatset.Tables("Parent").Columns("Product"),
mydatset.Tables("FilteredChild").Columns("Product"), False)
///

By the way; Nicely done those culture independent dates and a very good way
to show the problem.

Cor