当我执行下面的脚本时,我有以下错误。错误是关于什么,如何解决?

Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)

错误:

服务器:Msg 544,级别16,状态1,线路1 当IDENTITY_INSERT设置为OFF时,无法为表'table'中的标识列插入显式值。


当前回答

只需删除拖放到.dbml文件中的表,然后重新拖动它们。清洁方案>重建方案>重建方案。

这对我很管用。

我没有做我自己的表格,我是用VS和SSMS,我遵循这个链接为ASP。净身份:https://learn.microsoft.com/en-us/aspnet/identity/overview/getting-started/adding-aspnet-identity-to-an-empty-or-existing-web-forms-project

其他回答

基本上有两种不同的方法来插入记录而不会出现错误:

1)当IDENTITY_INSERT设置为OFF时。主键“ID”不能出现

2)当IDENTITY_INSERT设置为ON时。主键“ID”必须存在

根据下面的例子,同一个表用IDENTITY主键创建:

CREATE TABLE [dbo].[Persons] (    
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(40) NOT NULL,
    FirstName VARCHAR(40)
);

1)在第一个例子中,当IDENTITY_INSERT是OFF时,你可以插入新的记录到表中而不会得到一个错误。主键“ID”不能出现在“INSERT INTO”语句中,一个唯一的ID值将被自动添加:。如果在这种情况下,ID从INSERT中出现,您将得到错误“不能为表中标识列插入显式值…”

SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE'); 
INSERT INTO Persons (FirstName,LastName) 
VALUES ('JOE','BROWN');

表[dbo]的输出。[人员]将是:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE

2)在第二个例子中,当IDENTITY_INSERT是ON的时候,你可以插入新的记录到表中而不会得到一个错误。只要ID值不存在,主键“ID”必须从“INSERT INTO”语句中出现:如果ID不存在,在这种情况下,你将得到错误“必须为标识列表指定显式值…”

SET IDENTITY_INSERT [dbo].[Persons] ON;
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (5,'JOHN','WHITE'); 
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (3,'JACK','BLACK'); 

表[dbo]的输出。[人员]将是:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE
3     BLACK      JACK
5     WHITE      JOHN

This occurs when you have a (Primary key) column that is not set to Is Identity to true in SQL and you don't pass explicit value thereof during insert. It will take the first row, then you wont be able to insert the second row, the error will pop up. This can be corrected by adding this line of code [DatabaseGenerated(DatabaseGeneratedOption.Identity)] in your PrimaryKey column and make sure its set to a data type int. If the column is the primary key and is set to IsIDentity to true in SQL there is no need for this line of code [DatabaseGenerated(DatabaseGeneratedOption.Identity)] this also occurs when u have a column that is not the primary key, in SQL that is set to Is Identity to true, and in your EF you did not add this line of code [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

您正在为OperationId插入值,这是一个标识列。

您可以像这样在表上打开标识插入,以便您可以指定自己的标识值。

SET IDENTITY_INSERT Table1 ON

INSERT INTO Table1
/*Note the column list is REQUIRED here, not optional*/
            (OperationID,
             OpDescription,
             FilterID)
VALUES      (20,
             'Hierachy Update',
             1)

SET IDENTITY_INSERT Table1 OFF 

我使用asp.net core 5.0,我得到这个错误。我得到这个错误,因为我添加了另一个数据并触发了另一个.SaveChanges()方法,如下所示:

 _unitOfWorkVval.RepositoryVariantValue.Create(variantValue);
 int request = HttpContext.Response.StatusCode;
 if (request == 200)
 {
     int tryCatch = _unitOfWorkCVar.Complete();
     if (tryCatch != 0)
     {
         productVariant.CategoryVariantID = variantValue.CategoryVariantID;
         productVariant.ProductID = variantValue.ProductID;
         productVariant.CreatedDate = DateTime.Now;
         _unitOfWorkProductVariant.RepositoryProductVariant.Create(productVariant);
         _unitOfWorkVval.RepositoryVariantValue.Create(variantValue);
         int request2 = HttpContext.Response.StatusCode;
         if(request==200)
         {
             int tryCatch2=_unitOfWorkProductVariant.Complete();//The point where i get that error
         }///.......

在我的例子中,问题是,当我试图更新记录时,我自己从代码中指定了自增ID。 在从新记录创建中删除ID属性之后,一切都正常了。