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

13 posts, 0 answers
  1. Alec
    Alec avatar
    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

  2. PetarP
    Admin
    PetarP avatar
    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.
  3. DevCraft banner
  4. tmlipinski
    tmlipinski avatar
    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:

    <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

     

     

     

  5. Daniel
    Daniel avatar
    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.
  6. Kristian Nikolov
    Admin
    Kristian Nikolov avatar
    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.
  7. Daniel
    Daniel avatar
    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





  8. Kristian Nikolov
    Admin
    Kristian Nikolov avatar
    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.
     
  9. Bill Noble
    Bill Noble avatar
    22 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



  10. Bill Noble
    Bill Noble avatar
    22 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
  11. Bill Noble
    Bill Noble avatar
    22 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

  12. Doroteya
    Admin
    Doroteya avatar
    502 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.
     
  13. Michael
    Michael avatar
    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!






     

  14. Doroteya
    Admin
    Doroteya avatar
    502 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.
     
Back to Top
DevCraft banner