Aggregating a dataset column

[Pierre]

As I want to aggregate data from a dataset column, using VB.NET, I used the DataTable.Compute method.

The code that I developed is the following:

Load the list of expenses
Dim ItemsDataSet As New DataSet()
ItemsDataSet.ReadXml("ExpenseList.xml", XmlReadMode.InferSchema)
Dim CostTable As New DataTable()
CostTable = ItemsDataSet.Tables("CExpense")
Dim CostAmount As Object
CostAmount = CostTable.Compute("Sum(expenseAmount)", "expenseHouse = thisHouse")

When running it, I get the following exception: "Invalid usage of aggregate function Sum() and Type:String."

I do not understand what I need to do to fix this problem considering that the structure of this code mimics the one recommended by the .NET Framework Class Library.

FYI - The expenseAmount column that resides in the CExpense table (i.e. actually, the XML file that persists it) indeed contains only numerical data.

Any helt will be greatly appreciated!

 

[Divil]

I have never done this, but my only thought is that maybe it doesnt like a string being used in the filter parameter, as a constraint for the rows involved. Have you tried without a filter?

 

[Nerseus]

Is expenseAmount a numeric field, like int, decimal, etc? It sounds like its a String field. This would make sense since youre loading a DataSet from an XML file. If your XML doesnt contain a schema which defines each columns datatype, everything is assumed to be string.

Where is the XML file coming from? If youre creating it, you could add a schema to it to define the table definition (each column with a datatype). Otherwise youll have to conver the column to a numeric type. Not sure if you can do that directly; you may have to create a new column dynamically, set its type to decimal and set the expression property to something like "CONVERT(expenseAmount, decimal)". This may not be the correct syntax for the CONVERT function - check the help file. Then you can run compute on the expression column which will be type decimal.

-Nerseus

 

[Pierre]

Thanks, Nerseus!
The XML is indeed originating from another program that I had created, and did not include a schema. I had started suspecting this part, but without any specific clue.
Your indication that "If your XML doesnt contain a schema which defines each columns datatype, everything is assumed to be string" is the answer. I will adjust my serialization program accordingly.
Thanks!