[SOLVED] MySQL Error insert data

[DjaDjaWurung1710]

Hi,

I'm a newbie trying to use MySQL

I'm using MySQL 5.1.30

I've created the following table

CREATE TABLE OPTICAL_FRAMES (

opt_num INT UNSIGNED AUTO_INCREMENT,

Brand char (30) NULL,

model char (30) NULL,

Image LONGBLOB,

Description BLOB NULL,

Retail DECIMAL (3,2),

Sale_Price DECIMAL (3,2),

Material char (30) NULL,

col_nam char (30) NULL,

PRIMARY KEY (opt_num);

When I try to insert the following data

INSERT INTO OPTICAL_FRAMES Values

(NULL,'Smith and Jones New York','511 C1',NULL,'Hand Made Acetate with Spring Hinges','136.00','27.28','Acetate','Dark Orange');

I get this error message

ERROR 1264 <22003> Out of range value for column 'Retail'

I don't understand how it can be out of range the Decimal parameter is 3,2 and the price is three figures with two decimal places.

Please help, thanks.

David

 

[fenway]

First, don't use NULL for the id field... just leave it out.

 

Second, to do that, you'll need to have a column list -- which you should always have anyway.

 

Third, the DECIMAL format specifies the TOTAL number of digits, and then how many are decimals -- so (3,2) means you can store #.## -- not enough.  You want (5,2).

 

So:

 

INSERT INTO OPTICAL_FRAMES 
( Brand char, model char, Image, Description, Retail, Sale_Price, Material char, col_nam char)
VALUES ('Smith and Jones New York','511 C1',NULL,'Hand Made Acetate with Spring Hinges','136.00','27.28','Acetate','Dark Orange');

 

Isn't it good that MySQL 5 has strict mode on?

[DjaDjaWurung1710]

Thanks I'll try that, tomorrow. I thought I did the same thing in an earlier version & it worked, maybe not.