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 c开发者_运维知识库olumn 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 picture

---

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.

---

can only be specified when a column list is used and IDENTITY_INSERT is ON.

You're forgetting the first condition: the column list. The column list is a (usually) optional element between the table name and values. You should specify it like:

INSERT INTO AirConditioner (ID, CategoricNumber, ...)
VALUES(@ID, @CategoricNumber, ...)

---

Do NOT use set identity insert on. If you have an identity, you should be letting SQL server decide what value to put in there.

ALTER PROCEDURE [dbo].[AddAirConditioner] @CategoricNumber int, @Name nvarchar(50),
@PicName nvarchar(100), @ShortDetails nvarchar(200), @Details nvarchar(2000), 
@Price int, @ImagePath nvarchar(500), @AirConditionerType nvarchar(50), @COP float, 
@BTU float, @ProdType nvarchar(20), @ProdIndex int 
AS   

INSERT INTO AirConditioner VALUES( @CategoricNumber, @Name, @PicName, 
@ShortDetails, @Details, @Price, @ImagePath, @AirConditionerType, @COP,
@BTU, @ProdType, @ProdIndex) 

If you need to get the ID back for using in child tables, then use scope_identity or the output clause. Look these up in Books online to see how to use.

---

Try placing

SET IDENTITY_INSERT [dbo].[AirConditioner] ON
GO

Before the alter procedure