violation of primary key constraint .Cannot insert duplicate key in object using ADO

Question

Hi All we are working on a users apllication using Access2003(VBA) as software language and SQL Server 2005 as database. We are using ADO method and we encounter a problem. when users create new record in a ADO Screen and they want to save the record after implementing it they receive this error : error -2147217873 violation of primary key constraint 'PK_ '.Cannot insert duplicate key in object 'Pk_...' Any help will be appreciated Thanks in advance

Answer 1

Are you trying to insert a new record with the primary key field having a value that is already in the database. The primary key field must always contain unique values.

Answer 2

The problem occures since you can't have two primary keys with the same value.

If you are using Ints as primary key, remember to put auto-increment on it. If you are using GUID as primary key, you might forget to set the guid to sometheing else than the default empty guid, and there by trying to insert and empty guid twice.

Answer 3

Check witch columnt is your PrimaryKey. If you are trying to insert value that already exist then you are getting that error.

Answer 4

you should create your PK value either from your code or on the SQL side. On the SQL side, when creating your database, you have to indicate that default value for "myPrimaryKey" field is uniqueIdentifier, while from code, you could have something like

myRecordset.fields("myPrimaryKey") = stGuidGen()

(check here for the stGuidGen function)

There are some pros and cons to each method. By implementing the SQL method, you do not have to care about generating PKs anymore. By doing it through code, you can store the newly generated value without having to requery the database, and reuse it immediatly in your code, and this can be very useful.