Search code examples
asp.netsqlsql-serverstored-proceduresidentity-insert

Stored Procedure IDENTITY_INSERT

I'm recently change my data table, I remove column and add a new column that define as identity = True and identity seed = 1, identity increment = 1. When i tried to insert data to this table by STORE PROCEDURE i get this exception:

An explicit value for the identity column in table 'AirConditioner' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I saw that i need to add this lines: SET IDENTITY_INSERT [dbo].[AirConditioner] ON and finally OFF I added and its still throw an exception...

My store procedure is attached as a pictureenter image description here

Solution

  • SQL Server will take care of assigning a value to your identity column. You should remove @ID from the insert statement and remove the IDENTITY_INSERT statements.