Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Datagridview calculate column value

Status
Not open for further replies.
ICTECH

ICTECH

Technical User
Jun 5, 2002
131
CA
Hi!!

I've been googling and not really finding much.. I'm working on an app that a grid that shoulds a clients invoice amounts and there payments. I would like to have a running total at the bottom of each columb. Would it be posisble to have this the last line of the grid even if there are nore data than the grid can display? Or could this be added to the Columns Headers? Not to replace the existing column header, but to append to it?

Thanks...
 
Sort by date Sort by votes
Based on what you have described, it's a header/details scenario. I would put your header information in a label or something else instead of tacking it onto the details grid. Something like the following layout:


Code: 
----------------------------------------------
| Client Name:  Some Client                   
| Date Range Selected:  1/1/2009 - 7/28/2009
| Total Amount Invoiced:  $1000
| Total Amount Paid:      $500
|
| Invoices/Payments (DataGridView):
| --------------------
| |Date   |Amount   |
|  ------------------- 
| |       |         |
|  -------------------
| |       |         |
|  -------------------
| |       |         |
|  -------------------
| |       |         |
| --------------------
|
|
------------------------------------------------
 
Upvote 0 Downvote
Hi RiverGuy..

Thanks.. But how do you get the totals to update automatically when the data in the grid changes? The grid is where they enter the amount the client has paid. They want to have the totals change right after the data is entered for each invoice payment...

I've been playing with the code below.. It goes through the grid and sums up the data. It works great in the form load area.. But if I tage it in the DataGridView1.CellValueChanged sub. I get overflows, etc.

Code: 
Function totalsum(ByVal colnum As Integer)
        Dim currentRow As DataGridViewRow
        Dim total As Double = 0

        For Each currentRow In DataGridView1.Rows
            total += CInt(IIf(IsDBNull(currentRow.Cells(colnum).Value), "0.0", currentRow.Cells(colnum).Value))
        Next

        Return total

    End Function

Code: 
Usage of function:

DataGridView1.Columns(3).HeaderText = "Payment (Total: " + FormatCurrency(totalsum(3)).ToString & ")"

Thanks again...
 
Upvote 0 Downvote
Hi RiverGuy..

Not sure how to incorporate this..

Here's my Datagridview1.CellValueChanged code..

Thanks for the help

Code: 
Private Sub datagridview1_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
        Dim I As Double = 0
        Dim P As Double = 0

        Dim mytable As DataTable
        Dim myrow As DataRow

        If e.RowIndex - 1 Then
            If e.ColumnIndex = 3 Then
                With Me.DataGridView1.Rows(DataGridView1.CurrentCell.RowIndex)
                    ' Column 3 = Payment $$
                    Try
                        I = .Cells(0).Value
                        P = IIf(IsDBNull(.Cells(3).Value), "0", .Cells(3).Value)
                    Catch ex As Exception
                    End Try

                End With

            End If
        End If

        For Each mytable In DataSet1.Tables
            For Each myrow In mytable.Rows
                If myrow("Invoice") = I Then
                    myrow("Payment") = P
                End If
            Next
        Next

    End Sub
 
Upvote 0 Downvote
I wouldn't rely on the cell changing. You want to know if you have data modifications, not simply if someone has keyed in a value in a grid.

Here's an example.

Code: 
Private WithEvents cm As CurrencyManager

Code: 
cm = Me.BindingContext(YourDataSetOrDataTable)

Code: 
    Private Sub cm_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles cm.ListChanged
          MessageBox.Show("List has changed.  Add a call to your calculation routine here.")
    End Sub
 
Upvote 0 Downvote
Hi RiverGuy...

Thanks.. That Worked. But I noticed, that if you click on any other control besides using the Tab Key, it cuased the following error:

An unhandled exception of type
'System.StackOverflowException' occurred in System.Data.dll

Thanks again..

 
Upvote 0 Downvote
I'd have to see you implemented the code. My example is too generic to think of what the problem is.
 
Upvote 0 Downvote
Hi RiverGuy..

Here's the code for the winform... The dataset will be populated from a database. But for now I just populate it manually for testing.. The form has a datagridview control and a couple of buttons.. Let me kow if you need anything else.. And thanks again...

Code: 
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Drawing

Public Class PaymentReceived

    Private WithEvents cm As CurrencyManager
   
    Private Sub PaymentReceived_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DataSet1.Tables(0).Rows.Add("100", "6/6/2009", "6")
        DataSet1.Tables(0).Rows.Add("101", "7/6/2009", "6", "7")

        Datagridview1_Setup()

        'DataGridView1.AutoGenerateColumns = True
        DataGridView1.DataSource = DataSet1.Tables(0)

        'calculate the running totals for the Clients Invoices & Payments
        DataGridView1.Columns(2).HeaderText = "Invoice Amount (Total: " + FormatCurrency(totalsum(2)).ToString & ")"

        DataGridView1.Columns(3).HeaderText = "Payment (Total: " + FormatCurrency(totalsum(3)).ToString & ")"

        DataGridView1.Columns(4).HeaderText = "Amount Carried Over (Total: " + FormatCurrency(totalsum(4)).ToString & ")"

        cm = Me.BindingContext(DataSet1.Tables(0))

    End Sub

    Private Sub cm_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles cm.ListChanged
        'MessageBox.Show("List has changed.  Add a call to your calculation routine here.")
        Dim I As Double = 0
        Dim P As Double = 0

        Dim mytable As DataTable
        Dim myrow As DataRow

        If DataGridView1.CurrentCell.RowIndex - 1 Then
            If DataGridView1.CurrentCell.ColumnIndex = 3 Then
                With Me.DataGridView1.Rows(DataGridView1.CurrentCell.RowIndex)
                    ' Column 3 = Payment $$
                    Try
                        I = .Cells(0).Value
                        P = IIf(IsDBNull(.Cells(3).Value), "0", .Cells(3).Value)
                    Catch ex As Exception
                    End Try

                End With

            End If
        End If

        For Each mytable In DataSet1.Tables
            For Each myrow In mytable.Rows
                If myrow("Invoice") = I Then
                    myrow("Payment") = P
                End If
            Next
        Next
        DataGridView1.Columns(3).HeaderText = "Payment"
        DataGridView1.Columns(3).HeaderText = "Payment (Total: " + FormatCurrency(totalsum(3)).ToString & ")"

        DataGridView1.Columns(4).HeaderText = "Amount Carried Over"
        DataGridView1.Columns(4).HeaderText = "Amount Carried Over (Total: " + FormatCurrency(totalsum(4)).ToString & ")"
    End Sub

    Function totalsum(ByVal colnum As Integer)
        Dim currentRow As DataGridViewRow
        Dim total As Double = 0

        For Each currentRow In DataGridView1.Rows
            total += IIf(IsDBNull(currentRow.Cells(colnum).Value), "0.0", currentRow.Cells(colnum).Value)
        Next

        Return total

    End Function
 
Upvote 0 Downvote
I don't receive any exceptions. The Payment header updates for me me when I add a new row. The Invoice Amount and Amount Carrier Over columns do not update. I modified your code a little bit to get it to work (you didn't give me the definition for DataSet1). I tried arrowing to another record and clicking on a button--no exception

Code: 
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Drawing

Public Class PaymentReceived

    Private WithEvents cm As CurrencyManager
    Private DataSet1 As New DataSet

    Private Sub PaymentReceived_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DataSet1.Tables.Add("Table1")
        DataSet1.Tables(0).Columns.Add("Col1", System.Type.GetType("System.String"))
        DataSet1.Tables(0).Columns.Add("Col2", System.Type.GetType("System.String"))
        DataSet1.Tables(0).Columns.Add("Invoice", System.Type.GetType("System.Decimal"))
        DataSet1.Tables(0).Columns.Add("Payment", System.Type.GetType("System.Decimal"))
        DataSet1.Tables(0).Columns.Add("Col5", System.Type.GetType("System.String"))



        DataSet1.Tables(0).Rows.Add("100", "6/6/2009", "6")
        DataSet1.Tables(0).Rows.Add("101", "7/6/2009", "6", "7")

        'Datagridview1_Setup()

        'DataGridView1.AutoGenerateColumns = True
        DataGridView1.DataSource = DataSet1.Tables(0)

        'calculate the running totals for the Clients Invoices & Payments
        DataGridView1.Columns(2).HeaderText = "Invoice Amount (Total: " + FormatCurrency(totalsum(2)).ToString & ")"

        DataGridView1.Columns(3).HeaderText = "Payment (Total: " + FormatCurrency(totalsum(3)).ToString & ")"

        DataGridView1.Columns(4).HeaderText = "Amount Carried Over (Total: " + FormatCurrency(totalsum(4)).ToString & ")"

        cm = Me.BindingContext(DataSet1.Tables(0))

    End Sub

    Private Sub cm_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles cm.ListChanged
        'MessageBox.Show("List has changed.  Add a call to your calculation routine here.")
        Dim I As Double = 0
        Dim P As Double = 0

        Dim mytable As DataTable
        Dim myrow As DataRow

        If DataGridView1.CurrentCell.RowIndex - 1 Then
            If DataGridView1.CurrentCell.ColumnIndex = 3 Then
                With Me.DataGridView1.Rows(DataGridView1.CurrentCell.RowIndex)
                    ' Column 3 = Payment $$
                    Try
                        I = .Cells(0).Value
                        P = IIf(IsDBNull(.Cells(3).Value), "0", .Cells(3).Value)
                    Catch ex As Exception
                    End Try

                End With

            End If
        End If

        For Each mytable In DataSet1.Tables
            For Each myrow In mytable.Rows
                If myrow("Invoice") = I Then
                    myrow("Payment") = P
                End If
            Next
        Next
        DataGridView1.Columns(3).HeaderText = "Payment"
        DataGridView1.Columns(3).HeaderText = "Payment (Total: " + FormatCurrency(totalsum(3)).ToString & ")"

        DataGridView1.Columns(4).HeaderText = "Amount Carried Over"
        DataGridView1.Columns(4).HeaderText = "Amount Carried Over (Total: " + FormatCurrency(totalsum(4)).ToString & ")"
    End Sub

    Function totalsum(ByVal colnum As Integer)
        Dim currentRow As DataGridViewRow
        Dim total As Double = 0

        For Each currentRow In DataGridView1.Rows
            total += IIf(IsDBNull(currentRow.Cells(colnum).Value), "0.0", currentRow.Cells(colnum).Value)
        Next

        Return total

    End Function

End Class
 
Upvote 0 Downvote
Hi RiverGuy..

Sorry, The Dataset1 has 5 members..

Invoice - Invoice #
Date - Date the Invoice was issued
Amount - the total amount for the Invoice
Payment - this gets entered manually
Difference - this is calculated by the datset (Payment - Amount)

There all System.Double except for the Invoice & Date they are strings.

Code: 
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Drawing

Public Class PaymentReceived

    Private WithEvents cm As CurrencyManager

    Dim dataset2 As New DataSet

    Private Sub PaymentReceived_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DataSet1.Tables(0).Rows.Add("100", "6/6/2009", "6")
        DataSet1.Tables(0).Rows.Add("101", "7/6/2009", "6", "7")

        Dim OdbcDA As New OleDbDataAdapter("SELECT Cancer FROM Cancer", MyConnectionString)
        OdbcDA.Fill(DataSet2, "Table1")

        Datagridview1_Setup()

        'DataGridView1.AutoGenerateColumns = True
        DataGridView1.DataSource = DataSet1.Tables(0)

        'calculate the running totals for the Clients Invoices & Payments
        DataGridView1.Columns(2).HeaderText = "Invoice Amount (Total: " + FormatCurrency(totalsum(2)).ToString & ")"

        DataGridView1.Columns(3).HeaderText = "Payment (Total: " + FormatCurrency(totalsum(3)).ToString & ")"

        DataGridView1.Columns(4).HeaderText = "Amount Carried Over (Total: " + FormatCurrency(totalsum(4)).ToString & ")"

        cm = Me.BindingContext(DataSet1.Tables(0))


    End Sub

    Private Sub cm_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles cm.ListChanged
        'MessageBox.Show("List has changed.  Add a call to your calculation routine here.")
        Dim I As Double = 0
        Dim P As Double = 0

        Dim mytable As DataTable
        Dim myrow As DataRow

        If DataGridView1.CurrentCell.RowIndex - 1 Then
            If DataGridView1.CurrentCell.ColumnIndex = 3 Then
                With Me.DataGridView1.Rows(DataGridView1.CurrentCell.RowIndex)
                    ' Column 3 = Payment $$
                    Try
                        I = .Cells(0).Value
                        P = IIf(IsDBNull(.Cells(3).Value), "0", .Cells(3).Value)
                    Catch ex As Exception
                    End Try

                End With

            End If
        End If

        For Each mytable In DataSet1.Tables
            For Each myrow In mytable.Rows
                If myrow("Invoice") = I Then
                    myrow("Payment") = P
                End If
            Next
        Next
        DataGridView1.Columns(3).HeaderText = "Payment"
        DataGridView1.Columns(3).HeaderText = "Payment (Total: " + FormatCurrency(totalsum(3)).ToString & ")"

        DataGridView1.Columns(4).HeaderText = "Amount Carried Over"
        DataGridView1.Columns(4).HeaderText = "Amount Carried Over (Total: " + FormatCurrency(totalsum(4)).ToString & ")"
    End Sub

    Function totalsum(ByVal colnum As Integer)
        Dim currentRow As DataGridViewRow
        Dim total As Double = 0

        For Each currentRow In DataGridView1.Rows
            total += IIf(IsDBNull(currentRow.Cells(colnum).Value), "0.0", currentRow.Cells(colnum).Value)
        Next

        Return total

    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DirectCast(Me.ParentForm, MDIParent1).MenuStrip1.Items(0).Enabled = True
        DirectCast(Me.ParentForm, MDIParent1).MenuStrip1.Items(1).Enabled = True
        DirectCast(Me.ParentForm, MDIParent1).MenuStrip1.Items(2).Enabled = True
        DirectCast(Me.ParentForm, MDIParent1).MenuStrip1.Items(3).Enabled = True
        DirectCast(Me.ParentForm, MDIParent1).MenuStrip1.Items(4).Enabled = True
        DirectCast(Me.ParentForm, MDIParent1).MenuStrip1.Items(5).Enabled = True
        DirectCast(Me.ParentForm, MDIParent1).MenuStrip1.Items(6).Enabled = True
        DirectCast(Me.ParentForm, MDIParent1).MenuStrip1.Items(7).Enabled = True
        DirectCast(Me.ParentForm, MDIParent1).MenuStrip1.Items(8).Enabled = True

        Close()
    End Sub


#Region " Datagridview Setup "

    Private Sub Datagridview1_Setup()

        With DataGridView1
            .BackColor = Color.GhostWhite
            .BackgroundColor = Color.Lavender
            .BorderStyle = BorderStyle.None
            .Font = New Font("Tahoma", 12.0!)
        End With

        Dim grdTableStyle1 As New DataGridTableStyle
        With grdTableStyle1
            .AlternatingBackColor = Color.GhostWhite
            .BackColor = Color.GhostWhite
            .ForeColor = Color.MidnightBlue
            .GridLineColor = Color.RoyalBlue
            .HeaderBackColor = Color.MidnightBlue
            .HeaderFont = New Font("Tahoma", 12.0!, FontStyle.Bold)
            .HeaderForeColor = Color.Lavender
            .SelectionBackColor = Color.Teal
            .SelectionForeColor = Color.PaleGreen
            ' Do not forget to set the MappingName property. 
            ' Without this, the DataGridTableStyle properties
            ' and any associated DataGridColumnStyle objects
            ' will have no effect.
            .MappingName = "Table1"
            .PreferredColumnWidth = 125
            .PreferredRowHeight = 15
        End With

        Dim grdColStyle0 As New DataGridViewTextBoxColumn
        With grdColStyle0
            .HeaderText = "Invoice #"
            .DataPropertyName = "Invoice"
            .Width = 105
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .ReadOnly = True
        End With

        Dim grdColStyle1 As New DataGridViewTextBoxColumn
        With grdColStyle1
            .HeaderText = "Invoice Date"
            .DataPropertyName = "Date"
            .Width = 148
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .ReadOnly = True
        End With

        Dim grdColStyle2 As New DataGridViewTextBoxColumn
        With grdColStyle2
            .HeaderText = "Invoice Amount"
            .DataPropertyName = "Amount"
            .Width = 150
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .ReadOnly = True
        End With

        Dim grdColStyle3 As New DataGridViewTextBoxColumn
        With grdColStyle3
            .HeaderText = "Payment"
            .DataPropertyName = "Payment"
            .Width = 120
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .ReadOnly = False
        End With

        Dim grdColStyle4 As New DataGridViewTextBoxColumn
        With grdColStyle4
            .HeaderText = "Amount Carried Over"
            .DataPropertyName = "Difference"
            .Width = 190
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .ReadOnly = True
        End With

        ' Add the style objects to the table style's collection of 
        ' column styles. Without this the styles do not take effect.        


        Me.DataGridView1.Columns.Clear()

        Me.DataGridView1.Columns.AddRange(grdColStyle0, grdColStyle1, grdColStyle2, grdColStyle3, grdColStyle4)

        Me.DataGridView1.MultiSelect = False
        Me.DataGridView1.RowHeadersVisible = False

        Dim i As Integer = 0

        Do While i < 5
            Me.DataGridView1.Columns(i).SortMode = DataGridViewColumnSortMode.NotSortable
            Me.DataGridView1.Columns(i).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            Me.DataGridView1.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

            i += 1
        Loop

        Me.DataGridView1.Columns(2).DefaultCellStyle.Format = "c"
        Me.DataGridView1.Columns(3).DefaultCellStyle.Format = "c"
        Me.DataGridView1.Columns(4).DefaultCellStyle.Format = "c"

    End Sub
#End Region

End Class
 
Upvote 0 Downvote
Sorry I haven't had a chance to check out your code yet. I'll give it a go when I get some spare time. Are you still having issues?
 
Upvote 0 Downvote
Hi RiverGuy...

Thanks for all your help.. I've started working on the Report section for the Payment recieved module.. I figured that you may be busy.. When you have time..
 
Upvote 0 Downvote
I just tried it out. I can't really get the app to work without the entire project. It's reference an MDI parent, for example.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
FIND VALUE IN COLUMN 0 OF MSFKLEXGRID
Replies
0
Views
88
sal21
sal21
2009luca
  • Locked
  • Question
SORT msflexgrid column 1 with decimal number 1
Replies
4
Views
196
2009luca
2009luca
2009luca
  • Locked
  • Question
GET splitted value from node and sub node in treeview
Replies
1
Views
178
Andrzejek
Andrzejek
VisKataboman
  • Locked
  • Question
Linear Column Gauge measurement form on Visual Basic 6.0
Replies
1
Views
146
xwb
xwb
sal21
  • Locked
  • Question
GET VALUE FROM HREF IN WEB PAGE
Replies
3
Views
205
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top