#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    2
    Rep Power
    0

    ADO decimal issue


    Hi,

    I'm using ADO connection via ODBC to connect to Sybase DB.
    When i'm doing a query via stored procedure, i'll get a decimal value
    like '1500.00'
    But in my program i can only get the value '15', meaning the two '0'
    at the back is somehow truncated.
    I've tried a simple sql in the stored procedure like
    SELECT 1500.00

    just for testing purpose.
    And i get the same result of '15'* (from
    mobjSaiisTaksirRst.Fields.Item(0))

    My sample code as below

    Private mobjSaiisTaksirRst As ADODB.Recordset
    Private mobjSaiisTaksirConn As ADODB.Connection
    Private mobjSaiisTaksirCmd As ADODB.Command

    Dim dblJumlahSewa As Double

    With mobjSaiisTaksirCmd
    * * * * * * .ActiveConnection = mobjSaiisTaksirConn
    * * * * * * .CommandText = mstrSewaInfoProc
    * * * * * * .CommandType = adCmdStoredProc
    * * * * * * .Parameters.Append .CreateParameter("no_akaun", adVarChar,
    adParamInput, 20, strAccount)
    * * * * End With

    * * * * Set mobjSaiisTaksirRst = mobjSaiisTaksirCmd.Execute

    If Not mobjSaiisTaksirRst.EOF Then
    * * * dblJumlahSewa = IIf(IsNull(mobjSaiisTaksirRst.Fields.Item(0)),
    0, mobjSaiisTaksirRst.Fields.Item(0))
    * ***End If

    Do you have any idea what is wrong ?
    Thanks.

    regards,
    Tan
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    You're treating numeric data as text data.

    1500 is the value

    That's the same as 1500.0
    The same as 1500.00
    The same as 1500.000000000000

    Decide if you're dealing with numbers or text. I think you *should* be treating it as numbers, but you can't do a LIKE select on numbers.

    If you must, which you should not, but ... if you must then use Format to get back to text from a numeric field such as:

    SELECT * FROM MyTable WHERE Format([MyNumericField],"0.00") Like "1500.0*";

    You can use BETWEEN to select a range of numeric values instead:

    SELECT * FROM MyTable WHERE [MyNumericField] BETWEEN 1500 and 1500.999;
    Last edited by medialint; October 4th, 2011 at 05:38 PM.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    2
    Rep Power
    0
    Hi medialint,

    I think you got me wrong
    now supposed i run an sql
    SELECT 1500.00

    in my program i declared

    Dim dblJumlahSewa As Double

    dblJumlahSewa = mobjSaiisTaksirRst.Fields.Item(0)

    now the value of mobjSaiisTaksirRst.Fields.Item(0) is supposed to be 1500.00 , but what i got is 15
    which is strange



    Originally Posted by medialint
    You're treating numeric data as text data.

    1500 is the value

    That's the same as 1500.0
    The same as 1500.00
    The same as 1500.000000000000

    Decide if you're dealing with numbers or text. I think you *should* be treating it as numbers, but you can't do a LIKE select on numbers.

    If you must, which you should not, but ... if you must then use Format to get back to text from a numeric field such as:

    SELECT * FROM MyTable WHERE Format([MyNumericField],"0.00") Like "1500.0*";

    You can use BETWEEN to select a range of numeric values instead:

    SELECT * FROM MyTable WHERE [MyNumericField] BETWEEN 1500 and 1500.999;
  6. #4
  7. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Well that just seems broken then. I'd test your queries in a dedicated query application (I use TOAD for Oracle but I don't use Sybase) to see what results you're getting. Or for that matter if you have MS Access make a connection there and try to run the queries and see what's happening. It's really hard for me to deconstruct your code, but I'd definitely get down to the primary SQL statement and test that. I suspect that will return data correctly, then it's a matter of how you're treating it once you get it back.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss

IMN logo majestic logo threadwatch logo seochat tools logo