How do i add a calculated column to a dataset?

[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

[One Handed Man [ OHM# ]]

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 Keeley wrote:

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

Regards - OHM# OneHandedMan{at}BTInternet{dot}com

[Jan Tielens]

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/ms...cewrapper.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.

--
Greetz,
Jan
__________________________________
Read my weblog: http://weblogs.asp.net/jan
"Dan Keeley" <ma********@hotmail.com> schreef in bericht
news:jb****************@newsfep4-winn.server.ntli.net...

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

[scorpion53061]

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

"Dan Keeley" <ma********@hotmail.com> wrote in message
news:jb****************@newsfep4-winn.server.ntli.net...

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

[scorpion53061]

When you bind to the dataset it will be there.

"scorpion53061" <sc****************************@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

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

"Dan Keeley" <ma********@hotmail.com> wrote in message
news:jb****************@newsfep4-winn.server.ntli.net...

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

[Jay B. Harlow [MVP - Outlook]]

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/de...ctorTopic4.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

"Dan Keeley" <ma********@hotmail.com> wrote in message
news:jb****************@newsfep4-winn.server.ntli.net...

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

[scorpion53061]

you were right Jay sorry.......

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

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/de...ctorTopic4.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

"Dan Keeley" <ma********@hotmail.com> wrote in message
news:jb****************@newsfep4-winn.server.ntli.net...

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

[Dan Keeley]

excellent thats perfect!

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

Cheers!
Dan
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

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/de...ctorTopic4.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

"Dan Keeley" <ma********@hotmail.com> wrote in message
news:jb****************@newsfep4-winn.server.ntli.net...

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

[Jay B. Harlow [MVP - Outlook]]

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

"scorpion53061" <sc****************************@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

you were right Jay sorry.......

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

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/de...ctorTopic4.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

"Dan Keeley" <ma********@hotmail.com> wrote in message
news:jb****************@newsfep4-winn.server.ntli.net...

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