add total row to bound datagridview

how can i add a total row to the bottom of my datagridview.

my dgv is bound to a datatable in a dataset.  what i would like to to append a total row to the grid.  no updates need to be made to the actual data.

it does need to be on the bottom of the dgv though as that data is often exported directly into excel, and hopefully soon i will be exporting in to word as well
MarcuswAsked:
Who is Participating?
 
vbturboConnect With a Mentor Commented:
Hi,

if you're working with DataTables, you can perform aggregate function over its rows. For example, table.Compute("sum(price)", "") would calculate the sum on all rows over price column in your DataTable.

as shown here

dim myvalue as Object
myvalue = dsSale.Tables("Grid").Compute("Sum(columnToBeCalulated)", "")

should give the total(sum of all rows) in that column.

now since you have that value you only need to add a row to your grid and assign the different data in that row.



0
 
vbturboCommented:
Hi

you can use the DataColumn.Expression Property which also provide aggregation and sum's collumns

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

Or

dim myvalue as Object
myvalue = dsSale.Tables("Sales").Compute("Sum(columntotal)", String.Empty)

DataGridView1.Rows.Add(Column1Data,"",myvalue, etc.)

vbturbo
0
 
MarcuswAuthor Commented:
thanks for the code but i now get this error
Rows cannot be programmatically added to the DataGridView's rows collection when the control is data-bound.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
vbturboCommented:
sorry , the code for inserting into the datagridview was for a unbounded grid

try this

  Dim dr As DataRow = dsSale.Tables("Sales").NewRow
   'Put values into row
   dr(0) = ""
   dr(1) = "total ="
   dr(2) = myvalue
   dr(3) = "$"

   'and so on, making sure you put values in any ...
   '... fields/columns that do not allow null
   dsSale.Tables("Sales").Rows.Add(dr)

vbturbo
0
 
MarcuswAuthor Commented:
i have had to alter your code slightly as the number of columns and the names of the column change every time the datatable is filled. so i have tired this

        Dim drw As DataRow = ds.Tables("Grid").NewRow
        'Put values into row
        drw(0) = ""
        For i As Integer = 1 To drw.Table.Columns.Count
            drw(i) = ds.Tables("Grid").Compute("Sum(" & i & ")", String.Empty)
        Next

but now i get the following error

Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.
0
 
MarcuswAuthor Commented:
the only thing that remains the same is that the first column is a description column
0
 
vbturboConnect With a Mentor Commented:
this add a new(last) in your grid

  Dim dr As DataRow = dsSale.Tables("Sales").NewRow
   'Put values into row
   dr(0) = ""
   dr(1) = "total ="
   dr(2) = myvalue' the sum you computed from your column
   dr(3) = "$"

   'and so on, making sure you put values in any ...
   '... fields/columns that do not allow null
   dsSale.Tables("Sales").Rows.Add(dr)
0
 
MarcuswAuthor Commented:
i have altered my code to this, so i now use your myvalue variable

        Dim drw As DataRow = ds.Tables("Grid").NewRow
        Dim myvalue As Object
        Dim cnt As Integer = 0
        For Each c As DataColumn In ds.Tables("Grid").Columns
            If c.ColumnName <> cmbRows.Text Then

                myvalue = ds.Tables("Grid").Compute("Sum(" & c.ColumnName & ")", "")
                drw(cnt) = myvalue 'ds.Tables("Grid").Compute("Sum(" + c.ColumnName + ")", String.Empty)
                myvalue = Nothing
                cnt = cnt + 1
            Else
                drw(cnt) = "TOTAL"
                cnt = cnt + 1
            End If
        Next
        ds.Tables("Grid").Rows.Add(drw)

and it adds my TOTAL column correctly but brings up an error for the next field

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

0
 
SanclerConnect With a Mentor Commented:
The compute expression must, after resolution, be a string expression.  Yours, when resolved, isn't.  If, for instance, the columnname was "Price", It would read

     myvalue = ds.Tables("Grid").Compute(Sum(Price), "")

rather than

     myvalue = ds.Tables("Grid").Compute("Sum(Price)", "")

Either stick double double quotes at the start and end of it.  Or, better in my view, declare a string and stick that in.  Like this

     Dim s As String = ("Sum(" & c.ColumnName & ")"
     myvalue = ds.Tables("Grid").Compute(s, "")

Roger
0
 
SanclerCommented:
Bad copy and paste.  For

     Dim s As String = ("Sum(" & c.ColumnName & ")"

read

     Dim s As String = "Sum(" & c.ColumnName & ")"

Roger


0
 
SanclerCommented:
I see you'e accepted my anser and given me all the points.

That looks a bit unfair to me.  The basic answer that you have used was vbturbo's, not mine.  I just corrected an error that you made in translating what vbturbo gave you into your own code.

I shall not object if you wish to have this question re-opened so that you can re-allocate the points.  Indeed, I shan't object if you do that and give all of them to vbturbo.

Roger
0
 
vbturboCommented:
Hi Roger

No problem at all regarding the assigning the points to you. -:)

Jens
0
 
SanclerCommented:
Jens

Long time no speak.

We'll have to leave it in Marcusw's hands but, if he doesn't change it, I'll owe you one ;-)

Roger
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.