Decimal Rounding in DataTable.

danyeungw

Well-known member
Joined
Aug 30, 2005
Messages
73
Programming Experience
10+
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
 
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, 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


VB.NET:
Decimal.Round(..)
 
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?
 
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
 
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?
 
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
 
He's setting an expression:

"dtDevelop.Expression = "

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

-tg
 
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
 
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
 
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
 
Back
Top