PRB: ADO Does Not Return Records After an Update

• ActiveX Data Objects (ADO), versions 2.0, 2.01

SYMPTOMS

When issuing an Update, Insert, or Delete statement followed by a Select statement from an ADO client application, referencing the ADO recordset object would generate 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.

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 NextRecordsetmethod to process the actual recordset.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new Standard EXE project in Visual Basic 6.0. Form1 is created by default.



2. From the Project menu, select References, and then add a reference to "Microsoft ActiveX Data Objects 2.0 Library".



3. Add a command button to your form; this would default to Command1.



4. Place the following code in the general declaration section of Form1.
   
   NOTE: Change the Data Source name accordingly to your SQL Server.

   
     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=sa;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 rst = rs.NextRecordset
            ' MsgBox rst(1)
   
            ' For workaround 2, please comment the following line
            MsgBox rs(1)
   
            MsgBox "Done..."
   
            cn.Close
   
         End Sub
    




5. Run the project and note that error 3265 is returned.

REFERENCES

For additional information about SET NOCOUNT option, please refer to the Transact SQL Help and search for SET NOCOUNT.

Additional query words: kbdse

Keywords          : kbADO kbDatabase kbMDAC kbOLEDB kbSQLServ 
Version           : WINDOWS:2.0,2.01
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: July 2, 1999