An explicit value for the identity column in table ‘Table’ can only be specified when a column list is used and IDENTITY_INSERT is ON

Does someone know what this error
(An explicit value for the identity column in table ‘HD_AANVRAAG_FASE’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table ‘HD_AANVRAAG_FASE’ can only be specified when a column list is used and IDENTITY_INSERT is ON.) means?
Any help is appreciated.

private void Insert2()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HELPDESK_OUTLOOKConnectionString3"].ToString());

        conn.Open();
        SqlCommand dCmd2 = new SqlCommand(
            "INSERT INTO HD_AANVRAAG_FASE VALUES (@fase_id, @aanvraag_id, @status_id, "
                + "@werknemer_id, @fase_datum) SET IDENTITY_INSERT HD_AANVRAAG_FASE OFF ",
            conn);



        dCmd2.Parameters.AddWithValue("@fase_id", 1);
        dCmd2.Parameters.AddWithValue("@aanvraag_id", 2622);
        dCmd2.Parameters.AddWithValue("@status_id", 15);
        dCmd2.Parameters.AddWithValue("@werknemer_id", 165);
        dCmd2.Parameters.AddWithValue("@fase_datum", "12-12-2001");
        dCmd2.ExecuteNonQuery();
        conn.Close();
    }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Since you aren’t specifying the columns explicitly, I assume @fase_id is being passed into an IDENTITY column, which as the error indicates you can’t do unless you force it via IDENTITY_INSERT.

Usually, you let the DB generate this; specify the columns in the INSERT and omit the identity column (and don’t try to assign a value). The straight after the INSERT your new id is available as SCOPE_IDENTITY().

Method 2

Your code contains

SET IDENTITY_INSERT HD_AANVRAAG_FASE OFF at the end

But not a corresponding

SET IDENTITY_INSERT HD_AANVRAAG_FASE ON at the start

why are you explicitly inserting these any way? Is it a synchronization task?

Method 3

You’re missing a

SET IDENTITY_INSERT HD_AANVRAAG_FASE ON

before you run your insert. That’s the exact cause of the error.

That said, inserting identity values explicitly is rare– for example, used only when copying data from one table to another, or initializing a new table with explicit IDs. Usually you’ll just want to omit identity values from your INSERT, which also avoids the error.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0