SQL IDENTITY_INSERT 设置为关闭错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2851754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IDENTITY_INSERT is set to off error
提问by kingrichard2005
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:
我有一个 MVC Web 应用程序，模型中有一个表，我想添加到其中。我设置了主键和其他数据字段，但每次尝试添加到表中时，都会出现以下错误：
"Cannot insert explicit value for identity column in table 'TABLE_NAME' when IDENTITY_INSERT is set to OFF."
“当 IDENTITY_INSERT 设置为 OFF 时，无法为表 'TABLE_NAME' 中的标识列插入显式值。”
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.
我不确定为什么会出现这个问题，我将主键设置为标识，并且在 Visual Studio 表设计器中也设置为自动递增。有什么办法可以在 Visual Studio 的表设计器中调整 IDENTITY_INSERT 参数？或者是否有其他一些问题可能导致这种情况。
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).
更新：@Brian - 据我所知，我没有明确设置值，这是添加到表中的代码。
//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();
}
}
}
回答by marc_s
It would seem that your code is trying to insert a specific value into the primary key column that is defined as IDENTITY.
您的代码似乎试图将特定值插入定义为 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:
如果您使用的是 Linq-to-SQL，请在您的可视化设计器中单击相关表，并查看属性：
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).
“自动生成的值”必须是“真”（这不是默认值），自动同步应该是“OnInsert”（同样：不是默认值）。此外，将“主键”设置为 true，并检查以确保“服务器数据类型”是正确的 - Int NOT NULL 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:
如果您需要覆盖默认行为（通常仅在清理脚本或一次性数据操作中），您可以打开表上的 IDENTITY_INSERT，例如之后，您可以将您喜欢的任何值插入到 IDENTITY 列中：
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.
这更像是一种清理/数据管理类任务 - 作为开发人员，您不应该一直做这些事情。
回答by Brian Mains
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.
您正在尝试可能在 LINQ 实体中显式设置主键值；例如，您想说的是，在作为身份的主键字段中插入 1，而 LINQ 将其视为插入的值。
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.
另外如果使用LINQ to SQL，在字段的服务器数据类型中，确保它在数据类型字段中显示为IDENTITY；如果它没有说 IDENTITY，则可能是 LINQ 中的配置错误。
回答by kingrichard2005
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.
感谢大家的建议，我最终通过刷新模型解决了我的问题。我的模型是在我将主键设置为具有自动增量的 Identity 规范之前创建的，所以我想它需要的只是刷新以使其运行。
回答by Vrushali Mayee
If your are using LinQ to SQL.just remove your table from .edmx file by right clicking on table and selecting "Remove from Model" Option. and then Update your .edmx file by right clicking on .edmx file and selecting "Update Model from Database..." option.
如果您正在使用 LinQ to SQL。只需通过右键单击表并选择“从模型中删除”选项从 .edmx 文件中删除您的表。然后通过右键单击 .edmx 文件并选择“从数据库更新模型...”选项来更新您的 .edmx 文件。
Its Work For me..Try it..
它对我有用..试试吧..
回答by Jonesopolis
I had this issue and solved it by removing the identity column from the model. That cleared it right up
我遇到了这个问题并通过从模型中删除标识列来解决它。这清除了它
