Cannot insert explicit value for identity column in table table when IDENTITY INSERT is set to OFF

Question

I have the below error when I execute the following script  What is the error about  and how it can be resolved   Insert table OperationID OpDescription FilterID  values  20  Hierachy Update  1    Error      Server  Msg 544  Level 16  State 1  Line 1      Cannot insert explicit value for identity column in table  table  when IDENTITY INSERT is set to OFF

User · Answer

If you are using liquibase to update your SQL Server  you are likely trying to insert a record  key into an autoIncrement field  By removing the column from the insert  your script should run     lt changeSet id  CREATE GROUP TABLE   gt       lt createTable tableName  GROUP D  gt           lt column name  GROUP ID  type  INTEGER  autoIncrement  true  gt               lt constraints primaryKey  true   gt           lt  column gt       lt  createTable gt   lt  changeSet gt    lt changeSet id  INSERT UNKNOWN GROUP   gt       lt insert tableName  GROUP D  gt                lt column name  GROUP ID  valueNumeric  -1   gt            lt  insert gt   lt  changeSet gt

User · Answer

I solved this problem by creating a new object every time I want to add anything to the database

User · Answer

The problem raised from using non-typed DBContext or DBSet if you using Interface and implement method of savechanges in a generic way  If this is your case I propose to strongly typed DBContex for example  MyDBContext MyEntity Add mynewObject    then  Savechanges will work

User · Answer

Another situation is to check that the Primary Key is the same name as with your classes where the only difference is that your primary key has an  ID  appended to it or to specify  Key  on primary keys that are not related to how the class is named

User · Answer

In my CASE I was inserting more character than defined in table  In My Table column was defined with nvarchar 3  and I was passing more than 3 characters and same ERROR message was coming   Its not answer but may be in some case problem is similar

User · Answer

Simply If you getting this error on SQL server then run this query-  SET IDENTITY INSERT tableName ON  This is working only for a single table of database e g  If the table name is student then query look like this  SET IDENTITY INSERT student ON   If you getting this error on your web application or you using entity framework then first run this query on SQL server and Update your entity model   edmx file   and build your project and this error will be resolved

User · Answer

Note that if you are closing each line with    the SET IDENTITY INSERT mytable ON command will not hold for the following lines     i e  a query like   SET IDENTITY INSERT mytable ON  INSERT INTO mytable  VoucherID  name  VALUES  1   Cole      Gives the error Cannot insert explicit value for identity column in table  mytable  when IDENTITY INSERT is set to OFF     But a query like this will work     SET IDENTITY INSERT mytable ON INSERT INTO mytable  VoucherID  name  VALUES  1   Cole   SET IDENTITY INSERT mytable OFF    It seems like the SET IDENTITY INSERT command only holds for a transaction  and the   will signify the end of a transaction

User · Answer

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

User · Answer

I m not sure what the use for the  Insert Table  is  but if you re just trying to insert some values try   Insert Into  tablename   OpDescription FilterID  values   Hierachy Update  1     I had the same error message come up  but I think this should work  The ID should auto-increment automatically as long as it s a primary key

User · Answer

If you re having this issue while using an sql-server with the sequelize-typescript npm make sure to add  AutoIncrement to ID column       PrimaryKey    AutoIncrement    Column   id   number

User · Answer

Be very wary of setting IDENTITY INSERT to ON  This is a poor practice unless the database is in maintenance mode and set to single user  This affects not only your insert  but those of anyone else trying to access the table    Why are you trying to put a value into an identity field

User · Answer

Simply delete the tables that are dragged into your  dbml file and re-drag them again  Then Clean solution Rebuild solution  Build solution   Thats what worked for me   I didnt made the table on my own  I was using VS and SSMS  I followed this link for ASP NET Identity https   docs microsoft com en-us aspnet identity overview getting-started adding-aspnet-identity-to-an-empty-or-existing-web-forms-project

User · Answer

The best solution is to use annotation GeneratedValue strategy         i e    Id  GeneratedValue strategy   GenerationType IDENTITY   Column     private int OperationID    it says  that this column is generated by database using IDENTITY strategy and you don t need to take care of - database will do it

User · Answer

don t put value to OperationID because it will be automatically generated  try this   Insert table OpDescription FilterID  values   Hierachy Update  1

User · Answer

everyone comment about SQL  but what hapend in EntityFramework  I spent reading the whole post and no one solved EF  So after a few days a found solution  EF Core  in the context to create the model there is an instruction like this  modelBuilder Entity lt Cliente gt  entity   gt    entity Property e   gt  e Id  ValueGeneratedNever    this produces the error too  solution  you have to change by ValueGeneratedOnAdd   and its works

User · Answer

In my case I was having set another property as key in context for my modelBuilder  modelBuilder Entity lt MyTable gt    HasKey t   gt  t OtherProp    I had to set the proper id  modelBuilder Entity lt MyTable gt    HasKey t   gt  t Id

User · Answer

In your entity for that table  add the DatabaseGenerated attribute above the column for which identity insert is set   Example    DatabaseGenerated DatabaseGeneratedOption Identity   public int TaskId   get  set

User · Answer

And if you are using Oracle SQL Developer to connect  remember to add  sqldev stmt    sqldev stmt  set identity insert TABLE on

User · Answer

There is pre-mentioned OperationId in your query which should not be there as it is auto increamented  Insert table OperationID OpDescription FilterID  values  20  Hierachy Update  1    so your query will be   Insert table OpDescription FilterID  values   Hierachy Update  1

User · Answer

you can simply use This statement for example if your table name is School  Before insertion make sure identity insert is set to  ON and after insert query turn identity insert  OFF  SET IDENTITY INSERT School ON      insert query   enter code here    SET IDENTITY INSERT School OFF

User · Answer

There are basically 2 different ways to INSERT records without having an error   1  When the IDENTITY INSERT is set OFF  The PRIMARY KEY  ID  MUST NOT BE PRESENT  2  When the IDENTITY INSERT is set ON  The PRIMARY KEY  ID  MUST BE PRESENT  As per the following example from the same Table created with an IDENTITY PRIMARY KEY   CREATE TABLE  dbo   Persons            ID INT IDENTITY 1 1  PRIMARY KEY      LastName VARCHAR 40  NOT NULL      FirstName VARCHAR 40       1  In the first example  you can insert new records into the table without getting an error when the IDENTITY INSERT is OFF  The PRIMARY KEY  ID  MUST NOT BE PRESENT from the  INSERT INTO  Statements and a unique ID value will be added automatically    If the ID is present from the INSERT in this case  you will get the error  Cannot insert explicit value for identify column in table      SET IDENTITY INSERT  dbo   Persons  OFF  INSERT INTO  dbo   Persons   FirstName LastName  VALUES   JANE   DOE     INSERT INTO Persons  FirstName LastName   VALUES   JOE   BROWN      OUTPUT of TABLE  dbo   Persons  will be   ID    LastName   FirstName 1     DOE        Jane 2     BROWN      JOE   2  In the Second example  you can insert new records into the table without getting an error when the IDENTITY INSERT is ON  The PRIMARY KEY  ID  MUST BE PRESENT from the  INSERT INTO  Statements as long as the ID value does not already exist  If the ID is NOT present from the INSERT in this case  you will get the error  Explicit value must be specified for identity column table      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       OUTPUT of TABLE  dbo   Persons  will be   ID    LastName   FirstName 1     DOE        Jane 2     BROWN      JOE 3     BLACK      JACK 5     WHITE      JOHN

User · Answer

You re inserting values for OperationId that is an identity column   You can turn on identity insert on the table like this so that you can specify your own identity values   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

[sql] Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF

Examples related to sql

Examples related to sql-server

Examples related to sybase