Cannot insert explicit value for identity column in table

Question

Code:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AddUpdateCustomer]
      @CustomerID NCHAR(5),
      @ContactName NVARCHAR(30),
      @CompanyName NVARCHAR(40)
AS
BEGIN
      SET NOCOUNT ON;
    IF EXISTS(SELECT * FROM Customers WHERE CustomerID = @CustomerID)
    BEGIN
            UPDATE [Customers]
            SET [CompanyName] = @CompanyName
               ,[ContactName] = @ContactName
            WHERE CustomerID = @CustomerID
    END
    ELSE
    BEGIN
            INSERT INTO [Customers]
           ([CustomerID]
           ,[CompanyName]
           ,[ContactName])
        VALUES
           (@CustomerID
           ,@CompanyName
           ,@ContactName)
    END

    SELECT [CustomerID]
          ,[CompanyName]
          ,[ContactName]
      FROM Customers         
END

When I am inserting data in textbox this error occurs:

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

So what exactly is your question? You can either set IDENTITY_INSERT to ON, or not insert the ID's. — HoneyBadger
CustomerID is not an nchar if it is an identity column. Do you know what "identity" means? — Crowcoder

Mikhail Lobanov Mikhail Lobanov · Accepted Answer · 2017-05-17T10:08:54

ALTER PROCEDURE [dbo].[AddUpdateCustomer]
      @CustomerID  INT           OUTPUT,
      @ContactName NVARCHAR(30),
      @CompanyName NVARCHAR(40)
AS
BEGIN
      SET NOCOUNT ON;
    IF EXISTS(SELECT * FROM Customers WHERE CustomerID = @CustomerID)
    BEGIN
            UPDATE [Customers]
            SET [CompanyName] = @CompanyName
               ,[ContactName] = @ContactName
            WHERE CustomerID = @CustomerID
    END
    ELSE
    BEGIN
        INSERT INTO [Customers]
           ([CompanyName]
           ,[ContactName])
        VALUES
           (@CompanyName
           ,@ContactName)
        SET @CustomerID = SCOPE_IDENTITY()
    END

    SELECT [CustomerID]
          ,[CompanyName]
          ,[ContactName]
    FROM Customers  
    WHERE [CustomerID] = @CustomerID
END

Cannot insert explicit value for identity column in table

2 Answers