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

    Question

  • Hopefully this is an easy question.

    I created a new project and connected to existing SQL Server 2008 R2 instance and imported a table.

    I have the SQL table set as identity for the Id column.   Using SQL insert scripts I can add records and have the Id auto-increments.

    However, in my LS app I get this error:

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

    Is there anything in the SQLEXPRESS instance that would impact this approach?

    Any suggestions?

     

    Tony

     

     

     

     

    Friday, July 29, 2011 3:20 AM

Answers

  • Problem solved -

    Turns out that any config/design changes that you do on the SQL Server side you will have to refresh the data source in Lightswitch to ensure that the local metadata is current.

    This solved my problem.  

    I think I originally forgot the set the is_identity to Yes is SQL Server and then corrected it after I had already imported the table.   Doing the refresh fixed this.

    I knew it had to be something easy to address.  It would be good to have LS provide some visual indicators for external data table state info for is_identity setting so if you catch this within LS you'll know to do the refresh.  

     

    Tony

     

     

    Friday, July 29, 2011 3:57 PM

All replies

  • http://msdn.microsoft.com/en-us/library/ms188059.aspx

    This happens when you are trying to insert an identity value, by default you should be letting the database assign those values, if you need to bulk insert those you need to turn it off before you execute your transaction.


    http://www.virtualrealm.com.au - XNA Game Programming News and Resources from Downunder.
    Friday, July 29, 2011 3:37 AM
  • For each table, add this line before the insert lines in your script:

    SET IDENTITY_INSERT dbo.YourTableName ON;
    GO
    

    Then after each table's inserts:

    SET IDENTITY_INSERT dbo.YourTableName OFF;
    GO
    

     


    Yann

    (plus ça change, plus c'est la même chose!)

    Friday, July 29, 2011 5:58 AM
  • When I first got this error I searched and found information on the 'set identity_insert ....' and set this to ON for that table.

    However, that didn't solve the problem.   I think that either there is something I still need to config somewhere. Or,  I suspect there is an issue with using SQL Server 2008 R2.

    My next test will be to create the table within LS and SQLEXPRESS and see if I can get that to work.  

    Maybe a check list of steps to ensure are setup would be helpful - from both the SQL Server 2008 R2 side and LS side.

     

    Tony

     

     

     

     

     

     

    Friday, July 29, 2011 12:43 PM
  • One more test to share -

    I can recreate that error message within SQL Server 2008 R2 by doing  -

    1.) setup table design to IS_IDENTITY = Yes

    2.) Insert statement:

    Insert into [LightswitchDemo].[dbo].[Business]
    (id,name) values (4,'SSSSSSSSSSSSSSSSSS')
    Go

    Here is the reproduced error message:

    Msg 544, Level 16, State 1, Line 1
     
    Cannot insert explicit value for identity column in table 'Business' when IDENTITY_INSERT is set to OFF.

    Seems to me that LS is sending a value for the Id to SQL Server 2008 R2.   Is there a way to set the Id field properties within VS designer to set a column as identity enabled and let the server auto increment.

    Thoughts/Comments?

    Tony

     

     

    Friday, July 29, 2011 1:10 PM
  • You didn't surround your statements in your script, as described above:

     

    SET IDENTITY_INSERT dbo.Business ON;
    GO
    

    Insert into [LightswitchDemo].[dbo].[Business]

    (id,name) values (4,'SSSSSSSSSSSSSSSSSS')
    Go

      SET IDENTITY_INSERT dbo.Business OFF; 

    GO

     

     

     

     


    Yann

    (plus ça change, plus c'est la même chose!)

    • Proposed as answer by PlusOrMinus Friday, July 29, 2011 3:35 PM
    Friday, July 29, 2011 2:01 PM
  • Problem solved -

    Turns out that any config/design changes that you do on the SQL Server side you will have to refresh the data source in Lightswitch to ensure that the local metadata is current.

    This solved my problem.  

    I think I originally forgot the set the is_identity to Yes is SQL Server and then corrected it after I had already imported the table.   Doing the refresh fixed this.

    I knew it had to be something easy to address.  It would be good to have LS provide some visual indicators for external data table state info for is_identity setting so if you catch this within LS you'll know to do the refresh.  

     

    Tony

     

     

    Friday, July 29, 2011 3:57 PM