Cannot insert explicit value for identity column in table 'EmailSignature' when IDENTITY_INSERT is set to OFF.

This is a migrated thread and some comments may be shown as answers.

12 Answers 268 Views

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

12 Answers, 1 is accepted

answered on 06 Apr 2009, 08:58 AM

Hello Alec,
The problem is that the SQL server does not allow by default manual insertion of identity values when another mechanism for auto-generation is used. This option can be enabled at runtime with the SQL "SET IDENTITY_INSERT Table ON" command. In order to do that with OpenAccess you can use a startup SQL script that is defined in the <backendconfiguration> section of the app.config file. Here is an example:

    
            <backendconfigurations>  
        
               <backendconfiguration id="mssqlConfiguration" backend="mssql">  
        
                  <mappingname>mssqlMapping</mappingname>  
        
                  <initSql>SET IDENTITY_INSERT EmailSignature ON</initSql>  
        
               </backendconfiguration>  
        
            </backendconfigurations>

Note that this option can be enabled for only one table at the same time. If your application requires this for more than one table, you will have to use another approach - with a stored procedure which enables this option before insertion of a record and another that disables it after that.

Sincerely yours,
PetarP
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

answered on 05 Mar 2014, 12:57 PM

Hello Daniel,

A possible reason for the missing attribute is that the Identity Mechanism property for the specific persistent class is not set to DatabaseServerCalculated.

Manually adding the attribute to the class will set the Identity Mechanism, but if you save your model, the class will be regenerated again without the attribute. To avoid this, you must set the identity mechanism from the properties window of the persistent class as shown in this article.

I hope this helps. If you have any more questions, feel free to use our forums again.

Regards,
Kristian Nikolov
Telerik

OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.

answered on 04 Nov 2014, 01:24 PM

Hey again!

We came across this problem again and as we don't use model first mapping we cannot make use of your suggestion. Perhaps you have another solution for this issue? The database is set up correctly and it seems as the object model itself renders one of the classes without the KeyGenerator attribute (this is the second time it happens in a year so its a rare situation).

We have solved it by removing the affected table and its autogenerated class from the model and updated the model from database. This works and lets us continue developing, but nevertheless it is a strange bug and a better solution than removing and updating would be appreciated.

Thank you!
/Daniel

answered on 06 Nov 2014, 02:51 PM

Hello Daniel,

We are glad you have resolved the situation on your side.

Regarding a solution to the problem - even if you are using a Database First approach it should be safe to manually specify the that identity property should be DatabaseServerCalculated if its respective column in the database is auto incremented. Doing so will resolve a discrepancy between your model and the mapped database that is already present. Therefore, generally the proposed solution should still be applicable.

As for the problem itself, we have tried to reproduce it but with no success. For this reason we are unable to confirm whether this behavior is a bug. Should you be able to consistently reproduce the issue, we would appreciate it if you provide us with the steps taken in doing so. This would allow us to investigate the situation further.

I hope this helps. If you have additional questions feel free to use our forums again.

Regards,
Kristian Nikolov
Telerik

OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.

answered on 16 Apr 2015, 04:42 PM

ANOTHER FIX!!!

SITUATION:
---------------------------
In my particular case I was getting this same error because I had a field other than than the primary key set as the Identity Field!

Pertinent fields from Table:

Fieldname: ProjectId (Primary Key)
Type: UniqueIdentifier
RowGuid: True

Fieldname: ClusterIndex
Type: Int
Identity Specification: Yes

It was all but impossible to tell from the huge exception but it was actually the attempt to write to the ClusterIndex field that was throwing the exception!

SOLUTION:
---------------------------

1) Open the rlinq file and in the designer click the offending field (ClusterIndex) in my case.

2) In the Visual Studio Properties Editor set "Kind" to: "PersistentReadOnly".

3) Save

That's it! Now this field will NOT attempt to be written to when adding/updating records - and thus no more exception!

Alternatively you could probably just remove the offending field from the entity model completely if you have no need for it in your code, etc.

Hope this helps someone else!