Jump to content
Xtreme .Net Talk

Recommended Posts

Posted

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!

  • *Experts*
Posted

Is expenseAmount a numeric field, like int, decimal, etc? It sounds like it's a String field. This would make sense since you're loading a DataSet from an XML file. If your XML doesn't contain a schema which defines each column's datatype, everything is assumed to be string.

 

Where is the XML file coming from? If you're creating it, you could add a schema to it to define the table definition (each column with a datatype). Otherwise you'll 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

"I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
Posted

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 doesn't contain a schema which defines each column's datatype, everything is assumed to be string" is the answer. I will adjust my serialization program accordingly.

Thanks!

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...