Numeric overflow . No errors. Insert the wrong number

[Eugene]

Hi,

I have a field: usercode [tinyint]

In Query Analyzer:
UPDATE tblUserProcess
SET usercode = 1002

Result: Error "Arithmetic overflow error for data type tinyint, value = 1002.
The statement has been terminated."
In VBA/Access ( linked to SQL Server ):

intOptions = 512
pstrQuerySQL = "UPDATE ..."
CurrentDb.Execute pstrQuerySQL, intOptions

Result: no errors, insert value 223 (???)

Why?

Thanks, Eugene

[louis]

TINYINT max value is 255. The error msg said it can't insert value
1002 -- I don't understand what the 223 is about.

[dbmonitor]

The max value for a tinyint is 255. as your value is more then that,
SQL Server gives you this message.

--
David Rowland
For a good user and performance monitor, check DBMonitor
http://dbmonitor.tripod.com

[Erland Sommarskog]

Eugene (yg********@hotmail.com) writes:

I have a field: usercode [tinyint]

In Query Analyzer:
UPDATE tblUserProcess
SET usercode = 1002

Result: Error "Arithmetic overflow error for data type tinyint, value =
1002.
The statement has been terminated."

In VBA/Access ( linked to SQL Server ):

intOptions = 512
pstrQuerySQL = "UPDATE ..."
CurrentDb.Execute pstrQuerySQL, intOptions

Result: no errors, insert value 223 (???)

Why?

If the string you pass from the VBA app really is
"UPDATE tblUserProcess SET usercode = 1002", then there is something
really fishy going on.

I can see a couple of possibilities:
1) You have poor error handling in the VBA code, so you fail to catch
the error. The column already had the value 223 prior to the UPDATE.
2) The statement is parameterized, and the VBA code loses the upper bits
of 1002. In such case you get 224. Is 223 a typo?

More information would be useful.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[dbmonitor]

Erland Sommarskog wrote:

I can see a couple of possibilities:
1) You have poor error handling in the VBA code, so you fail to catch
the error. The column already had the value 223 prior to the UPDATE. 2) The statement is parameterized, and the VBA code loses the upper bits of 1002. In such case you get 224. Is 223 a typo?

How do you get 224?

Is this calculated by translating 1002 into hex giving you 0x3EA,
stripping the 3 as a tiny int is only two bytes leaving 0xEA, then
converting back to dec.

In this case the number would be 234.

Just asking as I have always wanted to know how this works.

--
David Rowland
For a good user and performance monitor, check DBMonitor
http://dbmonitor.tripod.com

[Erland Sommarskog]

dbmonitor (db***************@hotmail.com) writes:

How do you get 224?

Is this calculated by translating 1002 into hex giving you 0x3EA,
stripping the 3 as a tiny int is only two bytes leaving 0xEA, then
converting back to dec.

In this case the number would be 234.

I used "SELECT 1002 % 256". Hm, now in the light of day the result is indeed
234. Hm...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[dbmonitor]

Erland Sommarskog wrote:

dbmonitor (db***************@hotmail.com) writes:

How do you get 224?

Is this calculated by translating 1002 into hex giving you 0x3EA,
stripping the 3 as a tiny int is only two bytes leaving 0xEA, then
converting back to dec.

In this case the number would be 234.
I used "SELECT 1002 % 256". Hm, now in the light of day the result is

indeed 234. Hm...
Trust me to take the programmers approach!!! B^D
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

--
David Rowland
For a good user and performance monitor, check DBMonitor
http://dbmonitor.tripod.com