Using AutoIncrement with Access DB

[clg]

I am using an Access database with an autoincrement field. When I am writing the new records to my Dataset, I get an error saying that the field is constrained to be unique and a record with that number already exists. I have played with the autoincrement seed and deleted records enough to realize that the autoincrement number in the dataset is not the same number as the actual database (it increments properly). My problem is -- what do I do with the error? If I say "continue" the record does not get saved.

 

Can I capture this exception somehow and still save the record?:confused:

[clg]

I just came upon a suggestion to set the

 

AutoincrementSeed to -1 and the AutoIncrementStep to -1

 

This should make the dataset count backwards and therefore never reach my actual table values. This sounds like it might work.

 

Any comments?

[Robby]

I don't get it... What method are you using to save/update the database from the dataset?

 

Either way, you don't need to include the ID field as part f your update, that usually causes this type of error.

[clg]

I'm using a DataRow

 

First I create a new datarow, then add values to the row, then add the row to the dataset. Next I use the CommandBuilder Insert command to update the database.

 

Previously, when I didn't deal with that column at all, I got an error that the column can't be null. I just tried to comment out the autoincrement stuff to recreate the error, but I didn't get it this time. Maybe there was something else amiss back then.

 

Are you saying that I don't need to deal with the autoincrement column at all in my code, only set it to autoincrement in my Access Database?

[Mehyar]

Simply you cannot deal with Auto Increment in your .NET applications when the database is Microsoft Access. If you don't include it in the Insert statement it tells you that you cannot insert null into the field. If you include it you do not know what the actual id value should be since mostly your application will be multi user.

My advice dont use it.

Create your own ID.

Unless you can use SQL Server

[pcf108]

There's an article on using the AutoIncrement on microsoft..

 

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629&Product=vbNET