Skip to content

Issues with IDENTITY_INSERT is set to OFF with ASP.NET Entity Framework 3.5

February 7, 2013

Today, I had been playing with ASP.NET Entity Framework 3.5 using the same ASP.NET version 3.5. Firstly yesterday was time spent getting the right version and updates for installing ASP.NET VS2008 SP1 for Windows 7!  After using google extensively, the correct one is http://www.microsoft.com/en-us/download/details.aspx?id=21016. If you don’t get the right one, you usually get the install window and once it completes nothing happens!

After installing ASP.NET Entity Framework 3.5 I had set the basic insert, update, and delete following a useful tutorial on ASP.NET Entity Framework 3.5 but came up to a common problem: –

Cannot insert explicit value for identity column in table ‘City’ when IDENTITY_INSERT is set to OFF.

After trial and error, including writing the following SQL statements: –

USE [worlddata]

SET IDENTITY_INSERT City ON

DBCC CHECKIDENT ([City], RESEED, 4080) to set the index record starting at 4081.

Unfortunately this still brought up the error even though I had actually set the index identity to on and set the starting index.

The fact of the matter is due to Visual Studio 2008, you have to go refresh the mappings of the Entity Framework Design file (in my case its WorldData.edmx), if you right click and select Update Model from Database, click tables in Add tab and hit Finish. Then with the index record reseeded from the third statement in the SQL script above.  It should solve the IDENTITY_INSERT is set to OFF error.

(The reason because of this was that the ID of the City table wasn’t set as a primary key and as a Identity set to on in Identity Specification, so the mappings in WorldData.edmx hadn’t been reflected until you remap it again!)

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: