Page 1 of 1

Unable to add new records for movie database

Posted: Fri Mar 03, 2017 9:47 pm
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 (?,?)]

Re: Unable to add new records for movie database

Posted: Fri Mar 03, 2017 10:07 pm
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?

Re: Unable to add new records for movie database

Posted: Sat Mar 04, 2017 2:24 am
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?

Re: Unable to add new records for movie database

Posted: Sat Mar 04, 2017 7:19 am
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.

Re: Unable to add new records for movie database

Posted: Sat Mar 04, 2017 9:22 pm
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.??

Re: Unable to add new records for movie database

Posted: Sun Mar 05, 2017 1:39 am
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.