Insert Fails with Auto-Incrementing ID (Cannot insert value null into column 'id')

Question

I'm encountering an "insert fails" error when trying to create new gig records. The error message is:

Cannot insert value null into column 'id', table 'GigHub.dbo.Gigs'; column does not allow nulls. INSERT fails. The system has been terminated.

I'm developing a campus gig finder application for a final term project. I have a Gigs table with an id column that I intended to be auto-incrementing. Here's the relevant table structure:

CREATE TABLE dbo.Gigs 
(
    id INT PRIMARY KEY,
    [title] NVARCHAR(MAX) NOT NULL,
    [description] NVARCHAR(MAX) NOT NULL,
    [location] NVARCHAR(MAX) NOT NULL,
    [gigType] INT NOT NULL,
    [startDate] DATETIME NOT NULL,
    [endDate] DATETIME NOT NULL,
    [rate] DECIMAL(10, 2) NOT NULL,
    [gigStatus] INT NOT NULL,
    [createdDate] DATETIME NOT NULL,
    [requirements] NVARCHAR(MAX),
     [gigPoser_id] INT FOREIGN KEY REFERENCES dbo.Users(id) --this holds the id of the user that posted the gig
);

I attempted to fix the error by modifying the id column to be auto-incrementing using the following statement:

ALTER TABLE dbo.Gigs
    ALTER COLUMN id INT IDENTITY NOT NULL

I verified that the ALTER TABLE statement executed successfully in SQL Server Management Studio. My application code creates a new GigModel object with all relevant properties set before calling a method (CreateGig) that interacts with a stored procedure (sp_InsertGigs) to insert the gig data.

What could be causing the insert failure despite the id column being auto-incrementing? Are there any additional steps I need to take in my application code or stored procedure to handle the auto-incrementing ID correctly?

Please let me know if you need any further details about the application code or stored procedure logic. I'm using SQL Server 2022 and C# with Dapper with the database interaction.

Stored Procedure Logic:

ALTER PROCEDURE [dbo].[sp_InsertGigs_2] 
    @title nvarchar(255),
    @description ntext,
    @location nvarchar(1000), 
    @type nvarchar(355),
    @start_date date,
    @end_date date,
    @rate decimal(10,2),
    @status nvarchar(355),
    @gigPoster_id int,
    @date_created datetime,
    @skills_required nvarchar(MAX),     

    @id int output
    AS
    BEGIN
        
        SET NOCOUNT ON;
        SET IDENTITY_INSERT dbo.Gigs ON;

        insert into dbo.Gigs(id, title, description, location, type, start_date, end_date, rate, status, gigPoster_id, date_created, skills_required)
        values(@id, @title, @description, @location, @type, @start_date, @end_date, @rate, @status, @gigPoster_id, @date_created, @skills_required);

        SET IDENTITY_INSERT dbo.Gigs OFF

        select @id = SCOPE_IDENTITY();      
    END

Answer 1

You are setting IDENTITY_INSERT ON in your stored procedure. Once you do this, you have to provide a value for @id . But as your @id is an output variable, it doesn't have a value yet, when you run INSERT thus the error.

Remove the SET IDENTITY_INSERT dbo.Gigs statements in your stored procedure and remove the id column from your INSERT statement

-- Don't turn on IDENTITY_INSERT!
-- SET IDENTITY_INSERT dbo.Gigs ON;

insert into dbo.Gigs(title, description, location, type, start_date, end_date, rate, status, gigPoster_id, date_created, skills_required)
values( @title, @description, @location, @type, @start_date, @end_date, @rate, @status, @gigPoster_id, @date_created, @skills_required);

-- SET IDENTITY_INSERT dbo.Gigs OFF;

Because, that's the whole idea of IDENTITY_COLUMNS. You don't need (and you are not allowed) to provide them (or a value for them) in INSERT statement.