Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Location
    Singapore
    Posts
    4

    Question Unanswered: Arithmetic Overflow - VB6.0/Stored Proc - Application

    Dear All,

    I am a Singapore based ASP/VB developer and need your advise for a problem i am facing in my VB/SQLServer/Stored Procedure application.

    When i executed a stored procedure(SQLServer 6.5) from VB(6.0) application, I had error messages, from ADO error collection, which are "Arithmetic overflow error converting numeric to data type numeric." followed by "The statement has been terminated.". This is the exact message(s) i am getting.

    The commands i executed are :

    1.
    update live_stock
    set
    stock_value = convert(numeric(9,3), convert(numeric(28,3) , "2593500")/1 ),
    day = convert(Char(3), datename(dw, getdate())) ,
    stock_timestamp = getdate()
    where
    stock_code="C27 " and
    market =

    Pls note that the database data type of "stock_value" column is "int".


    2.
    update live_stock
    set
    stock_volume = convert(numeric(9,3), convert(numeric(28,3) , "1017000")/1 ),
    day = convert(Char(3), datename(dw, getdate())) , stock_timestamp = getdate()
    where
    stock_code="C27 " and
    market =


    Pls note that the database data type of "stock_volume" column is "numeric](11, 2)".


    Need you advise/help as this is taking up lot of my time.

    Thanks and regards,
    Muru
    Singapore

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i know nothing of asp or vb, so maybe the reasons why you are doing it that way escape me

    if i were doing it, i would write

    set stock_value = 2593500


    rudy

  3. #3
    Join Date
    Nov 2002
    Location
    Singapore
    Posts
    4
    Thanks for the reply.

    For simplicity, i have mentioned the expression like "2593500"/1. Actually, this 1 is a parameter - could be 1 or 2 etc...

    Any suggestion?

    Regards,
    Muru

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, try not putting quotes around your numerics

    e.g. set stock_value = 2593500 / 2

    rudy

  5. #5
    Join Date
    Nov 2002
    Location
    Singapore
    Posts
    4
    Originally posted by r937
    okay, try not putting quotes around your numerics

    e.g. set stock_value = 2593500 / 2

    rudy
    2593500 is also a parameter of string and is being passed from VB. My backend VB application is updating the database only and this program is an ugraded version of existing version. Currently, there are ASP pages accessing this data from the sqlserver database.

    I can do the conversion on the VB side. But my concern is that it may affect the formatting for existing ASP pages. And also that, this will lead to changes in the parameter datatype.

    For this reason, i am keeping all the existing data types for the parameters for all the functions and stored procedures.

    Thanks,
    Muru

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Here is the problem - you have a value that has 7 digits to the left and 3 digits to the right of the decimal point. However, your precision that you are asking for is 9 digits total - but you have 10 total. That is why you are receiving the overflow. Either change to (9,2) or (10,3) and that will fix your overflow problem. Also, be careful and examine how sql server handles rounding/truncating when you store an int/decimal(11,2) from a decimal(x,3). In your case -- When you convert to an int, ss will truncate. When you convert to a smaller decimal place, ss will round. So, in your situation you have 2 different "rounding" models which may eventually affect (adversely) your results - depending on how scientific or precise you are trying to be.

  7. #7
    Join Date
    Oct 2002
    Posts
    369

    Exclamation

    rnealejr is correct, (you are receiving the overflow because you have 10 digits that you are casting to 9). I would add the following more general warning:

    It is often very important to be aware of the implications of (both) explicit (as rnealejr pointed out) and implicit type conversions (unfortunatly, the results are often DBMS implementation, and / or version specific);

    -- Simple example:
    Select
    1234567 / 17 As 'Truncation',
    1.0000 * (1234567 / 17 ) As 'Truncation + Scale',
    1.0000 * 1234567 / 17 As 'LessTruncation',
    1.0000 * 1234567 / '17' As 'MuchLessTruncation',
    1.0000 * (1234567 / '17') As 'Truncation + Scale'

    -- More simple examples with some explanation:
    Select
    1234567.0000 / 17.0000 As 'A',
    (1.0000 * 1234567) / (1.0000 * 17) As 'B',
    1234567 / 17 As 'C'

    -- Some Conversions involve truncation
    Select
    Cast ((1234567.0000 / 17.0000) AS Int) As 'Cast A --> Int',
    Cast (((1.0000 * 1234567) / (1.0000 * 17)) AS Int) As 'Cast B --> Int',
    Cast ((1234567 / 17) AS Int) As 'Cast C --> Int'

    -- Some Conversions involve rounding
    Select
    Cast ((1234567.0000 / 17.0000) AS Money) As 'Cast A --> Money',
    Cast (((1.0000 * 1234567) / (1.0000 * 17)) AS Money) As 'Cast B --> Money',
    Cast ((1234567 / 17) AS Money) As 'Cast C --> Money'

    -- Other Conversions are not as intuitively obvious
    -- Char (implicit conversions) of A, B, and C give:
    Select 1234567.0000 / '17.0000' As 'A Char',
    'B Char results in an overflow' As 'B Char',
    1234567 / '17' As 'C Char'
    -- NOTE B Char --> results in an overflow (OVERFLOW error result)
    Select (1.0000 * 1234567) / (1.0000 * '17') As 'B Char Overflow'

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    For future reference, go to your sql books online and look at the "Cast and Convert" reference.

  9. #9
    Join Date
    Nov 2002
    Location
    Singapore
    Posts
    4

    Arithmetic Overflow - VB6.0/Stored Proc - Application

    Dear All,

    Thanks a lot for your help.

    I have changed the scaling from numeric(9,3) to (11,3) and it now works. When tried with (10,3), my program gave the same error again for another set of data and hence i am using (11,3).

    Thanks and regards,
    Muru

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •