adCurrency problem in ASP

Discussion in 'ASP General' started by Jonathan Dodds, Mar 26, 2005.

  1. 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?
    Jonathan Dodds, Mar 26, 2005
    #1
    1. Advertising

  2. Jonathan Dodds

    Evertjan. Guest

    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.

    --
    Evertjan.
    The Netherlands.
    (Replace all crosses with dots in my emailaddress)
    Evertjan., Mar 27, 2005
    #2
    1. Advertising

  3. "Evertjan." <> wrote in message
    news:Xns9626842FB84E3eejj99@194.109.133.29...
    > 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.
    >
    > --
    > Evertjan.
    > The Netherlands.
    > (Replace all crosses with dots in my emailaddress)
    >


    Why doesn't Query Analyzer display the decimal error? Is it rounding behind
    my back?
    Jonathan Dodds, Mar 27, 2005
    #3
  4. Jonathan Dodds

    Evertjan. Guest

    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.

    --
    Evertjan.
    The Netherlands.
    (Replace all crosses with dots in my emailaddress)
    Evertjan., Mar 27, 2005
    #4
  5. Jonathan Dodds

    Bob Lehmann Guest

    > 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" <NO_REPLY> wrote in message
    news:...
    >
    > "Evertjan." <> wrote in message
    > news:Xns9626842FB84E3eejj99@194.109.133.29...
    > > 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.
    > >
    > > --
    > > Evertjan.
    > > The Netherlands.
    > > (Replace all crosses with dots in my emailaddress)
    > >

    >
    > Why doesn't Query Analyzer display the decimal error? Is it rounding

    behind
    > my back?
    >
    >
    Bob Lehmann, Mar 27, 2005
    #5
  6. Aha. Thank you.

    "Bob Lehmann" <> wrote in message
    news:...
    > > 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" <NO_REPLY> wrote in message
    > news:...
    > >
    > > "Evertjan." <> wrote in message
    > > news:Xns9626842FB84E3eejj99@194.109.133.29...
    > > > 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.
    > > >
    > > > --
    > > > Evertjan.
    > > > The Netherlands.
    > > > (Replace all crosses with dots in my emailaddress)
    > > >

    > >
    > > Why doesn't Query Analyzer display the decimal error? Is it rounding

    > behind
    > > my back?
    > >
    > >

    >
    >
    Jonathan Dodds, Mar 27, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ben
    Replies:
    3
    Views:
    1,114
  2. Andrea Raimondi
    Replies:
    1
    Views:
    434
  3. R.A.M.
    Replies:
    1
    Views:
    3,224
    =?Utf-8?B?Y2xpY2tvbg==?=
    Mar 29, 2006
  4. Daves
    Replies:
    2
    Views:
    721
    Patrice
    May 31, 2006
  5. David A. Beck
    Replies:
    10
    Views:
    991
    Scott McNair
    Apr 13, 2004
Loading...

Share This Page