Microsoft KB Archive/197528

= PRB: ADO Does Not Return Records After an Update =

Article ID: 197528

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q197528



SYMPTOMS
When you issue an Update, Insert, or Delete statement followed by a Select statement from an ADO client application, if you reference the ADO Recordset object, you receive the following error:

Run-time error '3265':

ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application.

The following error is returned from ADO 2.5 or later:

Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal.



CAUSE
The execution of the update statement against SQL Server returns a message with the number of records affected by the Update, Insert, or Delete statement (pcRowsAffected.) This number of records message will be cached in the TDS stream. When the calling application checks for results, the actual recordset will be waiting on the pipe after the records affected message.



RESOLUTION
There are two possible workarounds to the this behavior:
 * The first option is to provide a SET NOCOUNT ON statement prior to the Update statement. This would prevent the records affected message from being returned at the end of each Update, Insert, or Delete statement.
 * The second option is to call the Next Recordsetmethod to process the actual recordset.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
 Create a new Standard EXE project in Visual Basic 6.0. Form1 is created by default. From the Project menu, select References, and then add a reference to "Microsoft ActiveX Data Objects Library". Add a command button to your form; this would default to Command1.  Place the following code in the general declaration section of Form1.

Note Change the Data Source name accordingly to your SQL Server.

Note: You must change User ID= and Password= to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. Option Explicit

Private Sub Command1_Click

Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim rst As ADODB.Recordset Dim Str As String

With cn           .Provider = "SQLOLEDB" .Open "Data Source=Test;User ID= ;Password= ;" .DefaultDatabase = "Pubs" End With

' Un-comment the following line to demonstrate Workaround 1 ' cn.Execute "SET NOCOUNT ON "

' Passing an Insert statement followed by a Select statement ' as the source of the recordset Str = "Insert into Jobs (job_desc, min_lvl, max_lvl) Values " _ & "('Support Professional', 25, 75) " _ & " Select * from Jobs"

rs.CursorLocation = adUseClient rs.Open Str, cn, adOpenStatic, adLockOptimistic, adCmdText

' Un-comment the following two lines to demonstrate Workaround 2 ' Set rs = rs.NextRecordset

' For workaround 2, please comment the following line MsgBox rs(1)

MsgBox "Done..."

cn.Close

End Sub  Run the project and note that error 3265 is returned.</li></ol>

<div class="references_section">