Decimal Rounding in DataTable.

[danyeungw]

I want to round the column in a DataTable to two decimals. Please refer to the following code. dsBarChart is the DataSet that contains columns Development, Research, and Sustaining.

Dim dtGrid As New DataTable
dtGrid = dsBarChart.Tables(0)

' Create column for the datatable
Dim dtDevelop As New DataColumn("% Development", GetType(Decimal))

dtDevelop.Expression = "Development / (Development + Research + Sustaining)"

' Add the columns to the datatable
dtGrid.Columns.Add(dtDevelop)

I tried
dtDevelop.Expression = "Decimal.Round(Development / (Development + Research + Sustaining), 2)" and received a runtime error "The expression contains undefined function call Decimal.Round()."

I tried dtGrid.Columns.Add(Decimal.Round(dtDevelop, 2)) and received an error "Value of type 'System.Data.DataColumn' cannot be converted to 'Decimal'.)

Where can I round the number to two decimals? Thanks.

DanYeung

 

[fpineda101]

You did not mention which DB you are using. I will assume SQL Server.

You try searching msdn.com for SQL Functions. I did that and came up with this...

Round(numeric_value_here, 2)
The two is the number of decimals places you want.

 

[danyeungw]

I used round. It is not vb.net command. Yes, I use SQL Server, but the code should be VB code. Thanks.

DanYeung

 

[vis781]

danyeungw, can you set your primary platform please. it helps when people are trying to give a correct response to your query. If your using .net 2.0 then you can try

Decimal.Round(..)

 

[danyeungw]

My primary platform is .net 1.1. I tried Decimal.Round() and didn't work. Please refer the code above. Thanks.

DanYeung

p.s. How do I edit my profile?

 

[TechGnome]

Decimal.Round is not going to work... ever.... That's VB.NET command, what is needed is the DBMS equivelent which is "Round" .... no Decimal. ... no Maths. .... jsut "Round".

-tg

 

[vis781]

Originally Posted By TechGnome
Decimal.Round is not going to work... ever.... That's VB.NET command, what is needed is the DBMS equivelent which is "Round" .... no Decimal. ... no Maths. .... jsut "Round".

I tried dtGrid.Columns.Add(Decimal.Round(dtDevelop, 2)) and received an error "Value of type 'System.Data.DataColumn' cannot be converted to 'Decimal'.)

If he is trying to round a value in a system.data.datacolumn then why would he not need a VB.Net Command?

 

[danyeungw]

I tried
dtDevelop.Expression = "(Round(Development / (Development + Research + Sustaining)) * 100, 2)"
and received a runtime error "The expression contains undefined function call Round()."

I also tried dtGrid.Columns.Add(Round(dtDevelop, 2)) and received an error "Round is not declared."

Can you please give an example of the syntex. Thanks.

DanYeung

 

[TechGnome]

He's setting an expression:

"dtDevelop.Expression = "

What does the Help files say about the .Expression property anyways?

-tg

 

[danyeungw]

I didn't find it in Help. I searched on the web and have not found anything.

DanYeung

 

[TechGnome]

wasn't that hard:
MSDN:
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

Used DataColumn.Expression as the search....

It even includes a list of commands that you can use....

-tg

 

[danyeungw]

The link shows how to use .expression. My example in the first post uses expression to do calculation. The result is correct, but I don't know how to round the number. I didn't find rounding from the link. Did you?

Thanks.
DanYeung

 

[TechGnome]

That was kinda my point.... the link I provided shows the functions/commands/etc that you CAN use in the Expression property.... please note that Round was NOT one of the options..... so.... the logical conclusion would be that you can't use Round.... which would then explain the error message.....

-tg

 

[danyeungw]

That is why I am looking for a way to round the numbers.

DanYeung

 

[TechGnome]

What database system are you using"? Access? SQL Server? Oracle? Orajel? MySQL?

Two options:
1) Dump the expression and write a function that takes a datrow, extracts the fields you need, do the calculation, then update the data row by setting the field back to the result
Con: the field will not auto update when one of the other fields updates.
Pro: the calculation can be put off until all values have been updated; ie set the fields, then call the calc.

2) Do it in the SQL.
Con: It's only going to work the first time when the SQL is first run.
Pro: It's only done once and if you don't need to update the fields further, is the fastest way to do it.

-tg