Recordset based on stored procedure not allowing recordcount property

[tumtoo10]

Well I am calling a stored procedure written in Sql Server from VB.I take the result in a recordset. But the recordset doesn't allow Move previous/Recordcount/Movefirst , and find methods. Also the recordcount returned is always -1. Can anybody let me know how to overcome this.

 

[CodeFish]

Hi tumtoo10,

Try This:-

Public Function GetStoredProcRS(vConnection, sStoredProcName As String) As ADODB.Recordset
'---------------------------------------------------------'
' Purpose: - Executes Stored Procedure.
'
' Notes: - Returns ADO Recordset if Completed Successfully
'
' The returned Recordset object is always a read-only, forward-only cursor.
' If you need a Recordset object with more functionality, first create a
' Recordset object with the desired property settings, then use the Recordset
' object's Open method to execute the query and return the desired cursor type.
'
'----------------------------------------------------------

'Declare ADO Objects
Dim oCmd As ADODB.Command
Dim oRec As ADODB.Recordset

'Instantiate ADO Objects
Set oRec = New ADODB.Recordset
Set oCmd = New ADODB.Command

With oCmd
'Set the connection to use for this command
.ActiveConnection = vConnection

' Set the properties of the command
.CommandType = adCmdStoredProc
.CommandText = sStoredProcName
End With

'Create Recordset - Assign Properties
With oRec
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open oCmd
End With

Set GetStoredProcRS = oRec

'Release References
Set oCmd = Nothing
Set oRec = Nothing

End Function


Regards,

Codefish

 

[Sypher2]

tumtoo10,

What is your code for your recordset.open statement? I would imagine you have no options specified, which would return a read-only, forward-only recordset. Try specifying adOpenStatic for the cursor type.