PRB: NextRecordset Never Returns Null or Nothing

ID: Q189838

The information in this article applies to:

ActiveX Data Objects (ADO), version 2.0

SYMPTOMS

If you declare your recordset variable with the New keyword in the declaration on the object, the variable never returns nothing. This behavior could cause problems when using multiple recordsets and looping through the recordsets using the NextRecordset until the recordset returns nothing.

CAUSE

This occurs because the act of checking for nothingness instantiates the object.

RESOLUTION

To avoid this problems, do not declare the recordset object with the New keyword in the declaration.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new Visual Basic Standard EXE project.

2. On the Project menu, choose References and add a reference to Microsoft

   ActiveX Data Objects 2.0.

3. Create a command button on the default form.

4. Behind the command button's click event place the following code:

   NOTE: You might need to change the connection information before you run
   the code.

      Private Sub Command1_Click()

      'This causes an error.
      '----------------------------
      Dim cn As New Connection
      Dim rs As New ADODB.Recordset
      Dim cmd As New ADODB.Command

      'This does not cause an error.
      '----------------------------
      ' Dim cn As Connection
      ' Dim rs As ADODB.Recordset
      ' Dim cmd As ADODB.Command
      ' Set cn = New Connection
      ' Set cmd = New Command

      Dim i As Integer, Records As Integer
      Dim SQL As String
      Dim bFlag As Boolean

      cn.ConnectionString = "DRIVER={sql server}" & _
        ";SERVER=YourServer;DATABASE=pubs;UID=sa;PWD="
      '--OR--
      'cn.ConnectionString = "Provider=SQLOLEDB;" & _
        "Location=YourServer;Data Source=pubs;User ID=sa"

      cn.Open

      On Error Resume Next
      cn.Execute "DROP TABLE x"
      On Error GoTo eh

      cn.Execute "CREATE TABLE x(rdint INT CONSTRAINT " & _
        "pk_rdint PRIMARY KEY, rdchar CHAR(255) )"
      SQL = ""
      SQL = SQL & "INSERT INTO x(rdint, rdchar) VALUES(1, 'ONE') "
      SQL = SQL & "INSERT INTO x(rdint, rdchar) VALUES(2, 'TWO') "
      SQL = SQL & "INSERT INTO x(rdint, rdchar) VALUES(3, 'THREE') "

      cmd.CommandText = SQL
      cmd.CommandType = adCmdText
      cmd.ActiveConnection = cn

      i = 1
      bFlag = True
      Set rs = cmd.Execute(Records)
      While bFlag = True
          If rs Is Nothing Then
              bFlag = False
          Else
              Debug.Print "i: " & i; "  State:"; rs.State;
              Debug.Print "  Records Affected:"; Records;
              Debug.Print "  Is Null: " & IsNull(rs)
              i = i + 1
              Set rs = rs.NextRecordset(Records)

          End If
      Wend
      Exit Sub
   eh:
      MsgBox Err.Number & " -- " & Err.Description

   End Sub

When you run the code using the New keyword in the declaration of your recordset object, you will receive the Error 3251 "The operation requested by the application is not supported by the provider." To avoid this error, comment out the section labeled "This causes an Error." and uncomment the section labeled "This does not cause an error."

Additional query words: kbvbp600 kbADO200 kbADO

Version           : WINDOWS:2.0;
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: August 7, 1998