none
Cannot insert explicit value for identity column

    Question

  • I am writing a program to import data across from one database to another. I need to be able to make it use the current ID numbers or else the relations will be broken.

    I keep getting:

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

    I have opened up SQL Server Management Studio created a new query with:

    SET IDENTITY_INSERT [BMSSUNRISE].[dbo].[SystemInventory] ON
    GO

    this results in success but the program still fails, I have tested the Insert SQL statement in the SQL Studio tool and included the above line in front of it and it works.

    MY QUESTION, how do I get VB.NET2005 to set this option on?

    I have tried to place that line above in the insert command of the table adapter but it comes up with an error saying that SET is not supported. There must be a way to be able to "enable" this using the dynamic classes that a generated.

    Any help is muchly appreciated, thankyou.

    Regards,

    Michael Proctor

    Monday, April 17, 2006 8:32 AM

Answers

  • Have you tried using an SqlCommand object, and executing the statement as SQL?

    There's an example of how to do this in the following article:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnhcvs04/html/vs04i1.asp

    Here's a code snippet from the article:

    dap1 = New SqlClient.SqlDataAdapter _
    ("SELECT ShipperID, CompanyName, Phone " & _
    "FROM Shippers", SqlConnection1)
    dap1.InsertCommand = New SqlClient.SqlCommand _
    ("SET IDENTITY_INSERT Shippers ON ")
    dap1.InsertCommand.CommandText &= _
    "INSERT INTO Shippers " & _
    "(ShipperID, CompanyName, Phone) " & _
    "VALUES (@ShipperID, @CompanyName, @Phone)"
    dap1.InsertCommand.Connection = SqlConnection1

    Dim prm1 As SqlClient.SqlParameter = _
    dap1.InsertCommand.Parameters.Add _
    ("@ShipperID", SqlDbType.Int)
    prm1.SourceColumn = "ShipperID"
    prm1.SourceVersion = DataRowVersion.Current
    Dim prm2 As SqlClient.SqlParameter = _
    dap1.InsertCommand.Parameters.Add _
    ("@CompanyName", SqlDbType.NVarChar, 40)
    prm2.SourceColumn = "CompanyName"
    Dim prm3 As SqlClient.SqlParameter = _
    dap1.InsertCommand.Parameters.Add _
    ("@Phone", SqlDbType.NVarChar, 24)
    prm3.SourceColumn = "Phone"


    Monday, April 17, 2006 9:33 AM
  • Thanks for your suggestions Christopher, I completed my project tonight YAY!

    In the end the opening a connection and running my SQL statement then the Update method works 100% of the time even on tables with over 200,000+ records so it seems that the connection does stay pinned up during the update method :)

    Funny you should say about the Upsizing Wizard, although I am sure it does a great job on some databases unfortunately it doesn't do so well on ours. It had major issues with dates fields not being of SQL type and also problems with Queries as i have VBA functions in them.

    In the end a custom program to transpose the data into SQL valid data seemed to be my only answer (however MSFT did suggest SSIS which I looked into and did trial, it would have worked also as you can customise it's transfer of data, however I had already completed over 50% of my project so just kept going)

    Pity Microsoft don't have a property or method to allow this simply from the TableAdapter, however I had an idea (if I had to do this again) which is to overload the Update Method and have an additional parameter of IDENTITYINSERT as boolean and if so open the connection pass the SQL state then run the MS Update method and close it up. Then you would have to worry about coding each time you run the Update.

    Anyways acheived what I needed and again thanks for your assistance.

    Friday, April 21, 2006 5:12 PM

All replies

  • Have you tried using an SqlCommand object, and executing the statement as SQL?

    There's an example of how to do this in the following article:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnhcvs04/html/vs04i1.asp

    Here's a code snippet from the article:

    dap1 = New SqlClient.SqlDataAdapter _
    ("SELECT ShipperID, CompanyName, Phone " & _
    "FROM Shippers", SqlConnection1)
    dap1.InsertCommand = New SqlClient.SqlCommand _
    ("SET IDENTITY_INSERT Shippers ON ")
    dap1.InsertCommand.CommandText &= _
    "INSERT INTO Shippers " & _
    "(ShipperID, CompanyName, Phone) " & _
    "VALUES (@ShipperID, @CompanyName, @Phone)"
    dap1.InsertCommand.Connection = SqlConnection1

    Dim prm1 As SqlClient.SqlParameter = _
    dap1.InsertCommand.Parameters.Add _
    ("@ShipperID", SqlDbType.Int)
    prm1.SourceColumn = "ShipperID"
    prm1.SourceVersion = DataRowVersion.Current
    Dim prm2 As SqlClient.SqlParameter = _
    dap1.InsertCommand.Parameters.Add _
    ("@CompanyName", SqlDbType.NVarChar, 40)
    prm2.SourceColumn = "CompanyName"
    Dim prm3 As SqlClient.SqlParameter = _
    dap1.InsertCommand.Parameters.Add _
    ("@Phone", SqlDbType.NVarChar, 24)
    prm3.SourceColumn = "Phone"


    Monday, April 17, 2006 9:33 AM
  • This is certainly an option but then why did Microsoft even bother with DataSources and the Dynamically created DataTable, TableAdapter classes.

    Surely there is a way to accomplish this without having to resort to manually recoding every command in the program we are talking over 80 tables with collectively over 700 fields.

    Thankyou for your suggestion

    Monday, April 17, 2006 10:01 AM
  • If the database that you are copying to is new (ie. you are not migrating to a pre-populated database), then you could consider removing the identity constraint from all tables (and necessarily temporarily remove integrity constraints). Then, perform your data import/conversion - you won't need to worry about identity values - these should all be fine, as they would have been validated by your previous version of SQL Server. Once this is complete, then you can re-enable identity values (and set the next identifier number using the DBCC CHECKIDENT command), and constraints.
    Monday, April 17, 2006 10:08 AM
  • I have found a bandaid for the moment.

    Dim sqlcmdSetIdentityInsert As SqlClient.SqlCommand = Me.MyTableTableAdapter.Connection.CreateCommand()

    If Not sqlcmdSetIdentityInsert.Connection.State = ConnectionState.Open Then sqlcmdSetIdentityInsert.Connection.Open()

    sqlcmdSetIdentityInsert.CommandText = "SET IDENTITY_INSERT SystemInventory ON"

    sqlcmdSetIdentityInsert.ExecuteNonQuery()

    Me.MyTableTableAdapter.Update(myNewRow)

    'while connection is still open perform the update, the previous SET command will still apply to this command

    If Not sqlcmdSetIdentityInsert.Connection.State = ConnectionState.Closed Then sqlcmdSetIdentityInsert.Connection.Close()

    It appears to work, I am not sure on what problems this may cause, performance wise seems alright, would still like to know if Microsoft have a "correct" solution to SET this parameter.

     

    Regards

    Monday, April 17, 2006 10:41 AM
  • Yep - that's right - and I'd wrap it up in a transaction too.
    Monday, April 17, 2006 10:45 AM
  •  damn, it works sometimes, it appears sometimes when calling the TableAdapter.Update method it refreshes the connection and my SET is lost :(

    Your suggestion about a transaction sounds good except can't seem to find a solution for using Transactions on TableAdapters therefore back at square 1.

    There must be a way surely!

     

    Monday, April 17, 2006 11:42 AM
  • Actually I take that back I found a "Typo"

    when I copy and pasted the code into various areas of my program I forgot that I used the CreateCommand() method of the tableadapter, so it was still creating commands based off another table and not the table I was trying to up, things are looking good again.

    Dim IdentityInsert As SqlClient.SqlCommand = Me.SystemInventoryTableAdapter.Connection.CreateCommand()

    Monday, April 17, 2006 11:47 AM
  • Right - so if you wrap all updates for a given table (ie. the range of the Set IDENTITY_INSERT statement) into one transaction, the effects of your SET statement shouldn't be lost.

    Is there any specific transformation work that you need to do, or would it be easier just to do it all using TSQL (ie. no .NET code)? Personally, I don't like using DataSets, Table Adapters or the like - I prefer to do the whole lot manually using SqlCommands and SQL statements.
    Monday, April 17, 2006 2:59 PM
  • This project has a 2 month deadline and is a VB.NET project being replicated from an existing Access forms application.

    I need/want to use Datasets, TableAdapters and so on for easy databinding to speed the development cycle.

    However you are right, this importer program could be manually coded but was trying to get into the swing of Datasets and TableAdapters as I am still coming to terms with it all (have mainly dealt with ADO recordsets and Jet in VB and ASP).

    Always more than one way to skin a cat! :)

    I have my program working at the moment by creating a new command from the tableadapter.connection.newcommand method then executing a nonscalar command before running the update on the tableadapter and it seems to do it all in one transaction.

    I will let you know how it goes in the final release

    Tuesday, April 18, 2006 1:33 AM
  • Good luck with it.

    By the way, there is an automatic upsizing wizard for Microsoft Access to port to SQL Server. From within Access, click Tools, click Database Utilities, and then click Upsizing Wizard.

    There's a whitepaper which describes this process in detail. Have a look here:
    http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

    This is for SQL 2000, but it should be similar for SQL 2005.

    Tuesday, April 18, 2006 2:51 AM
  • Thanks for your suggestions Christopher, I completed my project tonight YAY!

    In the end the opening a connection and running my SQL statement then the Update method works 100% of the time even on tables with over 200,000+ records so it seems that the connection does stay pinned up during the update method :)

    Funny you should say about the Upsizing Wizard, although I am sure it does a great job on some databases unfortunately it doesn't do so well on ours. It had major issues with dates fields not being of SQL type and also problems with Queries as i have VBA functions in them.

    In the end a custom program to transpose the data into SQL valid data seemed to be my only answer (however MSFT did suggest SSIS which I looked into and did trial, it would have worked also as you can customise it's transfer of data, however I had already completed over 50% of my project so just kept going)

    Pity Microsoft don't have a property or method to allow this simply from the TableAdapter, however I had an idea (if I had to do this again) which is to overload the Update Method and have an additional parameter of IDENTITYINSERT as boolean and if so open the connection pass the SQL state then run the MS Update method and close it up. Then you would have to worry about coding each time you run the Update.

    Anyways acheived what I needed and again thanks for your assistance.

    Friday, April 21, 2006 5:12 PM
  • No worries. Glad you made your project!

    I know the feeling of being half way through something, then having an 'easier' way suggested. Sometimes if you have something working, it's easier just to keep going with it.
    Saturday, April 22, 2006 7:27 AM
  • Hi,

     

    refer the below link for ur above posted query...

     

    http://support.microsoft.com/kb/878501

     

    Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

    This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

    Thanks,

    R.Saranya.

    Wednesday, June 25, 2008 10:44 AM
  • hi!

    it happened to me to have this error when dealing with linq to sql designer. in the dbml file, we had a version of the table, where an identity was not specified, but in the database, we had the new version of table, with identity and PK specification.

    the solution was to update the table in the linq to sql designer, by dragging and dropping.

    what might help you, would be, if you do not generate the tables dynamically, is to go to sql management studio, and choose design option for the table generating the problem, find the identity column, and set "Identity Specification" to yes.

    Regards,
    mircea

    http://mirceacimpoi.spaces.live.com
    Friday, June 27, 2008 7:04 PM
  • A great thanks, Michael.. You just help me out of my problem.
    Saturday, March 20, 2010 10:36 AM