Microsoft KB Archive/253157: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - ">" to ">") |
||
Line 116: | Line 116: | ||
Set cn = New ADODB.Connection | Set cn = New ADODB.Connection | ||
Set rs = New ADODB.Recordset | Set rs = New ADODB.Recordset | ||
cn.Open "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=Pubs;User ID=<username | cn.Open "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=Pubs;User ID=<username>;Password=<strong password>;" | ||
cn.Execute "SET IDENTITY_INSERT Table1 ON", , adExecuteNoRecords | cn.Execute "SET IDENTITY_INSERT Table1 ON", , adExecuteNoRecords | ||
cn.Execute "INSERT INTO Table1(ID, Description) Values (234,'Test Record')" | cn.Execute "INSERT INTO Table1(ID, Description) Values (234,'Test Record')" |
Revision as of 09:44, 21 July 2020
Article ID: 253157
Article Last Modified on 4/29/2005
APPLIES TO
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.1 Service Pack 1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft Data Access Components 2.8
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic 5.0 Enterprise Edition
- Microsoft Visual Basic 6.0 Enterprise Edition
This article was previously published under Q253157
SYMPTOMS
Identity columns are normally read-only because the server generates the values. However, when you migrate data from one table to another you often want to keep the existing Identity column values rather than use server-generated values. Microsoft SQL Server 7.0 allows you to do this with the SET IDENTITY_INSERT ON statement.
This operates correctly when you use INSERT INTO SQL statements. However, when you insert records through the AddNew method of an ActiveX Data Objects (ADO) Recordset, you receive the following error:
CAUSE
The Microsoft SQL Server 7.0 OLE DB provider incorrectly reports to ADO that the Identity column is read-only.
RESOLUTION
Use INSERT INTO SQL statements to add records where you must supply a value for Identity columns.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
Steps to reproduce the behavior
Use the following steps to reproduce this behavior:
- In ISQL or the SQL Server 7.0 Enterprise Manager, create a table in the PUBS database named Table1 with the following fields:
Name: ID
DataType: INT (Identity)
Name: Description
DataType: VARCHAR (50)
- In Visual Basic 5.0 or 6.0, create a Standard EXE project. Form1 is created by default.
- Set a reference to:Microsoft Activex Data Objects.
Add a Command button and the following code to Form1:
Private Sub Command1_Click() Dim cn As ADODB.Connection, rs As ADODB.Recordset Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn.Open "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=Pubs;User ID=<username>;Password=<strong password>;" cn.Execute "SET IDENTITY_INSERT Table1 ON", , adExecuteNoRecords cn.Execute "INSERT INTO Table1(ID, Description) Values (234,'Test Record')" rs.Open "SELECT * FROM Table1", cn, adOpenKeyset, adLockOptimistic, adCmdText rs.AddNew rs!ID = 8 ' <---- fails here rs!Description = "Record 8" rs.Update rs.Close cn.Close End Sub
Note You must adjust the connect string to point to your server. Make sure that the User ID has the appropriate permissions to perform this operation on the database. Also, in the INSERT INTO statement you must specify the field names when using Identity_Insert.
- Run the project and click the Command button. You receive the error listed in the "Symptoms" section.
Note The INSERT INTO statement completes successfully.
Stop the project and comment the following lines:
cn.Execute "INSERT INTO Table1(ID, Description) Values (234,'Test Record')" cn.Execute "SET IDENTITY_INSERT Table1 ON", , adExecuteNoRecords rs!ID = 8
Run the code again. The INSERT proceeds without error but the record gets a default Identity value.
Keywords: kbbug kbpending KB253157