Numeric overflow in [tinyint] field

[Eugene]

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

[MGFoster]

-----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

[Trevor Best]

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