Link to home
Start Free TrialLog in
Avatar of coperations07
coperations07Flag for United States of America

asked on

Totals row in Datagridview

Hi,
I'm working with VB.Net 2005, VS2005,Access.
I am Not using ASP.Net. This is a Winform for office use, nothing to do w/ the web.
I'm populating a datagridview with data from a table in access. I want to find a way to show totals of some of the columns. I would like to have a row of totals at the bottom of the datagridview that is always visible. Something like a freeze pane. I've looked up the frozen method, but haven't figured out how to use it to accomplish this.

Thanks,
Dave
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

You can try to manually add last, summary, row in which you can display information that you need. For example, you can try to do the following:
1. Read data from database and fill System.Data.DataTable
2. Add one column to the newly created DataTable   that column might be set to true for the last, summary, row
3. Programmatically add one extra row that contains suitable summary data
4. Do the data binding to DataGridView control
5. Using appropriate event, bold or otherwise graphically distinct summary row (row that have extra column value set to true)


Avatar of coperations07

ASKER

Thanks Dhaest.  I've already created and added the totals row to the datatable. Now I will try to add the new column and set the new row value to true.
You can load that column with your data at once from the database
select ... , '0' as ExtraColumn from ...
Avatar of Sancler
Sancler

Here's an alternative for you to consider.  One form, one datagridview (called dgv) and one button.  The code below.

Although it is possible to include the totals in the dgv and in the datatable which is its datasource, I personally don't like that approach because it is altering the _data_.  Yes, I know you can code to remove any total row from the datatable before there is any further communication between the datatable and the database.  But that still leaves other possible problems - for example of sorting.

So I prefer to leave the _data_ as it is, and cope with displaying column totals in some other - unbound - control/s.  One way is labels.  But another is with an _unbound_ datagridview placed immediately below the "proper" one, but reproducing, so far as possible/desirable, the display features of the "proper" one.  Getting exactly the right display can take a bit of experimentation.  Even so, I prefer it to labels because it makes keeping the detail and the totals displays "in synch".  The demo illustrates the idea.  Run the form then click the button.  Alter values in the "proper" table, change column widths, scroll across the columns.

See what you think.

Roger
Public Class Form1
 
    Private dt As New DataTable
    Private dgvtotal As New DataGridView
 
    Private Sub maketable()
        Dim dc0 As New DataColumn("ID", GetType(Integer))
        dc0.AutoIncrement = True
        dt.Columns.Add(dc0)
        Dim dc1 As New DataColumn("Name", GetType(Integer))
        dt.Columns.Add(dc1)
        Dim dc2 As New DataColumn("Item", GetType(Integer))
        dt.Columns.Add(dc2)
        Dim dc3 As New DataColumn("Desc", GetType(Integer))
        dt.Columns.Add(dc3)
        Dim dc4 As New DataColumn("Posn", GetType(Integer))
        dt.Columns.Add(dc4)
        For i As Integer = 0 To 5
            Dim dr As DataRow = dt.NewRow
            dr(1) = i.ToString
            dr(2) = i.ToString
            dr(3) = i.ToString
            dr(4) = i.ToString
            dt.Rows.Add(dr)
        Next
        dt.AcceptChanges()
    End Sub
 
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        maketable()
        dgv.DataSource = dt
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dgvtotal.Left = dgv.Left
        dgvtotal.Top = dgv.Top + dgv.Height + 5
        dgvtotal.Width = dgv.Width
        dgvtotal.Height = 20
        For Each col As DataGridViewColumn In dgv.Columns
            Dim totcol As New DataGridViewTextBoxColumn
            dgvtotal.Columns.Add(totcol)
        Next
        dgvtotal.ColumnHeadersVisible = False
        dgvtotal.AllowUserToAddRows = False
        dgvtotal.ScrollBars = ScrollBars.None
        dgvtotal.ReadOnly = True
        Me.Controls.Add(dgvtotal)
        dgvtotal.Rows.Add()
        doWidths()
        doTotals()
    End Sub
 
    Private Sub doTotals()
        For i As Integer = 0 To dt.Columns.Count - 1
            Dim thistotal As Integer = dt.Compute("Sum(" & dt.Columns(i).ColumnName & ")", "")
            dgvtotal.Item(i, 0).Value = thistotal
        Next
    End Sub
 
    Private Sub doWidths()
        If dgvtotal.Columns.Count = 0 Then Exit Sub
        For i As Integer = 0 To dgv.Columns.Count - 1
            dgvtotal.Columns(i).Width = dgv.Columns(i).Width
        Next
        dgvtotal.FirstDisplayedScrollingColumnIndex = dgv.FirstDisplayedScrollingColumnIndex
    End Sub
    Private Sub dgv_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv.CellValueChanged
        doTotals()
    End Sub
 
    Private Sub dgv_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles dgv.Paint
        doWidths()
    End Sub
End Class

Open in new window

thanks for idea Sancler. I'm trying to plug this in,but I'm getting this error:  
Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.
In this Sub:
    Private Sub doTotals()
        For i As Integer = 0 To dt.Columns.Count - 1
            Dim thistotal As Integer = dt.Compute("Sum(" & dt.Columns(i).ColumnName & ")", "")
            dgvtotal.Item(i, 0).Value = thistotal
        Next
    End Sub
That's odd.  First, it was a direct paste from a working (albeit demo) app.  Second, I've just tried it again - on both 2005 and 2008 - and it's working for me.  Third, it should resolve to "a single column argument".  Assume a column(i)'s columnname is "Col1".  That would be substituted for dt.Columns(i).ColumnName, making the first argument

     "Sum(" & "Col1" & ")"

which, concatenated, is

     "Sum(Col1)"

Fourth, eVen if you've added a column without expressly giving it a name, my understanding is that VB.NET would add one - "Column" plus the first free number.

But it's happening.  Can you please change the code a bit so we can see why.  Change this

    Private Sub doTotals()
        For i As Integer = 0 To dt.Columns.Count - 1
            Dim thistotal As Integer = dt.Compute("Sum(" & dt.Columns(i).ColumnName & ")", "")
            dgvtotal.Item(i, 0).Value = thistotal
        Next
    End Sub

To this

    Private Sub doTotals()
        Dim TotalString As String = ""
        For i As Integer = 0 To dt.Columns.Count - 1
            TotalString = "Sum(" & dt.Columns(i).ColumnName & ")"
            Debug.WriteLine(TotalString)
            Dim thistotal As Integer = dt.Compute(TotalString, "")
            dgvtotal.Item(i, 0).Value = thistotal
        Next
    End Sub

And, if you can't spot the problem from it, post the Debug output back here.  Thanks

Roger
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Sancler. I'm still trying to plug this code in. I replace the doTotals Sub w/ the other one and I got the same error with it.
But when you get the error, what is printed in the Immediate Window?  Or, when it breaks, with that error, hover your mouse over the variable TotalString and a tooltip should appear saying what its value is?  What does that say?

Roger
Alright, I've tried this just the way you set it up and it works. Looks Great! Now I'll just have to mingle this code into what I'm already using....
OK, I think I've found what the problem might be with you plumbing my code into your setup.  I think one or more of your ColumnNames have "odd" characters in them.  By "odd", in this sense, I mean things like spaces, apostrophes, double-quotes, brackets that the system, when parsing the string used as the filter expression - that is, TotalString in the code I offered for debugging - treats as "separators".  If you can change such names in the database, so that you use only alphanumeric characters, dashes and underscores in field/column names, you will save yourself a lot of possible future trouble.

But if you cannot do that, change this

            Dim thistotal As Integer = dt.Compute("Sum(" & dt.Columns(i).ColumnName & ")", "")

to this

            Dim thistotal As Integer = dt.Compute("Sum([" & dt.Columns(i).ColumnName & "])", "")

Note the addition of the opening and closing square brackets within the existing round brackets.  Those should make sure that, even if your columnnames do include "odd" characters, the parser should still recognise them as part of the colunnames rather than separators.

If you want to read up on the detailed rules about this, you will find them here

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

Although that specifically relates to Expression columns in datatables, the same rules apply to filters and for methods like .Compute.

Roger
I do have a # symbol in one field. I can try to change that.
If I get this total field set up the way your suggesting will I be able to make the total update when a value is changed in the column?
That's precisely what this sub is for

    Private Sub dgv_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv.CellValueChanged
        doTotals()
    End Sub

The totals will, however, only change when the user _finishes_ editing the cell concerned - e.g. by moving to a different cell - and not just as s/he is typing in a new value.

Roger
Well, I'm still fumbling around w/ this. I've got the total row to populate in the correct spot under the DGV, but it only populates after I click a cell in the DGV.  When I click a cell to edit the value the focus goes to the first column in the clicked row and highlight the value in that cell. Also I haven't been able to get the totals to update on a cell click/value change. Here's what I have so far:
Public Class frmWaves
    Private dgvtotal As New DataGridView
    Private tSorter As New DataTable
Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRun.Click
        'Load DataGridView*********************************************
        mAdapter.SelectCommand = _
            New OleDb.OleDbCommand(SQL1, New OleDb.OleDbConnection(connectionString))
        'Auto-Generate INSERT, UPDATE and DELETE sql commands
        Dim cb As New OleDb.OleDbCommandBuilder(mAdapter)
        mAdapter.AcceptChangesDuringFill = False
        'Fill table
        mAdapter.Fill(tSorter)
        'Set as DataSource for grid
        Me.dgResults.DataSource = tSorter
        Me.dgResults.AutoResizeColumns( _
           DataGridViewAutoSizeColumnsMode.AllCells)
 
        'flag columns that get totaled.
        tSorter.Columns.Item(7).Caption = "DoTotal"
        tSorter.Columns.Item(8).Caption = "DoTotal"
        tSorter.Columns.Item(9).Caption = "DoTotal"
        tSorter.Columns.Item(10).Caption = "DoTotal"
        tSorter.Columns.Item(11).Caption = "DoTotal"
        tSorter.Columns.Item(12).Caption = "DoTotal"
        tSorter.Columns.Item(13).Caption = "DoTotal"
        tSorter.Columns.Item(14).Caption = "DoTotal"
        tSorter.Columns.Item(15).Caption = "DoTotal"
        tSorter.AcceptChanges()
 
        'Setup totals datagridview
        Dim dgvtotallabel As New Label
        dgvtotallabel.Text = "Totals"
        dgvtotallabel.TextAlign = ContentAlignment.MiddleLeft
        dgvtotallabel.AutoSize = True
        dgvtotallabel.Left = dgResults.Left + 3
        dgvtotallabel.Top = dgResults.Top + dgResults.Height + 5
        dgvtotal.Left = dgResults.Left + 40 'allow for rowheaders in main grid
        dgvtotal.Top = dgResults.Top + dgResults.Height - 2
        dgvtotal.Width = dgResults.Width - 40 - 20 'allow for rowheaders and vertical scrollbar in main grid
        dgvtotal.Height = 20
        For Each col As DataGridViewColumn In dgResults.Columns
            Dim totcol As New DataGridViewTextBoxColumn
            dgvtotal.Columns.Add(totcol)
        Next
 
        'make grid non-reactive
        dgvtotal.RowHeadersVisible = False
        dgvtotal.ScrollBars = ScrollBars.None
        dgvtotal.ColumnHeadersVisible = False
        dgvtotal.AllowUserToAddRows = False
        dgvtotal.ReadOnly = True
 
        'make row non-reactive
        Dim dgvr As New DataGridViewRow
        dgvr.DefaultCellStyle.SelectionForeColor = Color.Black
        dgvr.DefaultCellStyle.SelectionBackColor = Color.White
        dgvtotal.Rows.Add(dgvr)
        Me.Controls.Add(dgvtotal)
        Me.Controls.Add(dgvtotallabel)
 
        doWidths()
        doTotals()
End Sub
 
 'set up datagridview totals row from here down in the code****************************************
 
    Private Sub doTotals()
        For i As Integer = 0 To tSorter.Columns.Count - 1
            'only process if Caption (vice Tag) is marked
            If tSorter.Columns(i).Caption = "DoTotal" Then
                Dim thistotal As Integer = tSorter.Compute("Sum(" & tSorter.Columns(i).ColumnName & ")", "") 'can use second argument to filter if dataview with .RowFilter is used
                dgvtotal.Item(i, 0).Value = thistotal
            End If
        Next
    End Sub
 
    Private Sub doWidths()
        'with demo setup there'll be a paint of main grid before total grid is ready
        If dgvtotal.Columns.Count = 0 Then Exit Sub
        'synchronise column widths
        For i As Integer = 0 To dgResults.Columns.Count - 1
            dgvtotal.Columns(i).Width = dgResults.Columns(i).Width
        Next
        'synchronise column scrolling
        dgvtotal.FirstDisplayedScrollingColumnIndex = dgResults.FirstDisplayedScrollingColumnIndex
    End Sub
    Private Sub dgResults_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
        tSorter = Me.dgResults.DataSource
        tSorter.Columns.Item(7).Caption = "DoTotal"
        tSorter.Columns.Item(8).Caption = "DoTotal"
        tSorter.Columns.Item(9).Caption = "DoTotal"
        tSorter.Columns.Item(10).Caption = "DoTotal"
        tSorter.Columns.Item(11).Caption = "DoTotal"
        tSorter.Columns.Item(12).Caption = "DoTotal"
        tSorter.Columns.Item(13).Caption = "DoTotal"
        tSorter.Columns.Item(14).Caption = "DoTotal"
        tSorter.Columns.Item(15).Caption = "DoTotal"
        tSorter.AcceptChanges()
 
        doTotals()
    End Sub
 
    Private Sub dgResults_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs)
        doWidths()
    End Sub
 
End Class

Open in new window

I don't understand why lines 19 to 28 have been repeated - as lines 89 t0 98 - in the dgResults_CellValueChanged sub.  Nor why line 88 appears in the dgResults_CellValueChanged sub.

I cannot, at the moment explain (I'm not sure I totally understand the symptoms) the other phenomena you describe.  I can see nothing in the code which would cause them.  But change this

    Private Sub dgResults_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
        tSorter = Me.dgResults.DataSource
        tSorter.Columns.Item(7).Caption = "DoTotal"
        tSorter.Columns.Item(8).Caption = "DoTotal"
        tSorter.Columns.Item(9).Caption = "DoTotal"
        tSorter.Columns.Item(10).Caption = "DoTotal"
        tSorter.Columns.Item(11).Caption = "DoTotal"
        tSorter.Columns.Item(12).Caption = "DoTotal"
        tSorter.Columns.Item(13).Caption = "DoTotal"
        tSorter.Columns.Item(14).Caption = "DoTotal"
        tSorter.Columns.Item(15).Caption = "DoTotal"
        tSorter.AcceptChanges()
 
        doTotals()
    End Sub

to this

    Private Sub dgResults_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
        doTotals()
    End Sub

and try again.  It's possible that those extra, unnecessary, lines are having some effect.

And, by the way, it's not essential to do all the setting up in a Button's code.  I just did it that way for the purposes of the demo.  There's no reason why it shouldn't go, for instance, in the Form_Load sub if that's the pattern that you normally follow.

Roger
I removed the unnecessary lines. Not sure why I had them in there guess I was just thinking about it wrong. That fixed the focus problem I was having, so now when I click a cell I can edit it. The total row is also populating when the main DGV populates now, so thats working good. I was still having a problem w/ the totals cells not updating, so I put a breakpoint in the cellvaluechanged event. The event was never getting triggered.  So I put the code (below) in and now the totals update. This makes no sense to me, but it works.  I did the same thing w/ the Paint event and it had the same outcome. So now everything is working I just need to get the scrolling part synchronised a little better.
Thanks Sancler!
    Private Sub dgResults_CellValueChanged1(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgResults.CellValueChanged
        doTotals()
    End Sub

Open in new window

>>
The event was never getting triggered.  So I put the code (below) in and now the totals update. This makes no sense to me, but it works.  
<<

In the pasting, the Handles bit got missed off the end of the two subs in the original code.  See lines 87 and 103.

Thanks for the points.

Roger