By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Numeric overflow . No errors. Insert the wrong number

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


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

Jul 23 '05 #2

P: n/a
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

Jul 23 '05 #3

P: n/a
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
Jul 23 '05 #4

P: n/a

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

Jul 23 '05 #5

P: n/a
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
Jul 23 '05 #6

P: n/a

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

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.