CODEWITHSUNDEEP
c#sqllinq-to-sql
Eric
Eric

Reputation: 111

"Cannot insert duplicate key in object" when deleting entries

I know this sounds ridiculous, but I keep getting an error telling me that I cannot insert a duplicate key when trying to delete records in my database. My code is is quite basic

            using (var scope = new TransactionScope())
            {
                    this.DataSource.ParameterGroupEntries.DeleteAllOnSubmit(dataBaseParameterEntries);
                    this.DataSource.SubmitChanges();
                    this.DataSource.ParameterGroupEntries.InsertAllOnSubmit(localParameterEntries.ToArray());
                    this.DataSource.SubmitChanges();

                    scope.Complete();
            }

However on the SubmitChanges() right after the DeleteAllOnSubmit(dataBaseEntries) command, I keep getting the exception "Cannot insert duplicate key in object." I have tried breaking this up into two separate transactions, and even using DeleteOnSubmit() with a foreach loop and even removing the insert statement. No matter what, I get this error. This has really stumped me because I am not inserting any records, I am deleting them. Any advice?

UPDATE: Upon removing the key constraint that was giving me trouble seemed to work. But since I am not the creator of the database, this is not really the most desirable choice.

Upvotes: 0

Views: 1314

Answers (2)

arifnpm
arifnpm

Reputation: 417

You may need to check for change before transaction scope, remove all inside the transaction scope and leave only 1 SubmitChanges() and scope.complete() to check for changes (insertion of duplicate record key)

Upvotes: 0

Alex
Alex

Reputation: 2959

I guess you have a problem with your seed, may be the seed value is out of synch with the existing table values.

You should call 

dbcc checkident(TableName, reseed, Value)

and make value something less than the current value.

Upvotes: 1

Related Questions