[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 896
  • Last Modified:

Calculated field in datagridview

Below is part of a posting to the MS SQL Server site.  I know that most of you on this site use SQL Server for database connectivity.

"For those of you who may be familiar with the look of Quickbooks' check register where a check transaction has two lines associated with it and the grid has a running account of a balance field out beside each combined transaction, I am looking at designing a sql statement that gives me that capability."

In the event I am able to create a dataview with the rows I specify above, I want to incorporate into a datagridview that contains several editable fields....one of which is the check_amount where I can refresh the SQL statement above and recalculate the balance field.

Should I be able to do this with the default datagridview in VB 2005 or do I need to look for a 3rd party grid to make my life easier with this function.  Depending on this, I can incorporate this grid in about 7-8 other accounting forms/datagridviews for more flexibility and/or ease of use/design.

Sincerely!
Phil
0
TSFLLC
Asked:
TSFLLC
  • 8
  • 3
  • 2
1 Solution
 
VBRocksCommented:
I'm not sure how to do it with SQL, however with ADO.Net, you can loop through a DataTable and
manually calculate them:

        Dim dt As New DataTable()
        dt.Columns.Add("Debit")
        dt.Columns("Debit").DataType = GetType(Decimal)

        dt.Columns.Add("Credit")
        dt.Columns("Credit").DataType = GetType(Decimal)

        dt.Columns.Add("Total")
        dt.Columns("Total").DataType = GetType(Decimal)

        'Enter Beginning balance
        dt.Rows.Add(0, 100)
        dt.Rows.Add(10, 0)
        dt.Rows.Add(5, 0)
        dt.Rows.Add(2, 0)

        'Manually loop through rows and calculate
        Dim Balance As Decimal = 0
        For Each row As DataRow In dt.Rows
            Balance += (row.Item("Credit") - row.Item("Debit"))
            row.Item("Total") = Balance

        Next

        Me.DataGridView1.DataSource = dt

0
 
SQL_SERVER_DBACommented:
select credit-debit as Balance from <some_table>
0
 
VBRocksCommented:
You know, I thought about that, but won't that give you something like:

    Credit    Debit    Total
    100       0           100
    0           5           -5
    0           10         -10

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SQL_SERVER_DBACommented:
THE ANSWER IS YES, THIS WILL FORMAT IT TO 2 DECIMAL PLACES
SQL_SERVER_DBA:
select ROUND(CAST(credit-debit AS REAL), 2) as Balance from <some_table>
0
 
VBRocksCommented:
No, I'm not referring to the formatting, I'm referring to a running balance verses a total.


0
 
TSFLLCAuthor Commented:
RE:  select ROUND(CAST(credit-debit AS REAL), 2) as Balance from <some_table>

The only problem with this is that the Balance field is not based on a calculation of a credit and a debit.  Althought, check_amount IS a credit to an account, the original balance of the account and all of the amounts of the checks applied against the checking account have to be taken into consideration.  I have to start from the opening_balance of the account and the running balance beside each row is what I'm looking for.

Tthe changes below to what VBRocks has designed will work, but I was hoping to do it totally through the SQL statement especially if I may have 500-600 checks applied against account.

dt.Columns.Add("AccBalance")
dt.Columns("AccBalance").DataType = GetType(Decimal)

Dim Balance As Decimal = dvCheckingAccount(0)("original_balance")
For Each row As DataRow In dt.Rows
     Balance += (row.Item("Credit"))
     row.Item("AccBalance") = Balance
Next


0
 
TSFLLCAuthor Commented:
Actually I misspoke about Credit - Debit because I also need to take in consideration the Debit field because of deposits into the account...........which would additionally be a line item in the check_header table.
0
 
VBRocksCommented:
Well, in ADO.NET, you can loop through 500 rows in a few milliseconds, so that's not really a big
performance hit.

0
 
VBRocksCommented:
Here's an example, 22 milliseconds for 1000 rows:

        Dim dt As New DataTable()
        dt.Columns.Add("Debit")
        dt.Columns("Debit").DataType = GetType(Decimal)

        dt.Columns.Add("Credit")
        dt.Columns("Credit").DataType = GetType(Decimal)

        dt.Columns.Add("Total")
        dt.Columns("Total").DataType = GetType(Decimal)

        Dim sw As New Stopwatch()
        sw.Start()

        Randomize()

        Dim value As Int32

        For i As Int16 = 1 To 1000

            'Select random values for debit ($1 to $10)
            value = CInt(Int(10 * Microsoft.VisualBasic.Rnd() + 1))

            dt.Rows.Add(value, 0)

        Next

        'Set to beginning balance
        Dim Balance As Decimal = 150000

        'Manually loop through rows and calculate
        For Each row As DataRow In dt.Rows
            Balance += (row.Item("Credit") - row.Item("Debit"))
            row.Item("Total") = Balance

        Next

        'Me.DataGridView1.DataSource = dt

        sw.Stop()
        MsgBox("Time:  " & sw.ElapsedMilliseconds)

0
 
VBRocksCommented:
My mistake, less than 5 milliseconds if you don't set the datasource to the DataGridView.

0
 
TSFLLCAuthor Commented:
You must be kidding!  This will be easy to test.

VBRocks....I haven't forgotten about your postings on 'Right Click on gridview' from last week.  I think I was premature in posting it based on priorities.

Thanks!

0
 
VBRocksCommented:
You're welcome!

The last code example I posted uses a StopWatch to time it.  Just copy and paste the code, then run
it and you'll see what I mean.

0
 
VBRocksCommented:
Oops, that time actually includes Loading the dataset AND calculating.  If you move sw.Start() to just
above the calculating loop, and stop it just after, you'll find it returns around 2 ms.

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now