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

Numeric overflow in [tinyint] field

P: n/a
Hi,

In Query Analyzer:
update tblUserProcess
set usercode = 1002

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

In VBA/Access:

intOptions = 512
pstrQuerySQL = "UPDATE ..."

CurrentDb.Execute pstrQuerySQL, intOptions

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

Why?

Thanks, Eugene
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The TINYINT data type holds numbers 0 thru 255. Trying to put 1002 into
a column w/ that data type causes the overflow (imagine trying to pour
40 gallons of water into a 20 gallon container - it would overflow).

Why the dbSeeChanges (512) option suppresses the error - I don't know!

To be able to enter 1002 into your usercode column, try changing the
column's data type to SMALLINT.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQf/qO4echKqOuFEgEQJx6wCfTtmFoimgItOtNBHO2fWhYiJloPQAo IcZ
bLQQBUkLLKVBxIcux6Gc8f7o
=+Ha1
-----END PGP SIGNATURE-----
Eugene wrote:
Hi,

In Query Analyzer:
update tblUserProcess
set usercode = 1002

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

In VBA/Access:

intOptions = 512
pstrQuerySQL = "UPDATE ..."

CurrentDb.Execute pstrQuerySQL, intOptions

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

Why?

Thanks, Eugene

Nov 13 '05 #2

P: n/a
MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The TINYINT data type holds numbers 0 thru 255. Trying to put 1002 into
a column w/ that data type causes the overflow (imagine trying to pour
40 gallons of water into a 20 gallon container - it would overflow).

Why the dbSeeChanges (512) option suppresses the error - I don't know!


Because there's no dbFailOnError, use 640 instead of 512. Better still,
the OP should make the code a bit more self documenting:

CurrentDb.Execute pstrQuerySQL, dbSeeChanges + dbFailOnError

Most people would have to dig around to find out what slapping 512 onto
the end of an Execute method would do.

The value 233 probably comes from some sort of wrapping but I would have
expected nearer to 237 to be inserted.

--
This sig left intentionally blank
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.