CODEWITHSUNDEEP
mysqlinsert
AnchovyLegend
AnchovyLegend

Reputation: 12538

Incorrect integer value: '' for column 'id' at row 1

I am trying to insert into my mySQL database. The first column is the 'id' column, since its an auto_increment field, I left it blank. For some reason, I am unable to insert and I am getting the error mentioned below. I appreciate any help with this.

I am getting the following error while trying to insert:

Incorrect integer value: '' for column 'id' at row 1

my query

INSERT INTO workorders VALUES('', ?, ?, ?, ?, ?)

Upvotes: 46

Views: 385500

Answers (7)

Mucyo Christian
Mucyo Christian

Reputation: 101

When using mysql server, you can fix it by typing set global sql_mode=''.

Upvotes: 2

Kermit
Kermit

Reputation: 34055

That probably means that your id is an AUTO_INCREMENT integer and you're trying to send a string. You should specify a column list and omit it from your INSERT.

INSERT INTO workorders (column1, column2) VALUES (?, ?)

Upvotes: 44

Susampath
Susampath

Reputation: 716

This is because your data sending column type is integer and your are sending a string value to it.

So, the following way worked for me. Try with this one.

INSERT INTO workorders VALUES (
    null,
    ?,
    ?,
    ?,
    ?,
    ?
)

Don't use 'null'. use it as null without single quotes.

Upvotes: 2

peterm
peterm

Reputation: 92785

To let MySQL generate sequence numbers for an AUTO_INCREMENT field you have three options:

  1. specify list a column list and omit your auto_incremented column from it as njk suggested. That would be the best approach. See comments.
  2. explicitly assign NULL
  3. explicitly assign 0

3.6.9. Using AUTO_INCREMENT:

...No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

These three statements will produce the same result:

INSERT INTO workorders (`priority`, `request_type`) VALUES(?, ?, ...)
INSERT INTO workorders VALUES(NULL, ?, ...)
INSERT INTO workorders VALUES(0, ?, ...)

Upvotes: 34

Sive.Host
Sive.Host

Reputation: 85

If there is phpMyAdmin just go to variables and look for SQL Mode, variable, edit it and remove all the contents of the variable, then save. That should solve this problem too for those that do not want to touch the code.

Upvotes: 0

Machindra
Machindra

Reputation: 121

For the same error in wamp/phpmyadmin, I have edited my.ini, commented the original :

;sql-mode= "STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

and added sql_mode = "".

Upvotes: 3

PJunior
PJunior

Reputation: 2767

Try to edit your my.cf and comment the original sql_mode and add sql_mode = "".

vi /etc/mysql/my.cnf

sql_mode = ""

save and quit...

service mysql restart

Upvotes: 29

Related Questions