How do i add a calculated column to a dataset?

  • Thread starter Thread starter Dan Keeley
  • Start date Start date
D

Dan Keeley

Hi,

I have a dataset which is used to populate my datagrid something like this:
My question is, how do I add a caluclated display column to the datagrid?
It will be based on ScoreCardScore...

Dim Cmd As OleDbCommand = New OleDbCommand("Select AuditDate,
ScoreCardScore, DetailsScore from Audits where SupplierID = " &
ComboItem.Value.ToString, conn)

' create the data adapter object and pass in the sql command object

myDataAdapter = New System.Data.OleDb.OleDbDataAdapter(Cmd)

' Tell the myDataadabter to fill the dataset

myDataAdapter.Fill(myDataSet, "Name")

dgAudits.DataSource = myDataSet.Tables("Name").DefaultView



Thanks very much for any advice!

Dan
 
I have not tried this, and I'm going out soon so I don't have time but . . .
..

One suggestion that you might like to try is to Add a total column before
filling the dataset. For the table containing the values to be calculated,
add a row_changed delegate. and simply put that value in the datagrid cell
each time a change is made.

Regards - OHM



Dan said:
Hi,

I have a dataset which is used to populate my datagrid something like
this: My question is, how do I add a caluclated display column to the
datagrid? It will be based on ScoreCardScore...

Dim Cmd As OleDbCommand = New OleDbCommand("Select AuditDate,
ScoreCardScore, DetailsScore from Audits where SupplierID = " &
ComboItem.Value.ToString, conn)

' create the data adapter object and pass in the sql command object

myDataAdapter = New System.Data.OleDb.OleDbDataAdapter(Cmd)

' Tell the myDataadabter to fill the dataset

myDataAdapter.Fill(myDataSet, "Name")

dgAudits.DataSource = myDataSet.Tables("Name").DefaultView



Thanks very much for any advice!

Dan
Click to expand...

Regards - OHM# OneHandedMan{at}BTInternet{dot}com
 
Another way of solving this problem (I'm not saying it's better :-), is to
use a custom column style in the datagrid:

Data binding and Web Services using Custom Collections
http://www.microsoft.com/belux/nl/msdn/community/columns/jtielens/webservicewrapper.mspx
The data binding capabilities in .NET are great, you can bind many controls
to almost any type of data, but in some scenarios data binding has its
limitations. For example data binding is not possible when using custom
collections, instead of DataSets, coming from a Web Service. This article
explains the problem and a possible, easy-to-use, solution: a class that
dynamically builds wrapper classes at run time, which exposes the field
member of the proxy classes as properties.
 
Untested.......

dstest.Tables(0).Columns.Add("total")
Dim i As Integer
For i = 0 To dstest.Tables(0).Rows.Count - 1
dstest.Tables(0).Rows(i).Item("total") =
dstest.Tables(0).Rows(i).Item("price") *
dstest.Tables(0).Rows(i).Item("qty")
Next

Let me know if this helps.........
 
When you bind to the dataset it will be there.

scorpion53061 said:
Untested.......

dstest.Tables(0).Columns.Add("total")
Dim i As Integer
For i = 0 To dstest.Tables(0).Rows.Count - 1
dstest.Tables(0).Rows(i).Item("total") =
dstest.Tables(0).Rows(i).Item("price") *
dstest.Tables(0).Rows(i).Item("qty")
Next

Let me know if this helps.........
Click to expand...
 
Dan
Have you tried something like:

Dim myTable As DataTable = myDataSet.Tables("Name")
Dim myColumn As New DataColumn("Calculated",
GetType(Decimal), "col1 * col2")
myColumn.AutoIncrement = False
myColumn.ReadOnly = True
myTable.Columns.Add(myColumn)

Sample adapted from:
http://msdn.microsoft.com/library/d.../frlrfSystemDataDataColumnClassctorTopic4.asp

See the topic for DataColumn.Expression on the expression syntax allowed.

Of course if you are pre-defining your DataSet with an xsd, you can use the
designer to add the calculated column.

Hope this helps
Jay
 
you were right Jay sorry.......

Jay B. Harlow said:
Dan
Have you tried something like:

Dim myTable As DataTable = myDataSet.Tables("Name")
Dim myColumn As New DataColumn("Calculated",
GetType(Decimal), "col1 * col2")
myColumn.AutoIncrement = False
myColumn.ReadOnly = True
myTable.Columns.Add(myColumn)

Sample adapted from:
http://msdn.microsoft.com/library/d.../frlrfSystemDataDataColumnClassctorTopic4.asp

See the topic for DataColumn.Expression on the expression syntax allowed.

Of course if you are pre-defining your DataSet with an xsd, you can use the
designer to add the calculated column.

Hope this helps
Jay
Click to expand...
 
excellent thats perfect!

I managed to do exactly what i wanted with an expression field

Cheers!
Dan
 
scorpion53061,
Don't be sorry, as sometimes you need to use the loop you demonstrated!

For example the expression you are attempting to calculate is too complex
for the ADO.NET expression syntax. (interest & financial calculations or
other custom functions).

Or you want to have explicit control over when the column is recalculated,
not have it recalculate automatically.

Note I can see using either the loop or the DataTable.ColumnChanging event
to handle complex expressions. If I was displaying the values in a Windows
Grid, I would probably use the event as the grid would then be updated as I
changed items. In ASP.NET or non-visual windows grids I would probably use
the loop...

For explicit control over when the recalculation occurs, the loop makes more
sense to me.

Hope this helps
Jay
 
Back
Top