Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

BUG: Update of Newly Inserted Row Causes DB_E_ERRORSOCCURRED


View products that this article applies to.

Symptoms

On updating a row which has been newly inserted into a table, whether through an OLEDB rowset or an ActiveX Data Objects (ADO) recordset, the following error may occur:
(0x80040e21, DB_E_ERRORSOCCURRED) Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
This error occurs when the following conditions are met:
  • There is a connection to SQL Server through the OLE DB Provider for ODBC drivers (MSDASQL).
  • The recordset is created by selecting from a SQL Server database table.
  • The recordset uses server-side cursors.
  • One of the columns in the recordset is the primary key for the table.
  • There is an insert trigger on the table which inserts rows into some database object.
  • The AddNew and Update methods are used to add a new row to the table.
  • Update is called again to update a row before the recordset has been scrolled using FetchNext, FetchFirst, and so on.

↑ Back to the top


Resolution

To prevent this error from occurring, do any of the following:
  • Use client-side cursors (by setting the ADO recordset Cursor Location property to adUseClient).
  • Remove the insert trigger from the source table.
  • Put the following line at the beginning of the trigger code.
    SET NOCOUNT ON
    					
  • Use SQLOLEDB rather than MSDASQL, and set the "Change Inserted Rows" property of the Recordset object to true before opening the recordset:
    pRs.Properties.Item("Change Inserted Rows") = True
    					
  • Remove the primary key from the table.
  • After calling Update the first time to insert the new row in the table, scroll the recordset using methods like MoveNext and MovePrevious before calling Update again.

↑ Back to the top


Status

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

↑ Back to the top


More information

Steps to Reproduce Behavior

  1. Use the following script to create the database objects on SQL Server 7.0 or SQL Server 2000:
    create table ADOtest(
    col1 int not null primary key
    )
    go
    
    create table ADOtest2(
    col1 int not null primary key
    )
    go
    
    create trigger trIADOtest on ADOtest for insert as
    insert ADOtest2 values (7)
    go
    					
  2. Use the following code in a Microsoft Visual Basic application to reproduce the error. Be sure to change the DSN name, user name, and password.
    Dim pConn As New ADODB.Connection
    pConn.Open "dsn=YOURDSN", "USERNAME", "PASSWORD", adConnectUnspecified
    
    'The following two lines are just to clean up from previous runs.
    pConn.Execute "DELETE FROM ADOTEST"
    pConn.Execute "DELETE FROM ADOTEST2"
    
    Dim pRs As New ADODB.Recordset
    pRs.ActiveConnection = pConn
        
    pRs.Open "SELECT * FROM ADOtest", , adOpenKeyset, adLockOptimistic, adCmdUnspecified
    
    pRs.AddNew
    pRs!col1 = 3
    pRs.Update
    
    pRs!col1 = 4
    pRs.Update '<---- Error occurs here.
    					

↑ Back to the top


Keywords: KB294160, kbbug

↑ Back to the top

Article Info
Article ID : 294160
Revision : 7
Created on : 11/30/2007
Published on : 11/30/2007
Exists online : False
Views : 292