PRB: ADO Does Not Return Records After an Update

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

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.

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 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.

    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
  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.
Propiedades

Id. de artículo: 197528 - Última revisión: 03/02/2005 - Revisión: 1

Comentarios