Pierre Posted January 5, 2003 Posted January 5, 2003 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! Quote
*Gurus* divil Posted January 5, 2003 *Gurus* Posted January 5, 2003 I have never done this, but my only thought is that maybe it doesn't like a string being used in the filter parameter, as a constraint for the rows involved. Have you tried without a filter? Quote MVP, Visual Developer - .NET Now you see why evil will always triumph - because good is dumb. My free .NET Windows Forms Controls and Articles
*Experts* Nerseus Posted January 5, 2003 *Experts* Posted January 5, 2003 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 Quote "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
Pierre Posted January 5, 2003 Author Posted January 5, 2003 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! Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.