Calculate Totals from Dataset

[tommyready]

I have a dataset loaded with sales totals and I was wondering if its possible to calculate those values into like a grand totals and display in a text box on my form. Id like to use the dataset and not have to requery the csv file where the data comes from to sum the rows. Thanks for any help.

 

[tommyready]

Ok,

I figured out the method I should use but of course it has snags as well.

Dim GrandTotal As Integer = CType(ds.Tables(0).Compute("SUM([TX-Allowed Amount])", ""), Integer)

It tells me it cant find column "TX-Allowed Amount"

Here is the code I use to fill the dataset

        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\;Extended Properties=""Text;HDR=Yes;FMT=Delimited""")

        Dim da As New OleDbDataAdapter()

        Dim ds As New DataSet()

        Dim cd As New OleDbCommand(sqlString, cn)

        sqlViewer.Text = sqlString ' Display SQL

        cn.Open()
        da.SelectCommand = cd
        ds.Clear()
        da.Fill(ds, "CSV")

        dg.DataSource = ds.Tables(0)
        cn.Close()

Thanks for any feedback

 

[cjard]

So what are the columns called?

I'd have avoided column names with hyphens, spaces etc. Rewrite your query so that it's called TxAllowedAmount - you can always rename the header label of a grid, you don't have to make the column name bear the display name

You might have better luck using typed datasets rather than untyped ones. A typed one has columns and their datatypes defined in advance. You could then define a column called Total as having an Expression of SUM([TXAllowedAMount]) and every row in your table would have this same total value. you can then databind the column to a textbox and because every row has the same value, when you change rows the value in the text box does not appear to alter

 

[tommyready]

Hey,

Thanks for the reply. I finally got it to work before. This CSV file that I am querying is a canned data exported from some medical software. So some of the fields names have extra space.

So I had to do this:

Dim GrandTotal As Integer = CType(ds.Tables("CSV").Compute("SUM( [TX-Allowed Amount ] )", ""), Integer)

That works fine but if I try and do it with a filter I cannot get it to work:

Dim DiabeticTotal As Integer = CType(ds.Tables("CSV").Compute("SUM( [TX-Allowed Amount ] )", "[IT-Group] = 'DB' AND [IT-Group] = 'DM'"), Integer)

The error I get is something about casting a DBNull as an Integer.

 

[cjard]

One or more rows in your table has a Null in the TXAllowed Amount column

 

[tommyready]

So how can I skip the rows with NULLs?

 

[cjard]

Filter them out? See ISNULL in DataColumn.Expression Property (System.Data) or jsut uise an Expression column