• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

NetBeans database doesn't autogenerate the next primary key value when I insert a new record

 
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I have a NetBeans app that displays the tables from a database that I created thru NetBeans.
It has 3 tables and each table has an INTEGER primary field key, ID;
I can View Data for each table, check the header for the ID column, and, for each table, the ID column is marked PK.

The app displays the records in the database and scrolls forwards and backwards just fine.

The problem is that when I run an INSERT INTO any table to add a new record, if I don't give it a
value for the primary key field, it complains and fails.

Table Authors has 3 fields: ID INTEGER, PRIMARY KEY; FIRST_NAME, VARCHAR; LAST_NAME, VARCHAR.

running: INSERT INTO AUTHORS VALUES ('John', 'Steinbeck');
results in:
Error code 30000, SQL state 42802: The number of values assigned is not the same as the number of specified or implied columns.

running: INSERT INTO AUTHORS VALUES (6, 'John', 'Steinbeck');
results in:
a new record in the database.

Shouldn't the primary key value be generated automatically?
How can I fix this?
 
Marshal
Posts: 67447
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What database is netbeans using? Not all databases have that ability. And usually when it does, the column needs to be declared as such, or a sequence needs to be generated.

For example MS SQL uses an IDENTITY property to make a column as auto-generated.
 
Marshal
Posts: 25804
69
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Rosie Fairfield wrote:The problem is that when I run an INSERT INTO any table to add a new record, if I don't give it a
value for the primary key field, it complains and fails.

Table Authors has 3 fields: ID INTEGER, PRIMARY KEY; FIRST_NAME, VARCHAR; LAST_NAME, VARCHAR.

running: INSERT INTO AUTHORS VALUES ('John', 'Steinbeck');
results in:
Error code 30000, SQL state 42802: The number of values assigned is not the same as the number of specified or implied columns.



But that error message is saying that you didn't provide values for all three columns -- which you didn't. If you want to specify values for only two columns, and let the database provide its default value for the other, then you have to tell it which two columns you're providing values for. Depending on your database, that would be something like this:

INSERT INTO AUTHORS (FIRST_NAME, LAST_NAME) VALUES ('John', 'Steinbeck')

It may be that once you have this fixed, your database will insert sequential values into the ID column and not just zero or null, but you'll have to try it for yourself to see what happens.
 
Rosie Fairfield
Ranch Hand
Posts: 32
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well,

It took me awhile to figure it out but you both pointed me in the right direction.

I did need to figure how to set a column to autogenerate in NetBeans' Apache Derby database, like Bear said, and once I got that done, it did require naming the columns that
I need to fill, like Paul said.

It's working fine now

Thank you both.
 
Don't get me started about those stupid light bulbs.
    Bookmark Topic Watch Topic
  • New Topic