Execute Non Query: Cannot insert explicit value for identity column when IDENTITY_INSERT is set to OFF

Help Activities
, ,
1

Hello Guys,

I am trying to execute non-query for a SQL table.

This error message I am seeing :

Execute Non Query: Cannot insert explicit value for identity column in table 'Sample_Table_Name' when IDENTITY_INSERT is set to OFF.

Can anyone help me understand what could be the cause of it?? it for my client project, any quick help is much appreciated.

Thanks & Regards :smiley:

2

@Rajesh_Shet

This is because you are passing some value in a column which is set as identity (auto-increment).

First remove Auto-increment to that column then try your SQL query.

3

Thanks lot for your quick response @lakshman , there can not be any change in the type of column, as it was built by client, If I want to let that column work in auto-increment, how should I pass the query? is there a specific syntax to it, I am getting started with SQL, so not sure about how it works.

Thanks

4

@Rajesh_Shet

You can pass values to other columns except Column which is set as Auto-increment.

Can you please share the query here. So that I can check and help you.

5

Its a normal insert into query. I am passing values using Parameters from variable.

6

@Rajesh_Shet

Let’s say your table is created with this kind of schema.

CREATE TABLE Persons ( Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (Personid) );

To insert new records into Persons Table.

INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen');

Here, we are skipping to pass values to Personid column as it was set as Auto_Increment.

7

Thanks buddy, I will try it.

8

@Rajesh_Shet

Sure. Please check and let me know if you face any issues.

1 Like
9

Hey @lakshman

It worked when I used this at the beginning

SET identity_insert [dbo].[Sample_Table_Name] ON

Found this video useful: Insert value in Identity column in SQL server - YouTube

Thanks for your help too! I got clarity from it.

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.