• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Rosie Fairfield
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?
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • 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.
 
Paul Clapham
Sheriff
Posts: 21554
33
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic