Anders Hesselbom

Inserting a value in an identity column

2010-05-25

In SQL Server 2008, when a column is set to auto increment (Identity Specification), you are normally not allowed to give that column a value. If you are copying data from one database to another in Visual Basic, perhaps by loading a DataSet object using one connection, and inserting it’s data using another, this can be a problem.

The table option you must set, to be allowed to insert a value in an identity column is called IDENTITY_INSERT. So add this line to your command object to make it work:

SET IDENTITY_INSERT dbo.Products ON;

The following INSERT statement can give a value to the identity column, if the value complies with the rules for that column. The line of code could look something like this:

Using X As New SqlClient.SqlCommand( _
    "SET IDENTITY_INSERT dbo.Products ON; INSERT...

Categories: Visual Basic 9

Tags: Data access