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
Singaporei 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|||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|||okay, try not putting quotes around your numerics

e.g. set stock_value = 2593500 / 2

rudy|||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|||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.|||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'|||For future reference, go to your sql books online and look at the "Cast and Convert" reference.|||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