Microsoft KB Archive/189838

= PRB: NextRecordset Never Returns Null or Nothing =

Article ID: 189838

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q189838



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 problem, do not declare the recordset object with the New keyword in the declaration.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
 Start a new Visual Basic Standard EXE project. On the Project menu, choose References and add a reference to Microsoft ActiveX Data Objects. Create a command button on the default form.  Behind the command button's click event place the following code:

Note You must change UID=, User ID= , Password= , and PWD= 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= ;PWD= " '--OR-- 'cn.ConnectionString = "Provider=SQLOLEDB;" & _ "Data Sourcce=YourServer;Initial Catalog=pubs;User ID= ;Password= "

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

Keywords: kbprb KB189838

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.