-1

I am designing web application when I click save it shows the error Cannot insert explicit value for identity column in table 'StatusChange' when IDENTITY_INSERT is set to OFF.please anyone help me to fix this. Below is the code for status change.

avi
  • 83
  • 1
  • 3
  • 19
  • Perhaps showing us the code for saving instead of loading data would be more appropriate. – MrGadget Feb 25 '16 at 04:48
  • Hi I have updated SQL kindly check – avi Feb 25 '16 at 06:55
  • 1
    You're missing all of the code we can use to help. The error is because your code is trying to insert an explicit value into a column that is an `identity` column. An `identity` column generates it's own values so it won't let you insert values into them. Somewhere in the code (you have not posted), you are trying to insert a value into it. The column is probably `RegistrationID`. You need to 1. Work out which column is the identity column in the table; 2. Post the code that is actually inserting. – Nick.Mc Feb 25 '16 at 07:47
  • Are you using Entity Framework and LINQ or are you just throwing SQL strings at the database. Find the ASP code that has a SQL string with an INSERT in it and post it – Nick.Mc Feb 25 '16 at 07:48
  • Hi Nick what code you want to help me – avi Feb 25 '16 at 08:15
  • OK so we have established that the identity column is `ID`. The web code you have posted is `SELECT`. We need `INSERT` or if it is in LINQ we need the LINQ code. – Nick.Mc Feb 26 '16 at 13:57
  • Hi Nick I have changed the code now doesn't show any error but not saving in the database. – avi Feb 26 '16 at 14:03

1 Answers1

0

You can check table identity before to save as below

select is_identity from sys.columns where object_id = OBJECT_ID('[dbo].[StatusChange]', 'U') and name = 'column_Name'
Anjan Kant
  • 4,090
  • 41
  • 39
  • Hi Anjan when I ececute the above query the column is blank doesn't have any value what to add to fix my bug? – avi Feb 25 '16 at 08:14
  • You can set on identity seed of the table in database. here is code SET IDENTITY_INSERT StatusChange ON , here is more concept https://msdn.microsoft.com/en-us/library/ms188059.aspx – Anjan Kant Feb 25 '16 at 10:09
  • one more link which explaining http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server – Anjan Kant Feb 25 '16 at 10:11
  • write this sql statement directly in query analyzer to set on identity seed ALTER TABLE [dbo].[StatusChange] ADD TableID INT IDENTITY(1,1) NOT NULL – Anjan Kant Feb 25 '16 at 10:14
  • Hi what you said its already there in table do you want me to change anything – avi Feb 26 '16 at 03:10
  • yes, can you check identity see is on or not if not then on it. – Anjan Kant Feb 26 '16 at 05:53