Unable to add new records for movie database

Discuss the database features
Post Reply
RoamCajun63
Posts: 3
Joined: Fri Mar 03, 2017 9:33 pm

Unable to add new records for movie database

Post by RoamCajun63 »

This is the message I get when I try to add a new record to my movie database. Can this be fixed??

Error Inserting New Record

Violation of Unique Constraint SYS_PK_47: duplicate value(s) for column (s) "ID" in statement [INSERT INTO "DVD-Collection" ("ID", "Movie Title") VALUES (?,?)]
OpenOffice 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Unable to add new records for movie database

Post by UnklDonald418 »

duplicate value(s) for column (s) "ID"
this is telling you that there is already another record with the same value in the ID column as the one you are trying to add.
Look at your table "DVD-Collection" is the ID field set to Auto Increment, or are you choosing the ID value when you post a new entry?
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
RoamCajun63
Posts: 3
Joined: Fri Mar 03, 2017 9:33 pm

Re: Unable to add new records for movie database

Post by RoamCajun63 »

okay, I have 730 movies, so this time I tried to add a record without putting the number in, and got this message.

ERROR INPUT

Attempt to insert into a non-nullable column: ID TABLE: DVD Collection in Statement [INSERT INTO "DVD-COLLECTION" ("ID", "MOVIE TITLE") VALUES (?,?)].

So how do I put in new movies into this database or do I have to re-input the whole thing again so I can reflect my new purchases?
OpenOffice 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Unable to add new records for movie database

Post by UnklDonald418 »

Attempt to insert into a non-nullable column: ID TABLE: DVD Collection
This is telling you that you when adding a new record to table “DVD-COLLECTION” you MUST enter a value in the ID column, and from your previous message it must be UNIQUE.
The best solution is to open your table in the Edit mode and set the AutoValue property to “Yes” for the ID field. Once you save the changes Base should automatically select an appropriate value for ID field.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
RoamCajun63
Posts: 3
Joined: Fri Mar 03, 2017 9:33 pm

Re: Unable to add new records for movie database

Post by RoamCajun63 »

Can you walk me thru, step by step, from clicking on the Open Office, and opening the database cause I cannot seem to be doing the right sequence in order to get to resetting the Autovalue.??
OpenOffice 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Unable to add new records for movie database

Post by UnklDonald418 »

With your main database window open select the Tables icon from the list along the left side.
Right-Click on the table "DVD-Collection" and select Edit from the pop-up menu.
Select “ID” from the list of fields, and look toward the bottom of the screen for the property settings. AutoValue should be the first entry, click on the down-arrow and select Yes. Click on the X on the top right of the window and answer Yes when it asks if you want to "Save the changes".
Now when you want to enter a new row of data the ID field will show <AutoField> and it won't allow you to make an entry in that field. Once you have entered data in the other fields and move the cursor to a new row it will automatically assign a value to ID and save the the data you entered in that row.
Let us know if this solved your problem or if you still need help.

P.S.
When using an Embedded database (it says that in the lower left corner of the database window) be sure to back-up your obd file frequently, I recommend after every session where you add new records. When using Windows either save the backup to a different drive, or if you only have one drive then use a slightly different name for the backup. There are situations where the database doesn't shut down properly and your tables can disappear! The crashes generally don't happen very often but it can be rather disconcerting when you find all your data tables have vanished. If/when it does happen a recent backup can make things less stressful.
Converting to a split database sidesteps that problem, but the conversion can seem daunting to someone new to Base. For a home dvd collection I'm not sure it's worth the effort, but you can find the procedures (yes, it's further complicated because there is more than one way) in the Base Tutorials of this forum. Either way you still should backup.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply