Access - Autoincrement problem

I have some code to add a new record that I adapted from the Developer Tutorials as follows:

If _ARecID = 0 Then

Dim MyRecords As AUTORECSDataSet.AUTORECSRow

MyRecords = AUTORECSDataSet.AUTORECS.NewAUTORECSRow()

MyRecords.MFG = MfgComboBox.SelectedValue

MyRecords.Model = ModelTextBox.Text

MyRecords.Year = YearTextBox.Text

MyRecords.DateDone = DateTime.Parse(DatePicker.Text)

MyRecords.Mileage = Integer.Parse(MileageTextBox.Text)

MyRecords.Action = ActionTextBox.Text

MyRecords.Cost = Integer.Parse(CostTextBox.Text)

MyRecords.Done_By = DonebyTextBox.Text

AUTORECSDataSet.AUTORECS.AddAUTORECSRow(MyRecords)

Me.AUTORECSBindingSource.EndEdit()

Me.AUTORECSTableAdapter.Update(Me.AUTORECSDataSet.AUTORECS)

Else

end if

I am attaching to an Access database with an autoincrement field called 'Rec'. Code works fine the first time I use it but not the next. On the second try it throws an error. Message is as follows:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Code worked fine with a SQL database. I want to use Access as I have an existing table with data and didn't want to convert to SQL.

I have read some articles on issues with autoincrement fields access and VB express but haven't been able to figure it out.

Thoughts

Sal

Answer this question

Access - Autoincrement problem

Dholama

That error is quite descriptive and tells you exactly what the problem is: One of the fields requires a unique value. This is setup in the database. When you write data to the first row of the table, since there is no other data it will work fine (since that field will have a unique value).

The second time, that field is getting the same value as previously written - thus it isn't unique. Do you know what that column is You need to know this.

1. Change the column to allow duplicates.

2. Stop it from being an indexed field - this will allow duplicates to be placed in that field.

3. change it to an autoincrementing field - what this does is adds 1 to the field value each time a new record is added: you don't fill anything in this field - the database will do everything for you.

You need to go into the database to make these changes. Personally, every table I create has an autoincreminting field in it - it acts like a unique ID number for that record.
AnilAli

HI,

Check your insert query in your adapter. In the sql statement in the VALUES() area, the primary key or the auto increment field that you are using must not be included in the list. If so, remove it. Also check the "rec" column in your datatable see if its marked as an autoincrement.

But my best bet is that the autoincrement field is being added in your INSERT statement, but the field in your datatable is not autoincremented which could generate this kind of error...

cheers,

Paul June A. Domag
James Webster

Hi,

In addition to shakalama's statement, you can have a generated autonumber directly in your newly added record even if you didn't populate your dataset with the contents of your table by omitting it in your INSERT statement. example:

Table struct:

rec : autonumber
field1 : int
field2 : int

INSERT INTO table1(field1,field2) VALUES(1,2)

As you may have noticed, I have omitted the field "rec" in the insert statement. This statement would then add a record in your table, letting the SqlServier figure out which number to be generated in your "rec" field. Thus eliminating the problem of duplicate entry in your database. The only problem that I see here is getting the newly generated id to be stored in your dataset or datatable. Well you can issue a SELECT @@Identity-1 to get the newly generated autonumber....

So my suggestion is still the same. Check your INSERT statement and drop-off the autonumber field in the INSERT statement.

cheers,

Paul June A. Domag
John Eimer
I'm new to this so please be patient. The only code that I wrote is in my first message. I did not write an insert query. Is this generated by VB Express Where would I find the code

Thanks
Anujapv

hi,

you didn't understand my questions what i meant was that

if you read the table first from your database to your dataset , (lets say you have 10 records), when you add a new record it autoincremented to 11 so you will not get this problem when you updating your database

but if you didn't fill this table first from your database, and you trying to add record it will carry id =1 so that will cause conflict with your database during updating

other wise you can write a connection programmaticly and don't insert anything in the primary key field, the access database will take care of that

you may also try to recreat your dataset again from you database

hope this helps
Hamdy Ghanem
Sorry for my english.. What paul says is right, but something strange sometimes happen in access database on a attached table.

exemple with access XP: create a base .mdb named test1 with the table testindex1 with this structure:

idx : autonumber (primary key)
field1 : text

a second base .mdb named test2 with the table testindex2 (same structure) and the table testindex1 from test1 attached

open test2.mdb , put 10 lines in the testindex1 , and delete line 3, 4 , 5 , 6, 7

so the rec field contain the values: 1, 2, 8, 9 ,10 (if the table was empty and compacted to initalize the index),

now we put 4 lines in the testindex2 , and delete line 1,2,3 so we have one line number 4

execute this SQL statement

INSERT INTO testindex1 SELECT testindex2.* FROM testindex2;

in testindex1 we can see now the lines 1 2 4 8 9 10, and this is correct... but if you try to add a new line, you will see that the autoincremented field take the value of 5 and not 11. continue to insert new lines and you'll see that after the field reach the value of 7, nothing else can be inserted in this table because the line 8 stills exist, and the new autoincrement want to be 8. Something is corrupt in the autoincremented field.

this bug appears only with attached tables, if testindex1 was in test2.mdb, the next value of the idx field would have been 11, and nothing wrong happen.
Tony Maynard-Smith
The problem is with my Primary Key field which is set to autoincrement. It's like the index is not being increased / saved and starts back at the 'seed' value each time
xchiaroni
Paul,

Found the code in the 'XSD' file and made the correction. All works fine.

Thank You

Sal
render77

hi,

are you sure your Rec field hold AutoNumber type in your database if so

do your dataset read from that table first later on you save the updates back to your database or you just enter some new records and save them

i guess you build your dataset from mdf , have you tried to rebuild it again from mdb

best regards
wckdtribal
This is an Access database that I've used for some time. The 'Rec' field is autoincrement and works fine when I use Access to add resords.

I have a VB EXpress form set up to add data to the database (there are existing records in the database as well). I use the form to add the record and save to the database. All works fine the first time that I add a record. The nec=xt time that I try I get the error.
Access - Autoincrement problem

Answer this question