How can we get Sum of a column

[yousuf42]

Dear all,

I have a datagrid bound with tempDataTable.

Dim TempInvoiceData As TechManagement.DBComponents.ProductDB = New TechManagement.DBComponents.ProductDB
Dim DataTableName As String
tblTempInvoice = TempInvoiceData.Tempinvoice(DataTableName)

and DataTable Columns

dt.Columns.Add(New DataColumn("Sno", GetType(Integer)))
dt.Columns.Add(New DataColumn("ProdID", GetType(String)))
dt.Columns.Add(New DataColumn("Description", GetType(String)))
dt.Columns.Add(New DataColumn("Qty", GetType(Integer)))
dt.Columns.Add(New DataColumn("Uprice", GetType(Integer)))
dt.Columns.Add(New DataColumn("Amount", GetType(Integer)))

Now I want to get SUM of "Amount" column and display it in a textbox. How can I do that.

can anyone help me please?
Thanks in advance

 

[cjard]

Create a new column in the datatable, with the expression property set to "SUM([Amount])" and bind your textbox to it

 

[yousuf42]

Thank You verymuch cjard,

dc.Expression = "SUM([Amount])

This works well.

But I wanted to avoid error when there is Null or 0 in "Amount" Column (while I'm entering values into DG) So I Put this

dc.Expression = "SUM(IIF[Amount] = 0, 0,([Amount])"

But it gives error

Can you help me please?

Thanks in advance

 

[cjard]

http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.71).aspx

SUM( IIF( ISNULL(col), 0, col ) )

 

[yousuf42]

Thank you verymuch cjard,

Now I changed the code as:-

dc.Expression = "SUM(IIf(ISNULL(Amount), 0, (Amount)))"

But I have error at the start of application as:-

An unhandled exception of type 'System.Data.SyntaxErrorException' occurred in system.data.dll
Additional information: Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.

Sorry.

 

[cjard]

Maybe it cant nest that... In which case youre going to ahve to make 2 columns with the following expressions:

noNullCol.Expression = "ISNULL([Amount]), 0, [Amount])"
sumCol.Expression = "SUM(noNullCol)"

Note that the left-to-right order they appear in the table may be siignificant in terms of order of calculation, i dont know

 

[yousuf42]

Thank you verymuch cjard,

It is OK with this code:-

dcnoNull.Expression = "IsNull(Amount, -1)"

 

[cjard]

Thanks for the tip! Sorry for using the wrong syntax!