Microsoft KB Archive/294160

= BUG: Update of Newly Inserted Row Causes DB_E_ERRORSOCCURRED =

Article ID: 294160

Article Last Modified on 11/30/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.6
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q294160



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.



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 &quot;Change Inserted Rows&quot; property of the Recordset object to true before opening the recordset: pRs.Properties.Item(&quot;Change Inserted Rows&quot;) = 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.</ul>

<div class="status_section">

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

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol>  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 </li>  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 &quot;dsn=YOURDSN&quot;, &quot;USERNAME&quot;, &quot;PASSWORD&quot;, adConnectUnspecified

'The following two lines are just to clean up from previous runs. pConn.Execute &quot;DELETE FROM ADOTEST&quot; pConn.Execute &quot;DELETE FROM ADOTEST2&quot;

Dim pRs As New ADODB.Recordset pRs.ActiveConnection = pConn pRs.Open &quot;SELECT * FROM ADOtest&quot;,, adOpenKeyset, adLockOptimistic, adCmdUnspecified

pRs.AddNew pRs!col1 = 3 pRs.Update

pRs!col1 = 4 pRs.Update '< Error occurs here. </li></ol>

Keywords: kbbug KB294160

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.