[Solved] Unable to insert into table with Identity Column

by


The error message seems pretty clear: FactorId is an identity column. You shouldn’t set the value for FactorID yourself. Sql Server will set it for you. But if you really want to set it for some insane reason, you need to include a column list in the query like this:

SET IDENTITY_INSERT facetors on
    INSERT INTO Factor 
        (FactorID, CustomerID, Number, TotalPrice, PaidPrice, Date)
    VALUES 
        (@FactorID, @CustomersID, @Number, @TotalPrice, @PaidPrice,@Date)
SET IDENTITY_INSERT factors off

Even better, you should do something more like this, where you don’t have to mess with identity insert issues:

create proc insertfactors_pf
(
    @CustomersID int,
    @Number int,
    @TotalPrice decimal(18, 0),
    @PaidPrice decimal(18, 0),
    @Date Date,
    @ProductID int,
    @QTY int
)
AS

--Move this to inside the procedure definition. Don't ask for it as an argument
DECLARE @FactorID int

BEGIN TRANSACTION

--Don't mention FactorID anywhere here. Sql Server will take care of it
INSERT INTO Factor
    (CustomersID, Number, TotalPrice, PaidPrice, Date)
VALUES 
    (@CustomersID, @Number, @TotalPrice, @PaidPrice,@Date);

IF @@ERROR <> 0
BEGIN 
    ROLLBACK
    RETURN
END

--use scope_idenity() to get the FactorID value Sql Server just created
SELECT @FactorID = scope_identity();

INSERT INTO Produc_Factor 
    (FactorID, ProductID, Qty)
VALUES 
    (@FactorID,@ProductID,@QTY)

IF @@ERROR <> 0
BEGIN
    ROLLBACK
    RETURN
END

COMMIT

1

solved Unable to insert into table with Identity Column