Cannot insert explicit value for identity column in table 'EmailSignature' when IDENTITY_INSERT is set to OFF. in Data Access, formerly OpenAccess ORM General Discussions

Alec

43 posts

Member since:
Mar 2009

Posted 05 Apr 2009 Link to this post

When I try to add a new object to a particular table it shows this:

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

I don't know only this particular class has this problem. Others are just fine. I traced and the id is 0. Can someone help? Thanks

PetarP

Admin

754 posts

Posted 06 Apr 2009 Link to this post

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.

tmlipinski

131 posts

Member since:
Dec 2006

Posted 19 Nov 2009 Link to this post

Hi,

I think that Alec's problem was that there was no:

statement in his config file. Note that the id was 0 - no one tried to set it.

Regards
Tomasz

Daniel

7 posts

Member since:
May 2013

Posted 03 Mar 2014 in reply to tmlipinski Link to this post

For some strange reason the automatic generated file did not generate

[KeyGenerator(KeyGenerator.Autoinc)]

for this table, but it did for all the tables.

Adding it to the generated file solved my problem.

Kristian Nikolov

Admin

206 posts

Posted 05 Mar 2014 Link to this post

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.

Daniel

7 posts

Member since:
May 2013

Posted 04 Nov 2014 in reply to Kristian Nikolov Link to this post

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

Kristian Nikolov

Admin

206 posts

Posted 06 Nov 2014 Link to this post

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.

Bill Noble

25 posts

Member since:
Sep 2003

Posted 13 Feb 2015 Link to this post

Hello

I have just come across this same issue.
I have created a VERY BASIC database with just one table.
The table has two fields "id" & "description"
My connection string is to MS SQL 2008r2 "sa" "password"
I used a grid and created a "new datasource", and enabled allow auto inserts, updates etc.
When I click "Add new record", i get the "IDENTITY_INSERT" error.
I have used this same method for many years, but now it has just stopped working. ?
Best regards
Bill

Bill Noble

25 posts

Member since:
Sep 2003

Posted 14 Feb 2015 Link to this post

More information

I have installed a new instance of SQL 2008R2 and created a new test database with a single table.
I receive the same IDENTITY_INSERT error when using a grid to insert a record.

I have also connected to a remote SQL 2005 server and receive the same error.

I have re-installed VS 2010 and created a new project, but the same error occurs.

The common factor is Telerik Grid.
The version I am using is 2014.2.724.40

Please help
Best regards
Bill

Bill Noble

25 posts

Member since:
Sep 2003

Posted 15 Feb 2015 Link to this post

FIXED

For the benefit of others.

I don’t know what/how, but something happened to the SQL Data Control which was causing the error.
I Re-Set my Visual Studio to factory defaults.
I Created a new project and dragged a data control onto the design surface.
I created a New connection string, and checked for every option setting.
I applied the control to a new grid, and then everything started working again.

Mystery. !

Best regards
Bill

Doroteya

Admin

498 posts

Posted 18 Feb 2015 Link to this post

Hi Bill,

It seems that currently things on you side are working.

Regarding the error, let me confirm that from Telerik Data Access perspective, the post provided by Petar and Kristian in this thread (here, here, and here) are relevant.

If you experience any Data Access issues, do not hesitate to get back to us.

Regards,
Doroteya
Telerik

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

Michael

2 posts

Member since:
Oct 2011

Posted 16 Apr 2015 Link to this post

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!

Doroteya

Admin

498 posts

Posted 21 Apr 2015 Link to this post

Hi Michael,

Indeed the initial setup you describe can lead to the particular error, and the suggested solution is a valid one.

Thank you for the time and effort on your side.

Regards,
Doroteya
Telerik

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