Rowset position can not restart...

[MrSatan]

Hi All,

I have a function as follow to retrieve from DB and return the recordset to the caller.

----------------------------------------------
Public Function Retrieve() As ADODB.Recordset
Dim oConnection As ADODB.Connection
Dim oRecordset As ADODB.Recordset
Dim oCommand As ADODB.Command

Set oConnection = New ADODB.Connection
Set oRecordset = New ADODB.Recordset
Set oCommand = New ADODB.Command

oConnection.Open <Connection String>

With oCommand
.CommandType = adCmdStoredProc
.CommandText = <StoredProcedure>
Set .ActiveConnection = oConnection
End With

oRecordset.Open oCommand, , adOpenStatic
Set Retrieve = oRecordset
End Function
----------------------------------------------

In the caller, I am using some code as follow to loop through the recordset.

----------------------------------------------
With oRecordset
If Not .EOF Then
.MoveFirst '*** Fail here

While Not .EOF
.....
.....
.....
.MoveNext
Wend
End If
End With
----------------------------------------------

Now the code will fail in the .MoveFirst with error message "Rowset position can not restart...", I have ready specified "adOpenStatic" to let the recordset be able to scrollable. Why the recordset can't move back to the beginning??? It will work fine when the If statement is removed, but will fail when there is not record in the recordset (as expected)...

Any suggestion?

Thanks in advance.

 

[fredericofonseca]

Make sure you have set the cursorlocation to aduseclient.
If not your adOpenStatic cursortype may be changing to other value.

If you with to be sure add the following code after "oRecordset.Open "
debug.print oRecordset.cursorlocation
debug.print oRecordset.cursortype

Cursortype should be 3
cursorlocation should be 3 also

As for failing if there is no record change your code to be
If Not .EOF and not .BOF Then




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MrSatan]

Thank you very much, it works!!!