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
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.
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.
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.
Its a normal insert into query. I am passing values using Parameters from variable.
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.
Thanks buddy, I will try it.
Sure. Please check and let me know if you face any issues.
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.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.