asked on

Cannot insert explicit value for identity column in table xxx when IDENTITY_INSERT is set to OFF

Hello,

I have browsed google and saw that many people have the same problem, but the solution suggest I already use...

I have 2 Tables with a 1:Many relation.

I am using Entity Framework v4 beta 2.

When I am Add a Order to a selected Customer and save those changes to the Context I get this error message:

Cannot insert explicit value for identity column in table 'OrderSet' when IDENTITY_INSERT is set to OFF

BUT my identity column is set to (Is Identity) = YES, there is no ON/OFF thing...

what is wrong here?

Muhammad Kashif

some how your identity insert has been set to off

run following statement to make it on

SET IDENTITY_INSERT OrderSet ON

Guy Hengel [angelIII / a3]

if you insert a row into the table, and let sql server populate the identity value, do this:

INSERT INTO OrderSet ( col1, col2 ... all columns you need, skipping the ID field) VALUES ( value1, value2 ... all values you want to insert)

to fetch the id value generated, do:

SELECT SCOPE_IDENTITY()

if you want to provide the ID field itself

SET IDENTITY_INSERT OrderSet ON
INSERT INTO OrderSet ( ID, col1, col2 ... ) VALUES ( 1, value1, value2 .... )
SET IDENTITY_INSERT OrderSet OFF

Open in new window

ASKER CERTIFIED SOLUTION

MSFanboy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial