PRB: ADO Does Not Return Records After an Update (197528)

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.

MORE INFORMATION

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=<username> and Password=<strong 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=<user name>;Password=<strong 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.