My following piece of code draws on Northwind sample database, in
particular
tables Orders and OrderDetails.
Having added DataTables based on Orders and OrderDetails to a DataSet, I
then added the relation "FK_OrderDetails_Orders" to the dataset relating
the
two tables. Now I wish to add an expression column (namely OrderAmount) to
Orders datatable showing for each Orders row, sum total of the products
"OrderDetails.UnitPrice * OrderDetails.Quantity" for related child rows in
OrderDetails.
I tried following aggregate expression that seems a correct natural
expression for the logic but code fails with run time error
"SyntaxErrorException". Details of the error are also appended below the
code.
//Code starts here
DataColumn expressionColumn;
expressionColumn = new DataColumn("OrderAmount",
Type.GetType("System.Decimal"),
"SUM(Child(FK_OrderDetails_Orders).UnitPrice
* Child(FK_OrderDetails_Orders).Quantity)");
NWindDataSet.Tables["Orders"].Columns.Add(expressionColumn);
Details of exception
SyntaxErrorException was unhandled
Syntax error in aggregate argument; expecting a single column argument
with
possible 'Child' qualifier.
I did manage a workaround by first adding an expression colum to the
OrderDetails table (ItemAmount = UnitPrice*Quantity) and then taking the
aggregate of this child expression colum in the parent expression column.
I find msdn help quite vague on expression usage. The error suggests of a
single column limit but is equally vague. Can anyone please find out
problem
with my expression code or explain this error more precisely.
Thanks
Mansoor
