Getting total for a Column 1

[AFK1]

I have a datagrid that i want to get total for a column.
The column has Quantity that is of sting type(when get results back from Database).I need to sum up at the end. Also it should get refreshed if the quantity changes.
I want to add an additional line to the bottom of DG to show the Total.
Can someone help?

 

[RiverGuy]

Add a computed column to your DataGrid to get the total on the right. As far as the total on the bottom, I would probably fake it with some controls which have the appearance of your grid. You can loop through the dataset and increment running totals to get your final totals.

 

[SHelton]

You can also use the DataTable.Compute method, using SUM and CONVERT to total up your string quantity column, something like:

Dim i As Int32 = Convert.ToInt32(dt.Compute("SUM(CONVERT(Quantity, 'System.Int32'))", ""))

 

[RiverGuy]

SHelton--great tip.

 

[AFK1]

Thanks Shelto,

When I run my code I get an error:

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

What I am doing is creating a table than rows. Once table is created I am getting total for the Column Qty. Because I may get one row or I may get 4 rows depending on Seq.

line seq qty
1 222 5 this
2 202 5 this
3 223 5 this
----------------
Total: 15
---------------

line seq qty
1 222 5 this
2 202 5
3 222 5 this
------------------
Total: 10
-------------------
Here is my code;
'*********************************************************'
'Create a table
dt = New DataTable
dt.Columns.Add(New DataColumn("Line", GetType(String)))
dt.Columns.Add(New DataColumn("Seq", GetType(String)))
dt.Columns.Add(New DataColumn("QTY", GetType(String)))
'Create rows for each record in PDetails
For Each PDetails In PDetailsCollection
Dim dr As DataRow = dt.NewRow()
With PODetail
dr(0) = .Line
dr(1) = .Seq
dr(2) = .QTY
Me.lblLine.Text = .Line
End With
dt.Rows.Add(dr)
Dim t As Int32 = Convert.ToInt32(dt.Compute("(SUM(CONVERT(Qty, 'System.Int32')))", ""))
lblTotal.Text = "Total Qty:"
lblTotalQty.Text = t.ToString
Session("dt") = dt
Next
BindPDetailsGrid()
'*********************************************************'

Please help. What am I doing wrong

 

[SHelton]

I must admit to not checking the code before I posted it...

It seems that the Compute method of the DataTable does not support anything other than simple expressions, so to get your string values into a type than can be summed, RiverGuy's solution is also needed:

Dim dc As New DataColumn("QtyInt", GetType(Int32))

dc.Expression = "CONVERT(Qty, 'System.Int32')"

dt.Columns.Add(dc)

Dim t As Int32 = Convert.ToInt32(dt.Compute("SUM(QtyInt)", ""))

With regard to the second part of your post, am I right in thinking that you are only including quantities for the first unique sequence number in the table ? If so, the Filter property used by DataTable.Compute and also DataView.RowFilter does not support the DISTINCT function. If you can't perform this on the database server (I've no idea where your data is sourced from) you may need to resort to using something like a Hashtable to load all you sequence numbers as keys (catching and ignoring the duplicate key exceptions) and the summing the values:

Dim ht As New Hashtable

For Each dr As DataRow In dt.Rows

    Try

        ht.Add(dr("Seq"), dr("Qty"))

    Catch ae As ArgumentException

        'Duplicate key, okay to ignore

    Catch ex As Exception

        'Do something with this, it is a different error

    End Try

Next

Dim total As Int32

For Each de As DictionaryEntry In ht

    total += Convert.ToInt32(de.Value)

Next