MySQL Error 1136 "Column count does not match value count
INSERT INTO invoices
Write insert statement that adds this row to invoices table, Write without using a column list
INSERT INTO invoices VALUES ('','32','AX-014-027','8/1/2001','$434.58','$0.00','$0.00','2','8/31/2011''NULL') /* Write insert statement that adds this row to invoices table, Write without us' at line 7 */ Error Code: 1136. Column count doesn't match value count at row 1
the values are
invoice_id the next automatically generated number
I am doing this as an exercise for school, I have 10 columns and 10 values, so I have no idea why I'm getting a count does not match, and the instructions specify to NOT use a column list. first item in the list is "auto increment". I have tried with no quotes, with quotes (the way it is right now) and I am stumped. the code looks good to me!
thanks in advance!
using MySQL workbench ver 5.2.38
You are missing a comma before the last value. Also, you probably do not want quotes around NULL (unless you want to save the string literal 'NULL' instead of setting the field to be null?) Likewise, if the first field is auto-incremented integer, you probably also want to specify NULL. Optionally, you could specify a field list in the query, and just not include any fields you want to be null (if that is the default). Also, I'd suggest you store any value that are numeric as numeric types, not strings, so you would not include the dollar sign with the prices, and you would not quote the numeric values.
Another advantage of using a field list is that your query won't be broken if someone adds an new column to the table.
INSERT INTO invoices (
personally I would prefer to use the field list but it is an exercise in Murach's MySQL and specifies not to use one, well I tried the first fix and added the comma to the last field and got the err msg "Error Code: 1366. Incorrect integer value: '' for column 'invoice_id' at row 1"
then I tried your code and got this err msg
Error Code: 1136. Column count doesn't match value count at row 1
so I am kind of stumped, I do appreciate the advice about what to quote and not ($ signs included)
then I tried this
VALUES ('null',32, 'AX-014-027','8/1/2001',434.58,0.00, 0.00,2,'8/31/2011','')
and got a syntax error
so this was next
VALUES ('',32, 'AX-014-027', '8/1/2001', 434.58, 0.00, 0.00, 2,'8/31/2011','')
Error Code: 1366. Incorrect integer value: '' for column 'invoice_id' at row 1 (so now I am back to this err msg....grrrr
appreciate all the help but it still wont fly....
oh also I didn't create the table so I am going to ask the teacher as there may be an error in the table, I was trying to resolve it without asking him but....
Try NULL for the first column without any quotes around it (it's a SQL keyword, not a literal value).
You have a syntax error here: '8/31/2011''NULL'
Place a comma (,) between 2011' and 'NULL'.
Like so: '8/31/2011', 'NULL'
yeah already fixed that, still same error message, it cant get past the first column to even see the missing comma in the last column
23:19:07 INSERT INTO invoices VALUES ('',32, 'AX-014-027', '8/1/2001', 434.58, 0.00, 0.00, 2,'8/31/2011','null') /* Write insert statement that adds this row to invoices table, Write without using a column list */
Error Code: 1366. Incorrect integer value: '' for column 'invoice_id' at row 1
honestly I am beginning to think there is an issue with the table, we found errors in the table when we first started the project and the teacher went back and modified it twice, maybe he has modified it again, I honestly don't know why it wont auto increment. but I thank you for the response, I will let you know if I hear back from my teacher (this class only meets on Thursdays and Fridays sooo, and that's the problem because there are a total of 9 exercises and I am stuck on #4 and until I get this fixed I cant debug any of my other code
As I said, do not quote null:
INSERT INTO invoices VALUES (NULL, 32, 'AX-014-027', '8/1/2001', 434.58, 0.00, 0.00, 2, '8/31/2011', NULL)
Yeah, NULL shouldn't be in quotes.
I suggest the SET to insert instead of the common way to naming columns and them values. It is very simple to understand and you know which column has what. Also, to change this into an UPDATE is very simple since it's the same structure
(just change the INSERT INTO to UPDATE and add a WHERE clause).
INSERT INTO `folding_cartons` SET
`part_no` = '240-63100-00',
`description` = 'YB MV Cherry',
`count` = 90,
`size` = '3 x 2-11/16 x 5-5/64',
`pdf_link` = 'http://www.logicpkg.com/data/hero/copies/240-63100-00.pdf',
`min` = 0,
`max` = 0,
`current` = 0;
i have the same problem but not with the same line
Error Code: 1136 - Column count doesn't match value count at row 1
INSERT INTO `creature` VALUES ('1', '721', '0', '1', '1', '4626', '0', '-9638.02', '-3164.06', '49.1948', '3.11049', '300', '5', '0', '1', '0', '1', '0', '0', '0', '1', '1')
i hope you can give me a answer!
It's really quite simple: count how many different values you are trying to insert (comma-separated elements in the VALUES() expression). Now count how many columns are in your "creature" table. If those two numbers are not exactly the same, then there's your problem.
Originally Posted by DeathHorde
If you do not want to supply a value for every column in the table (e.g. leave some null or to use their default values), then you have to provide an explicit column list as part of the query:
INSERT INTO fubar (col1, col2, col3) VALUES ('one', 'two', 'three')
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)