• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

DataGrids and Calculations

Here is a tough one.  I have some data that is going into a datagrid via DATASETS.  I need to Grab 3 totals.
The final total I already have using this code.
        Dim x
        If DsOtherInc1.tblotherinc.Rows.Count > 0 Then
            x = dgotherinc.DataSource.compute("SUM([Brw Amount])", 1 = 1)
            txtTTLOthInc.Text = x
        End If
The other 2 totals are based off a different colum.
If colum "B" = Include Sum Column "C" of Includes only
If colum "B" = Exclude Sum Column "C" of Excludes only.

Example of what I am trying to say.

A               b                   c
File1        Include           10.00
File2        Include           20.00
File3       Exclude           10.00

Total 1 = 40.00
Total Include = 30.00
Total Exclude = 10.00

  • 2
1 Solution
You use a similar method to your existing code, but with a filter on the column containing the "Include/Exclude" value.  On these lines

            y = dgotherinc.DataSource.compute("SUM([Brw Amount])", "Colb = 'Include'")


            z = dgotherinc.DataSource.compute("SUM([Brw Amount])", "Colb = 'Exclude'")

danormeAuthor Commented:
Well, I am close, but not there yet.  Here is my code and here is the error?

        If DsDebts1.tbldebts.Rows.Count > 0 Then
            varCalcInclude = dgDebts.DataSource.compute("Sum([monthpay])", "[action]='Include'")
            varCalcExclude = dgDebts.DataSource.compute("Sum([monthpay])", "[action]='Exclude'")
            txtInclude.Text = varCalcInclude
            txtExclude.Text = varCalcExclude
            txtDebtTtl.Text = varCalcInclude + varCalcExclude
        End If

An unhandled exception of type 'System.Data.DataException' occurred in microsoft.visualbasic.dll

Additional information: Invalid usage of aggregate function Sum() and Type: String.
What that error message appears to be saying is that the [monthpay] column on which you are trying to use the Sum() function is of String datatype rather than a Numeric datatype.  Does that ring any bells with you?


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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