adCurrency problem in ASP

[Jonathan Dodds]

I'm using ADO from an ASP 3.0 page written in JScript to query a database
table in SQL Server 2000. One of the columns in a currency field.

Instead of retrieving a value of 1095.60 like I expect, I'm getting
1095.6000000000001 instead.

If I use other tools like Query Analyzer to run my select the value comes
out as I expect.

The type of the ADO field in my recordset is adCurrency as I expect.

Here's a short example of code that demonstrates the problem:

var strSQL = "select ItemPrice from dbo.item where ItemID = 5";
var oConn = Server.CreateObject("ADODB.Connection");
oConn.Open(getDBConnectionString());
var rs = oConn.Execute(strSQL);
while (!rs.EOF)
{
Response.Write(rs("ItemPrice").Value + "<br>");
rs.MoveNext();
}
rs.close();
oConn.close();


Am I doing something wrong here?

My DSN shows
Microsoft SQL Server ODBC Driver Version 03.85.1025

Is there a driver problem?

 

[Evertjan.]

Jonathan Dodds wrote on 27 mrt 2005 in
microsoft.public.inetserver.asp.general:

I'm using ADO from an ASP 3.0 page written in JScript to query a database
table in SQL Server 2000. One of the columns in a currency field.

Instead of retrieving a value of 1095.60 like I expect, I'm getting
1095.6000000000001 instead.
[..]

Am I doing something wrong here?

Yes you do.

What is wrong is your expectation.

Non integer values are usually stored in a binary format.
Just like 1/3 cannot be exactly stored in a decimal format,
many decimal fractions cannot be stored in a binary form,
without an error in the least significant bit(s).
The back conversion to decimal will not always result in the same
error nullified.

So if you want to represent a numberic value with a fixed number of
decimals, either store it as a integer [in cents, if we are talking
currency] or have a good rounding off algoritm.

btw: Some Basic interpreters build by Bill Gates around 1984
used BCD [binary coded decimal] as a standard number coding method.
There your problem would not arise. VBscript and Jscript do not use BCD.

 

[Jonathan Dodds]

Jonathan Dodds wrote on 27 mrt 2005 in
microsoft.public.inetserver.asp.general:

I'm using ADO from an ASP 3.0 page written in JScript to query a database
table in SQL Server 2000. One of the columns in a currency field.

Instead of retrieving a value of 1095.60 like I expect, I'm getting
1095.6000000000001 instead.
[..]

Am I doing something wrong here?

Yes you do.

What is wrong is your expectation.

Non integer values are usually stored in a binary format.
Just like 1/3 cannot be exactly stored in a decimal format,
many decimal fractions cannot be stored in a binary form,
without an error in the least significant bit(s).
The back conversion to decimal will not always result in the same
error nullified.

So if you want to represent a numberic value with a fixed number of
decimals, either store it as a integer [in cents, if we are talking
currency] or have a good rounding off algoritm.

btw: Some Basic interpreters build by Bill Gates around 1984
used BCD [binary coded decimal] as a standard number coding method.
There your problem would not arise. VBscript and Jscript do not use BCD.

Why doesn't Query Analyzer display the decimal error? Is it rounding behind
my back?

 

[Evertjan.]

Jonathan Dodds wrote on 27 mrt 2005 in
microsoft.public.inetserver.asp.general:

Why doesn't Query Analyzer display the decimal error? Is it rounding
behind my back?

I don't know what you mean by "Query Analyzer".

However there is no error, just wrong expectations on your side.

No, no rounding is involved, unless you specify that with your code.

Yes, a value is only put into memory to the maximum accuracy of the
floating binary specified.

 

[Bob Lehmann]

Why doesn't Query Analyzer display the decimal error?
Because, QA is aware of the number of decimals (scale) you specified in the
datatype for the column.

http://www.mikeindustries.com/blog/archive/2004/08/apple-calculator

Or, if you want to make your brain bleed...
http://docs.sun.com/source/806-3568/ncg_goldberg.html


Bob Lehmann

Jonathan Dodds wrote on 27 mrt 2005 in
microsoft.public.inetserver.asp.general:

I'm using ADO from an ASP 3.0 page written in JScript to query a database
table in SQL Server 2000. One of the columns in a currency field.

Instead of retrieving a value of 1095.60 like I expect, I'm getting
1095.6000000000001 instead.
[..]

Am I doing something wrong here?

Yes you do.

What is wrong is your expectation.

Non integer values are usually stored in a binary format.
Just like 1/3 cannot be exactly stored in a decimal format,
many decimal fractions cannot be stored in a binary form,
without an error in the least significant bit(s).
The back conversion to decimal will not always result in the same
error nullified.

So if you want to represent a numberic value with a fixed number of
decimals, either store it as a integer [in cents, if we are talking
currency] or have a good rounding off algoritm.

btw: Some Basic interpreters build by Bill Gates around 1984
used BCD [binary coded decimal] as a standard number coding method.
There your problem would not arise. VBscript and Jscript do not use BCD.

Why doesn't Query Analyzer display the decimal error? Is it rounding behind
my back?

 

[Jonathan Dodds]

Aha. Thank you.

Why doesn't Query Analyzer display the decimal error?

Because, QA is aware of the number of decimals (scale) you specified in the
datatype for the column.

http://www.mikeindustries.com/blog/archive/2004/08/apple-calculator

Or, if you want to make your brain bleed...
http://docs.sun.com/source/806-3568/ncg_goldberg.html


Bob Lehmann

Jonathan Dodds wrote on 27 mrt 2005 in
microsoft.public.inetserver.asp.general:

I'm using ADO from an ASP 3.0 page written in JScript to query a database
table in SQL Server 2000. One of the columns in a currency field.

Instead of retrieving a value of 1095.60 like I expect, I'm getting
1095.6000000000001 instead.

[..]

Am I doing something wrong here?

Yes you do.

What is wrong is your expectation.

Non integer values are usually stored in a binary format.
Just like 1/3 cannot be exactly stored in a decimal format,
many decimal fractions cannot be stored in a binary form,
without an error in the least significant bit(s).
The back conversion to decimal will not always result in the same
error nullified.

So if you want to represent a numberic value with a fixed number of
decimals, either store it as a integer [in cents, if we are talking
currency] or have a good rounding off algoritm.

btw: Some Basic interpreters build by Bill Gates around 1984
used BCD [binary coded decimal] as a standard number coding method.
There your problem would not arise. VBscript and Jscript do not use BC D.

Why doesn't Query Analyzer display the decimal error? Is it rounding behind
my back?