www.webdeveloper.com
Results 1 to 12 of 12

Thread: MySQL Error 1136 "Column count does not match value count

Hybrid View

  1. #1
    Join Date
    Oct 2013
    Posts
    4

    MySQL Error 1136 "Column count does not match value count

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

    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
    vendor_id 32
    invoice_number AX-014-027
    invoice_date 8/1/2001
    invoice_total $434.58
    payment_total $0.00
    credit_total $0.00
    terms_id 2
    invoice_due_date 8/31/2011
    payment_date null


    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

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,325
    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.
    Code:
    INSERT INTO invoices (
    	vendor_id,
    	invoice_number,
    	invoice_date,
    	invoice_total,
    	payment_total,
    	credit_total,
    	terms_id
    )
    VALUES (
    	32,
    	'AX-014-027',
    	'8/1/2001',
    	434.58,
    	0.00,
    	0.00,
    	2,
    	'8/31/2011'
    )
    Another advantage of using a field list is that your query won't be broken if someone adds an new column to the table.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Oct 2013
    Posts
    4
    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....

  4. #4
    Join Date
    Oct 2013
    Posts
    4
    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....

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,325
    Try NULL for the first column without any quotes around it (it's a SQL keyword, not a literal value).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Oct 2013
    Location
    USA
    Posts
    8
    You have a syntax error here: '8/31/2011''NULL'

    Place a comma (,) between 2011' and 'NULL'.

    Like so: '8/31/2011', 'NULL'

  7. #7
    Join Date
    Oct 2013
    Posts
    4
    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

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,325
    As I said, do not quote null:
    Code:
    INSERT INTO invoices VALUES (NULL, 32, 'AX-014-027', '8/1/2001', 434.58, 0.00, 0.00, 2, '8/31/2011', NULL)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    Oct 2013
    Location
    USA
    Posts
    8
    Yeah, NULL shouldn't be in quotes.

  10. #10
    Join Date
    Oct 2013
    Posts
    1

    introduction

    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;

  11. #11
    Join Date
    Jul 2014
    Posts
    1
    Hello all

    i have the same problem but not with the same line
    Error Code: 1136 - Column count doesn't match value count at row 1

    Query:
    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!

  12. #12
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,325
    Quote Originally Posted by DeathHorde View Post
    Hello all

    i have the same problem but not with the same line
    Error Code: 1136 - Column count doesn't match value count at row 1

    Query:
    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.

    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:
    Code:
    INSERT INTO fubar (col1, col2, col3) VALUES ('one', 'two', 'three')
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles