PRB: NextRecordset Never Returns Null or Nothing (189838)

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.
3. Create a command button on the default form.
4. Behind the command button's click event place the following code:
   
   Note You must change UID=<username>, User ID=<username>, Password=<strong password>, and PWD=<strong password> to the correct values before you run this code. Make sure that User has the appropriate permissions to perform this operation on the database.

         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=<username>;PWD=<strong password>"
         '--OR--
         'cn.ConnectionString = "Provider=SQLOLEDB;" & _
           "Data Sourcce=YourServer;Initial Catalog=pubs;User ID=<username>;Password=<strong password>"
   
         cn.Open
   
         On Error Resume Next
         cn.Execute "DROP TABLE x"
         On Error GoTo eh
   
         cn.Execute "CREATE TABLE x(rdint INT CONSTRAINT " &amp; _
           "pk_rdint PRIMARY KEY, rdchar CHAR(255) )"
         SQL = ""
         SQL = SQL &amp; "INSERT INTO x(rdint, rdchar) VALUES(1, 'ONE') "
         SQL = SQL &amp; "INSERT INTO x(rdint, rdchar) VALUES(2, 'TWO') "
         SQL = SQL &amp; "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."