I need to migrate some data from an old table to a new table. The old table and new table both have an auto incremented identity field as the primary key and I need to ensure the values remain the same in the new table.

I have a business object for the old and new and have written the conversion program but when I save the new business object I get a error message 'Cannot insert explicit value for identity column in table 'ContactNew' when IDENTITY_INSERT is set to OFF.'

I tried setting the IDENTITY_INDENTITY ON before the save and IDENTITY_INSERT OFF after the same but I still get the error.

loCommand.CommandText = "SET IDENTITY_INSERT " & Me.TableNameAndSchema & " ON"

Me.ExecuteNonQuery(loCommand)

Any ideas how to solve this issue?

 

Thanks,

Tim

 

you probably need to add the SET IDENTITY_INSERT ON and OFF to your main batch:







I haven't tried this, but it should work.

Greg is correct. The reason is this. When you call the ExecuteNonQuery, the connection session is only open for that one call. So when you set the IDENTITY_INSERT ON, execute, then execute the command. The IDENTITY_INSERT is back to off.



When I do this type of thing I generally like to create a stored procedure as it makes it a little easier.