?
Solved

add total row to bound datagridview

Posted on 2007-07-21
14
Medium Priority
?
2,098 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:Marcusw
  • 5
  • 4
  • 4
13 Comments
 
LVL 18

Expert Comment

by:vbturbo
ID: 19540561
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
 

Author Comment

by:Marcusw
ID: 19540596
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
 
LVL 18

Expert Comment

by:vbturbo
ID: 19540637
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Marcusw
ID: 19540671
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
 

Author Comment

by:Marcusw
ID: 19540673
the only thing that remains the same is that the first column is a description column
0
 
LVL 18

Accepted Solution

by:
vbturbo earned 1600 total points
ID: 19540738
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
 
LVL 18

Assisted Solution

by:vbturbo
vbturbo earned 1600 total points
ID: 19540753
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
 

Author Comment

by:Marcusw
ID: 19542465
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
 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 400 total points
ID: 19542753
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
 
LVL 34

Expert Comment

by:Sancler
ID: 19542774
Bad copy and paste.  For

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

read

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

Roger


0
 
LVL 34

Expert Comment

by:Sancler
ID: 19543106
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
 
LVL 18

Expert Comment

by:vbturbo
ID: 19543571
Hi Roger

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

Jens
0
 
LVL 34

Expert Comment

by:Sancler
ID: 19544145
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question