You cannot insert an explicit value for an identity column in a table when IDENTITY_INSERT is set to OFF

{"Cannot insert an explicit value to an identity column in table ' OrderList ' when IDENTITY_INSERT is set to OFF}

For this exception can be handled from two angles: A: Database execution Statement B: Directly modify the persistent class-map profile ID node in NHibernate

A database execution statement:

Problem Description: When the primary key of the database table is designed as (Orderid int identity primary key), relative to the primary key Identity_insert the default setting is off, that is, the value of the Insert primary key ID is not displayed, as in the example below:

Insert into OrderList (id,ordername) VALUES (4520, ' Electronic summons order ')

Executing the above statement will prompt an error:

Server: Msg 544, Level 16, State 1, line 1
When IDENTITY_INSERT is set to OFF, you cannot insert an explicit value into the identity column in table ' OrderList '.

One record of the primary key, when we want to set the ID of this record to our custom 4520 when the above error occurs, if we add some settings, modify the method as follows:

--allows explicit values to be inserted into the identity column of the table on-allow off-not allowed
SET IDENTITY_INSERT orderlist on--Open

Insert into OrderList (id,ordername,createdate)
VALUES (4520, ' Set ', GETDATE ())

SET Identity_insert orderlist off--off

There is one more setting when executing this INSERT statement, and the syntax for this setting is

--Set Syntax:

SET Identity_insert [database.[owner.] {table} {ON | OFF}
Allows explicit values to be inserted into the table's identity column

Parameter description:
Database: For Databases
Table: For a sheet

On: Allow insertion of an explicit value Insert identity column
OFF: Not allowed

Attention:

--Problem Note

(1) At any time, the Identity_insert property of only one table in a session can be set to ON. If a table has this property set to ON and a set IDENTITY_INSERT ON statement is issued for another table, Microsoft®sql Server™ returns an error message stating that set IDENTITY_INSERT is set to ON and reports Table for which this property has been set to on

(2) If the insertion value is greater than the current identity value of the table, SQL Server automatically uses the newly inserted value as the current identity value

(3) Set IDENTITY_INSERT settings are set at execution or runtime, not at parse time

The above execution statement: The statement to be executed before and after adding the setting, of course, the above for a record insert operation, after this record insert operation, if the data is inserted again when the setting is not enabled, OrderID primary key column According to the above custom represents 4520, Automatically grows to 4521. This is a very flexible operation. The key to see how the individual uses.

B: Modify the configuration file

We deal with this problem by directly modifying the configuration file.

File configuration when error occurs:

--hibernate Persistence class configuration Note ID in the Generator child node setting the Class property is: Assigned autogrow
<class name= "Testhibernateexpre.entities.orderlistmodel,testhibernateexpre" table= "OrderList" >
<id name= "Orderid" column= "id" type= "int" >
< setting of parameters in!--ID q:native/assigned/foreign/increment-->
<generator class= "Assigned" ></generator>
</id>
</class>

Modified file configuration:

Configuration of the persistence class in--hibernate note ID The Generator child node class property is configured to native
<class name= "Testhibernateexpre.entities.orderlistmodel,testhibernateexpre" table= "OrderList" >
<id name= "Orderid" column= "id" type= "int" >
< setting of parameters in!--ID q:native/assigned/foreign/increment-->
<generator class= "native" ></generator>
</id>
</class> turn from: http://blog.sina.com.cn/s/blog_7eecafbc0100rax7.html to: http://www.cnblogs.com/chenkai/archive /2009/04/13/1434606.html

You cannot insert an explicit value for an identity column in a table when IDENTITY_INSERT is set to OFF