PRB: NextRecordset Never Returns Null or Nothing

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

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;" & _
        "Data Sourcce=YourServer;Initial Catalog=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 

Additional query words: kbado200 kbado210sp2 kbdatabase kbsqlserv kbvbp500 kbvbp600

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb