c# - 无法为表中的标识列插入显式值

Question

代码:

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

当我在文本框中插入数据时出现此错误:

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

Answer 1

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