This is a migrated thread and some comments may be shown as answers.
Cannot insert explicit value for identity column in table 'EmailSignature' when IDENTITY_INSERT is set to OFF.
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.
Alec
Top achievements
Rank 1
Alec asked on 06 Apr 2009, 01:50 AM

 

 

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

12 Answers, 1 is accepted

Sort by
0
PetarP
Telerik team
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.
0
tmlipinski
Top achievements
Rank 1
answered on 19 Nov 2009, 02:22 PM
Hi,

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

<extension key="db-key-generator" value="AUTOINC" />

 

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

Regards
Tomasz

 

 

 

0
Daniel
Top achievements
Rank 1
answered on 03 Mar 2014, 11:26 AM
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.
0
Kristian Nikolov
Telerik team
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.
0
Daniel
Top achievements
Rank 1
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





0
Kristian Nikolov
Telerik team
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.
 
0
Bill Noble
Top achievements
Rank 1
answered on 13 Feb 2015, 04:39 PM
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



0
Bill Noble
Top achievements
Rank 1
answered on 14 Feb 2015, 12:54 PM
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
0
Bill Noble
Top achievements
Rank 1
answered on 15 Feb 2015, 12:32 PM
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

0
Doroteya
Telerik team
answered on 18 Feb 2015, 12:14 PM
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.
 
0
Michael
Top achievements
Rank 1
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!






 

0
Doroteya
Telerik team
answered on 21 Apr 2015, 05:20 PM
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.
 
Tags
General Discussions
Asked by
Alec
Top achievements
Rank 1
Answers by
PetarP
Telerik team
tmlipinski
Top achievements
Rank 1
Daniel
Top achievements
Rank 1
Kristian Nikolov
Telerik team
Bill Noble
Top achievements
Rank 1
Doroteya
Telerik team
Michael
Top achievements
Rank 1
Share this question
or