none
Identity Insert Off Error? RRS feed

  • Question

  • I am getting an error message that says, “Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.  This error occurs when there is an attempt to create a new row in a non-clustered table with a primary key that has an incremental identity and seed.  There was no error until I enabled the Identity Specification for the table.  But without it, another error occurs which states “Cannot insert duplicate key” when attempting to create a second row in the table.  I have been unsuccessful in finding where an IDENTITY_INSERT is set with the Server Explorer in Visual Studio.  Can anyone help me please?

    Maurice

    Saturday, April 6, 2013 1:21 AM

Answers

  • Dear Fernando,

    I have discovered why I was getting the “Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF” error.  I had failed to update the DataClasses.dbml files within Solution Explorer with the new altered Table data from Server Explorer.  It works fine now.  However, I am still very interested to know why I could not get App_Data in Solution Explorer of Visual Studio’s Web Developer to access the database created by SQL Server Management Studio.  Otherwise, I will continue to use the database I created with Server Explorer.  Thank you for your assistance.

    Maurice

    Saturday, April 6, 2013 8:24 PM

All replies

  • Hi Maurice;

    When creating a new record to be inserted do not set or modify the primary key of the new record because the database will generate a new key and update the record in the data context.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Saturday, April 6, 2013 2:41 AM
  • Hi Fernando,

    Thank you for responding.  The only thing that I set is another column other than the primary key.  The following is the C# code I use in a web page to create a new row in the OfficerTable object:

                OfficerTable officertable = new OfficerTable();

                officertable.OfficeTableID = ID;

                db.OfficerTables.InsertOnSubmit(officertable);

                db.SubmitChanges();

                long id = officertable.OfficerTableID;

     The table only contains the following:

    OfficerTableID               bigint

    OfficeTableID                 nchar(30)           Allow Nulls

    NextOfficerTableID         bigint                Allow Nulls

    Maurice

    Saturday, April 6, 2013 6:45 AM
  • Hi Maurice;

    I have created a table in SQL server with the info from your last post and I had no issues inserting a record. In the image below what value do you show in SQL Server Management Studio for the items associated with the node Identity Specification.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Saturday, April 6, 2013 1:53 PM
  • Dear Fernando,

    Thank you for responding once again.  I am glad that you had no issues when attempting to insert a new record in your table.  But, I am not surprised if your primary key has no identity specification.  I had no problem inserting the first record when I initially created the table with the primary key having no identity specification.  My problem occurred when I attempted to insert the second record.  Without an identity specification, there was no automatic incremental generation of the primary key bigint value and the second record was being created with the same value of zero as with the first record.  Since the primary key value was specified as being unique, there was a duplicate key in object table” error.

    Again, I am getting an error message that says, “Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF”.  I have been unsuccessful in finding where an IDENTITY_INSERT is set with the Server Explorer in Visual Studio.  Can you help me with this problem?

    I noticed that you requested what was stated in SQL Server Management Studio for the table’s primary key Identity Specification.  The Identity is set to Yes, the Identity Increment is 1 and the Identity Seed is 1.  However, I am using Visual Studio’s Server Explorer to create and examine tables, not SQL Server Management Studio.  I could not get the App_Code and App_Data in Solution Explorer of Visual Studio’s Web Developer to access the database created by SQL Server Management Studio.  So, I had to create the database using Server Explorer.  Where did I go wrong?

    Maurice

    Saturday, April 6, 2013 5:48 PM
  • Dear Fernando,

    I have discovered why I was getting the “Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF” error.  I had failed to update the DataClasses.dbml files within Solution Explorer with the new altered Table data from Server Explorer.  It works fine now.  However, I am still very interested to know why I could not get App_Data in Solution Explorer of Visual Studio’s Web Developer to access the database created by SQL Server Management Studio.  Otherwise, I will continue to use the database I created with Server Explorer.  Thank you for your assistance.

    Maurice

    Saturday, April 6, 2013 8:24 PM