I have the following table which I am displaying in a datagridview. It is a datatable based on a crosstab query.
ID Hens chicks Rabbits, etc..... Total
1 12 1 23 36
2 1 0 5 6
3 6 2 9 17
4
.
.
.
'-------------------------------------------------------------------
What I want to do is to add the rows downwards and place the answers in the last row
It should be easy to add total row to a datatable.
How can I get a sum for all the rows in a datatable and place them in the last row(add a DataRow displaying the column sums)
like shown below:
ID Hens chicks Rabbits, etc..... Total
1 12 1 23 36
2 1 0 5 6
3 6 2 9 17
total 19 3 37 59
[Here I tried to loop through the rows, sum the values and place them in the last row of each respective column]
but it doesn't work.
Dim totalsRow As DataRow = dt.NewRow()
For Each col As DataColumn In dt.Columns
Dim colTotal As Integer = 0
For Each row As DataRow In col.Table.Rows
colTotal += Int32.Parse(row(col).ToString())
Next
totalsRow(col.ColumnName) = colTotal
Next
dt.Rows.Add(totalsRow)
'-----------------------------------------
This method works for one column at a time which is named Hens, but I don't want to use named columns since i am using a crosstab query, hence the column names may change
'Calculate the sum
''Dim sum As Integer = 0
'Dim sum As Integer = CInt(sDs.Tables(0).Compute("sum(Hens)", ""))
''Create a new row
Dim row As DataRow = sDs.Tables(0).NewRow()
''set the value for the Hens item in this new row
row(1) = "Total Sum"
row("Hens") = sum
''Add the new row to the data table
'sDs.Tables(0).Rows.Add(row)
I have been stuck on this one for ages. Please assist me go about this.
Thanks in advance.