?
Solved

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

Posted on 2011-02-25
8
Medium Priority
?
1,246 Views
Last Modified: 2012-05-11
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
Comment
Question by:newbieweb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 400 total points
ID: 34982463
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
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 800 total points
ID: 34982473
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
 

Author Comment

by:newbieweb
ID: 34982498
> 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 800 total points
ID: 34982563
>>No, I am letting that value get set by the database.


Then it should be working irrespective of that setting.
0
 

Author Comment

by:newbieweb
ID: 34982662
For the record, where can I find that setting in SQL Server 2008?
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 800 total points
ID: 34982800
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
 

Author Comment

by:newbieweb
ID: 34985453
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
 

Author Closing Comment

by:newbieweb
ID: 34985454
Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question