IDENTITY_INSERT is set to off error

Question

I have a MVC web application with a table in the model that I would like to add to. I have the primary key set along with the other data fields, but every time I try to add to the table, I get the following error:

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

I'm not sure why this problem is coming up, I have the primary key set as the identity and it is also set to auto increment in the Visual Studio table designer. Is there any way I can adjust the IDENTITY_INSERT parameter in the table designer in Visual Studio?? Or is there some other issue that might be causing this.

UPDATE: @Brian - As far as I can tell, I'm not setting the value explicitly, here is the code that adds to the table(s).

//Add viewer
public void addViewer(ModelStateDictionary modelState, Users user)
{
   var userToAdd = new UserRoles();
   userToAdd.Users = user;

   if (String.IsNullOrEmpty(userToAdd.Users.Username))
   {
      modelState.AddModelError("noName", "Please enter a username for the new Viewer");
   }

   //See if Committee Member already exists
   try
   {
      userToAdd = _db.UserRoles.First(ur => ur.Users.Username == userToAdd.Users.Username);
      modelState.AddModelError("userExists", "A Viewer with that username already exists in the system");
      return;
    }
    catch (Exception e)
    {
       if (modelState.IsValid)
       {
          //Assign Committee Member role
          userToAdd.Role = "Viewer";
          userToAdd.Users = user;
          //Add new Committee Member to User Roles and associated username to Users
          _db.AddToUserRoles(userToAdd);
          _db.SaveChanges();
       }
    }
}

Answer 1

Hey,

You are trying to set a primary key value explicitly probably in the LINQ entity possibly; you are trying to say, for example, insert 1 in the primary key field that's an identity, and LINQ sees this as an inserted value.

Also if using LINQ to SQL, in the server data type of the field, ensure that it says IDENTITY in the data type field; if it doesn't say IDENTITY, there may be a configuration error in LINQ.

HTH.

Answer 2

It would seem that your code is trying to insert a specific value into the primary key column that is defined as IDENTITY.

To avoid the error - do not insert any values! Let the database handle getting a new value for the row you're inserting.

If you're using Linq-to-SQL, click on the table in question in your visual designer, and have a look at the properties:

The "Auto Generated Value" must be "True" (which is not the default), and the Auto-Sync should be "OnInsert" (again: not the default). Also, set the "Primary Key" to true, and check to make sure the "Server Data Type" is correct - Int NOT NULL IDENTITY (or something like that).

If you ever need to override the default behavior (typically only in clean-up scripts or one-off data manipulations), you can turn ON the IDENTITY_INSERT on a table, e.g. after that, you can insert any value you like into the IDENTITY column:

SET IDENTITY_INSERT YourTableName ON

INSERT INTO YourTableName(IdentityColumn) VALUES(5555)

SET IDENTITY_INSERT YourTableName OFF

This is more of a cleanup / data admin kind of task - not something you should do all the time as a dev.

Answer 3

Thank you all for your suggestions, I ended up solving my issue by doing a refresh of the model. My model had been created before I set the primary key to the Identity spec with auto-increment, so I guess all it needed was a refresh to get it going.