We help IT Professionals succeed at work.

Calculated field in datagridview

TSFLLC
TSFLLC asked
on
991 Views
Last Modified: 2008-01-09
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
Comment
Watch Question

Top Expert 2007

Commented:
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

select credit-debit as Balance from <some_table>
Top Expert 2007

Commented:
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

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>
Top Expert 2007

Commented:
No, I'm not referring to the formatting, I'm referring to a running balance verses a total.


Author

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


Author

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.
Top Expert 2007

Commented:
Well, in ADO.NET, you can loop through 500 rows in a few milliseconds, so that's not really a big
performance hit.

Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2007

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

Author

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!

Top Expert 2007

Commented:
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.

Top Expert 2007

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.