Filled dataset decimal value not same as fetched value?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I query my Oracle database for a decimal value, I explicitly specify a
ROUND to one decimal place. When the value is filled into the dataset, the
value is stored with two decimal places. For instance, if the value I am
querying (and rounding in the SQL statement itself) is 8.5, the actual value
in the dataset is 8.50.

Now, I'm not going to argue that 8.5 is the same value as 8.50, but when
that 8.50 value is displayed in a grid... it is displayed as 8.50. This is
not good when I have validation code checking to make sure then user didn't
enter a value with more than one decimal point.

Note, I am not looking for a workaround to this issue. I would like to solve
the problem at the source. I would like the value filled in the dataset to be
the value retrieved from the database.

What is really odd is that if the user types in 8.5 directly, the dataset
stores the value as 8.5. It isn't until the value is retrieved back from the
database that it gets stored as 8.50.
 
Hi Tom,

I believe rounding and truncating are totally different things. Even if you
rounded number, it does not mean zeros will be truncated from the end. I do
not know what Oracle has to manipulate datatypes, but if it has possibility
to convert to decimal value with different precision, then this is what you
need to remove this zero
 
Hi Val,

I believe you misunderstood my problem. Maybe this will help. I am using
Microsoft's .NET Data Provider for Oracle. I instantiated a dataset, then I
executed the following query:

"SELECT 8.1 as NUM1, 8.12 as NUM2, 8.123 as NUM3, 8.1234 as NUM4, 8.12345 as
NUM5, 8.123456 as NUM6, 8.1234567 as NUM7, 8.12345678 as NUM8 FROM DUAL"

I filled my dataset with the query above. Then, I created a new DataRow
object for the dataset above and manually added the follow values to the new
datarow.

8.1
8.12
8.123
8.1234
8.12345
8.123456
8.1234567
8.12345678

Then I added the new row to the dataset.

Finally I iterated through the ItemArray of each datarow in the dataset,
getting each value and sending the output to the debug window.

It's quite astonishing actually. You should try it. What I end up with is
the following:

For the values filled from the query, all values with a odd number of
decimal places get an extra zero padded to them. The values with an even
number of decimal places are left alone.

For the values I entered manually, they are just as I would expect them to
be... the way I entered them.

So, you see, the problem isn't as cut and dry as rounding and/or
truncating... I would have been able to solve that on my own.

Any suggestions?
 
Tom,
I don't think that this is the actual Decimal value but most likely
something in the ToString() calls for the value. Have you applied a special
format to the column to only show the desired # of decimal places? I can
think of a way to do it in my code (with a custom editor for the column) but
I always use a 3rd party grid which has some extra functionality for this.

Ron Allen
 
Hey Ron!

Well, let's face it... 8.1 is equal to 8.10, or for that matter
8.10000000000000000. So, yes, the actual decimal value has not been
compromised, per se. However, (assuming the filled values are identical to
the manually inserted values) shouldn't the ToString method return the same
text string for two decimal values that are identical?

I use a 3rd party datagrid too, and I can probably get around this issue by
setting an input mask... but our users find the input mask for this 3rd party
control to be difficult to use at best.

Basically, we have a display format set for the cell of the grid which works
fine when the cell isn't being edited. Once the user enters the cell to edit
the value, values filled by a query shows up with 2 decimal places... while
values the user entered but didn't commit show up with one decimal place (the
way we want them to be initially displayed for editing). The cells are wide
open as far as data entry. The user can enter what ever they like. Upon
leaving the cell, we validate the entry. Part of the validation specifies
that the value can't extend beyond one decimal place. Just by the user
navigating through the cell, the validation fires and the query filled value
8.50 fails validation even though the user effectively did nothing except
navigate through the cells.

It is something I can work around, and it sounds like I'm going to need to
do exactly that. I just wanted to make someone at MS aware of a potential
problem. Whether the problem resides in the dataset itself or the ToString
method... there is something wrong somewhere.
 
Back
Top