IDENTITY_INSERT设置为关闭错误 [英] IDENTITY_INSERT is set to off error

问题描述

我有一个MVC Web应用程序，该模型中有一个我要添加的表.我已经设置了主键以及其他数据字段，但是每次尝试添加到表中时，都会出现以下错误:

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:

当IDENTITY_INSERT设置为OFF时，不能为表'TABLE_NAME'中的标识列插入显式值."

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

我不确定为什么会出现此问题，我将主键设置为标识，并且在Visual Studio表设计器中还将其设置为自动递增.有什么方法可以在Visual Studio的表设计器中调整IDENTITY_INSERT参数?还是有其他可能导致此问题的问题.

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.

更新:@Brian-据我所知，我没有明确设置值，这是添加到表中的代码.

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();
       }
    }
}

推荐答案

您的代码似乎正在尝试将特定值插入定义为IDENTITY的主键列中.

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.

如果您使用的是Linq-to-SQL，请在可视设计器中单击有问题的表，然后查看属性:

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

自动生成的值"必须为"True"(不是默认值)，并且自动同步"应为"OnInsert"(再次:不是默认值).另外，将主键"设置为true，并检查以确保服务器数据类型"正确-Int NOT NULL IDENTITY(或类似名称).

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).

如果您需要覆盖默认行为(通常仅在清理脚本或一次性数据操作中使用)，则可以在表上打开IDENTITY_INSERT，例如之后，您可以在IDENTITY列中插入任意值:

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.

这篇关于IDENTITY_INSERT设置为关闭错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！