• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1265
  • Last Modified:

{"Cannot insert ...when IDENTITY_INSERT is set to OFF."}

Using Entity Framework 4.0 and SQL Server 2008 I got the following exception:

{"Cannot insert explicit value for identity column in table 'notifications' when IDENTITY_INSERT is set to OFF."}

Any idea why?

newbieweb
0
newbieweb
Asked:
newbieweb
4 Solutions
 
Rajkumar GsSoftware EngineerCommented:
Your table contains a IDENTITY column - It could be primary key itself.
When you are trying to insert a data, if you are passing value for this IDENTITY column also, this error could occur - Because IDENTITY column data is automatically inserted by incrementing
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
Because the setting in the database IDENTITY_INSERT == OFF does not allow it.  You need to set IDENTITY_INSERT to ON for it to be allowed
0
 
newbiewebSr. Software EngineerAuthor Commented:
> if you are passing value for this IDENTITY column also
No, I am letting that value get set by the database.

> You need to set IDENTITY_INSERT to ON for it to be allowed
How do I do that?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
AndyAinscowFreelance programmer / ConsultantCommented:
>>No, I am letting that value get set by the database.


Then it should be working irrespective of that setting.
0
 
newbiewebSr. Software EngineerAuthor Commented:
For the record, where can I find that setting in SQL Server 2008?
0
 
8080_DiverCommented:
newbieweb,

For the record, you really shouldn't be playing with that setting.

I suspect that your query looks something like:
INSERT INTO table
SELECT *
FROM anothertable;

or

INSERT INTO table
SELECT a list of values;

Open in new window


If you try to use the INSERT INTO table without specifying the columns you are inserting into, then the query engine assumes that you are going to insert into all of the columns . . . including the identity column.

try changing your INSERT query to look like:
INSERT INTO table
(
list your columns
)
VALUES
(
list your values
);

or

INSERT INTO table
(
list your columns
)
SELECT your values to be inserted;

Open in new window

0
 
newbiewebSr. Software EngineerAuthor Commented:
All great advice.  Thanks.

It turns out that I have modified the database to have the Indentity set to Yes on the primary ye, but forgot to import it it into the edmx file.  Once I imported, it worked.

0
 
newbiewebSr. Software EngineerAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now