VB.NET - How can I get a sum for all the rows in a datatable and place them in the last row

Asked By kibika on 23-Apr-13 03:27 PM

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

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())
    totalsRow(col.ColumnName) = colTotal

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

I have been stuck on this one for ages. Please assist me go about this.

Thanks in advance.