3
Answers

DataTable.Compute Issue

Madelein Gerber

Madelein Gerber
13y
11.3k
1
  Hello All,

first off I'm using Visual Studio 2010, framework 4.0

What am I trying to achieve:
well, I'm getting info from my SQL database and put it into a DataTable,  I then need to do a calculation PER ROW so I add another column to my datatable (ColumnV - datatype = decimal)

here is a sample of what my data looks like: Last Column = ColumnV

1234 31224 ROWA 6020941N9999 VLIES 24875.17 1122011 800 40753 40759 40759 139 PKGS 3536 FCL 40370017 60282960 MRKU 223 190-7 40FT 0
1246 3… ROWA 6045999N FLEECE
1122011 5 40753 40759 40759


FCL 40370017 60282960 MRKU 223 190-7 40FT  0
1248 3… KOSTAL IRELAND 0277241AA SEAT MEMORY 12284.22 1122011 624 40750 40752 40760 1 PAL 167 1.123 80440792 90032500 MSKU 041089-1 40FT  0
1249 3… KOSTAL IRELAND 0277242AA SEAT MEMORY
1122011 390 40750 40752 40760



80440794 90032500 MSKU 041089-1 40FT  0
1250 3… JOHNSON LAHNWERK 6027134L ZB STRUKTUR 9377.94 1122011 432 40752 40756 40760 24 PAL 4464 28.759 21036789 1260135088 MSKU 041089-1 40FT 14.364
1251 3… JOHNSON LAHNWERK 6027126R ZB STRUKTUR
1122011 432 40752 40756 40760



21036789 1260135088 MSKU 041089-1 40FT 14.364

All good and well but now for my next challenge - I need to add another column to my datatable - and ONLY write to it once the value of the container change (3rd last column with values e.g. MSKU 041089-1)

with the below code i tried to achieve this - but NO luck

Could you please spare me some of your time and help me out with my issue:
  1. public System.Data.DataTable Calc(System.Data.DataTable ACTable)  
  2.         {  
  3.             string i_sGroupByColumn = "CONTAINER";  
  4.   
  5.             //adding column for the row count    
  6.             ACTable.Columns.Add("Results", typeof(decimal));  
  7.   
  8.             //looping thru distinct values for the group, counting    
  9.             foreach (DataRow dr in ACTable.Rows)  
  10.             {  
  11.                 for (int i = 0; i < ACTable.Rows.Count; i++)  
  12.                 {  
  13.                     System.Data.DataTable dtContainer = new System.Data.DataTable();  
  14.                     dtContainer = GetData.GetContainerSize(ACTable.Rows[i]["Type"].ToString());  
  15.                     decimal dContainerSize = Convert.ToDecimal(dtContainer.Rows[0]["Container_Size"].ToString());  
  16.   
  17.                     string cExpr = "SUM(CONVERT(ColumnV, 'System.Decimal'))";  
  18.                     string cCond = i_sGroupByColumn + " = '" + dr[i_sGroupByColumn] + "'";  
  19.   
  20.                     decimal dSumVal = (decimal)(ACTable.Compute(cExpr, cCond));  
  21.                     dr["Results"] = dSumVal / dContainerSize;  
  22.                 }     
  23.             }    
  24.             return ACTable;  
  25.         }  

currently its bombing out on: decimal dSumVal = (decimal)(ACTable.Compute(cExpr, cCond));

with error:

Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.

 

Please please please  - i need to have this project done by 5th December :(

Answers (3)
Next Recommended Forum