Violation of PRIMARY KEY constraint Cannot insert duplicate key in object . In C#.net....Visual Studio 2010...framework 3.5...

Question

I'm Developing a small windows application in C#.net....Visual Studio 2010...framework 3.5... i use Linqtosql for database manipulation..... table name:cprofile Fields of the table are: custid int (primary key), custname varchar(50), address nvarchar(MAX), mobileno nchar(10)
So i have changed the 'Is identity' property of the 'cust id' to 'yes'...it automatically changes other 2 sub properties.... Identity Increment = 1 Identity Seed = 1, After these chages have been made in the table.... It throws error...when i try to save a new record... "Cannot insert explicit value for identity column in table 'cprofile' when IDENTITY_INSERT is set to OFF."

Answer 1

If you're trying to use the same insert statement you were using before, you can no longer do that. I'm not sure how it's done in the C# side of it, but in SQL, you'd have to run statements to turn identity_insert on, then run your statement. Because you changed the column to identity, the table makes sure the next entry is always 1 number higher than the previous. Because of this, you can't simply insert values into it. If you want the table to create the identity value for you, simply remove it. If my explanation doesn't help, hopefully this will.

Table Definition (Table1) Col1 Identity Col2 varchar(50) Col3 bool

Insert statement before identity INSERT INTO Table1 VALUES (1, 'Test', TRUE)

Insert statement after identity INSERT INTO Table1 VALUES ('Test', TRUE)

When identity is on, you cannot specify the value without turning on identity_edit. I'll see if I can find how to do that in Linq.

EDIT: I also like what Daniel said. Didn't think about that.

Answer 2

Not too familiar with L2S, but I'd say Daniel is correct: update your model (usually on a context menu somewhere) from the DB. That should prevent it from attempting to insert a value into your auto-incrementing ID column.

I believe there may be a way to have it set IDENTIY_INSERT ON, but I highly recommend against it.

If your table should not be in charge of setting the CustomerId (say, the business has some method of making that determination (especially in a non-linear way), leave your Customer Id column as the PK, but remove the Identity specificaiton from the column.