如何纠正错误"IDENTITY

本文介绍了如何纠正错误"IDENTITY_INSERT设置为OFF"；的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 在我的asp项目中，我使用的是Visual Studio安装的模板-> sql服务器数据库.在它身份属性被设置为关闭，我不知道为什么，但我试图将其设置为打开，但我不能.所以我直接在表的列上设置了identity属性.我是说这样

创建 表 cateo ( 类别ID int 身份( 1 ， 1 )主要 键， 类别名称 varchar ( 30 ) )

但是我正在解决这个错误

当IDENTITY_INSERT设置为OFF时，无法为表'cateo'中的身份列插入显式值."

如何纠正此错误

解决方案

在尝试将数据插入标识字段时发生该错误 sqlserverplanet/sql-server/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity_insert-is-set-to-off/ [ ^ ] 您尚未发布的某些代码中的其他位置，您正在执行此操作...

INSERT INTO cateo(类别，类别名称) VALUES ( 1 ，' 我的名字" )

...或类似的规定.一旦字段成为身份，数据库将生成cateid字段，因此您不应尝试将其插入该字段.您的sql应该更像...

INSERT INTO cateo(类别名称) VALUES (' 我的名字') 声明 @ NewID INT 选择 @ NewId = SCOPE_IDENTITY ()- 您的新数据库生成的ID

通常将取消身份插入.您指定为身份的列将自动增加.因此，您无法通过插入语句进行设置.如果要通过语句设置它，则必须将Identity_Insert设置为true. 您应该在插入(包括身份"列)之前执行此语句.

SET IDENTITY_INSERT ID 打开

这里的ID是应用身份的列的ID.

hi in my asp project i am using visual studio installed templates-->sql sever database . in it identity property was set to off ,i don`t know why but i tried to set it on but i could not. so i directly set on identity property on columns in my table . i mean to say like this

create table cateo ( cateid int identity(1,1) primary key, catename varchar(30) )

but i am geeting this error

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

how to rectify this error

解决方案

That error occurrs when you are trying to insert data into an identity field sqlserverplanet/sql-server/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity_insert-is-set-to-off/[^] Somewhere else in some code you haven''t posted, you are doing this...

INSERT INTO cateo (cateid, catename) VALUES (1, 'My Name')

...or something along those lines. Once a field is an identity, the database will generate the cateid field so you shouldn''t try to insert into that field. Your sql should be more like...

INSERT INTO cateo (catename) VALUES ('My Name') DECLARE @NewID INT SELECT @NewId = SCOPE_IDENTITY() --Your new database generated ID

Generally Identity Insert will be set off. The column, which you specify to be Identity, will be auto incremented. So, you cannot set it through your insert statement. If you want to set it through the statement, you have to set Identity_Insert to true. You should execute this statement before you insert(including Identity column).

SET IDENTITY_INSERT ID ON

Here ID id the column on which Identity is applied.