Routine for calculating running balance

Question

0.00/5 (No votes)

See more:

Hello friends,

I have datagridview that has three bound columns(AcctNo, Debit, Credit) and one unbound column(Running Balance). The Running Balance column is calculated field and displays running balance for the transactions.

My problem is that I cannot figure out an expression to calculate the running balance.

Any assistance would be greatly appreciated.

Thanks

Posted 8-May-13 7:33am

noblepaulaziz

Add a Solution

Comments

Maciej Los 8-May-13 14:33pm

What have you done so far?

2 solutions

Solution 1

As far as I know, you can't get a DataGridView to calculate running totals via the DataColumn.Expression property - since I don't know of any way to access other rows in the expression, just the columns of the current row.

There is a way to do it, however, which is to use teh DataGridView VirtualMode and not databind your DGV at all. When you select VirtualMode, you are saying that you want to control the data, and you supply it on a cell-by-cell basis on demand via the CellValueNeeded event. Since you supply the cell values, you supply them direct from your datatable for most of the columns, and calculate the value for the running total using values from previous rows in the DT.

If you think about it, it does make some sense - if the table is user sorted or filtered, then should the running total change? Or not? This allows you to make that decision.

It's not trivial to do, but MSDN does have a walkthough: http://msdn.microsoft.com/en-us/library/15a31akc.aspx[^]

Posted 8-May-13 9:00am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TnTinMn · Accepted Answer · 2013-05-08T19:16:00

This may not be the best method, but it works.

Public Class Form1

   Private dt As New DataTable()
   Private dv As DataView
   Private indexRT As Int32
   Private indexColumnToRT As Int32

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      With dt
         ' make some data
         .Columns.Add("SomethingToTotal", GetType(Double))
         .Columns.Add("SomethingElse", GetType(String))
         .Rows.Add(New Object() {1.0#, "fred"})
         .Rows.Add(New Object() {12.0#, "barney"})
         .Rows.Add(New Object() {1.0#, "wilma"})
         .Rows.Add(New Object() {50.0#, "betty"})
         .Rows.Add(New Object() {33.0#, "pebbles"})

         .Columns.Add("RunningTotal", GetType(Double)) ' If your datatable is filled from a DB
                                                       ' then add this column after filling it
         indexRT = .Columns("RunningTotal").Ordinal
         indexColumnToRT = .Columns("SomethingToTotal").Ordinal
      End With

      dv = dt.DefaultView 'set the dataview used

      ' force the 1st computaion of the running total
      dv_ListChanged(Nothing, New System.ComponentModel.ListChangedEventArgs(System.ComponentModel.ListChangedType.Reset, 0))

      DataGridView1.DataSource = dv
      DataGridView1.Columns("RunningTotal").DisplayIndex = 1
   End Sub

   Private Sub dv_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs)
      If e.ListChangedType = System.ComponentModel.ListChangedType.ItemChanged AndAlso _
         e.PropertyDescriptor IsNot Nothing AndAlso _
         e.PropertyDescriptor.Name <> "SomethingToTotal" Then 'don't process
         Exit Sub
      End If
      ' since we will be changing a item in the list, decouple event
      RemoveHandler dt.DefaultView.ListChanged, AddressOf dv_ListChanged

      Dim rt As Double = 0 ' initialize running total

      For Each drv As DataRowView In dv

         If drv(indexColumnToRT) IsNot DBNull.Value Then ' make sure it is not DbNull
            rt += CDbl(drv(indexColumnToRT))
         End If

         drv(indexRT) = rt
      Next

      AddHandler dt.DefaultView.ListChanged, AddressOf dv_ListChanged ' rewire the event
   End Sub

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      dv.RowFilter = "[SomethingElse]<='e'" 'set some filter to show that that will work as well
   End Sub

   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
      dv.Sort = "[SomethingElse] Asc"
   End Sub
End Class