Problem with recordset cursor type.

[Syerston]

Could anyone help please?

The code below returns the result set from a stored procedure. How can I set the cursortype of the recordset using this method. As I am using the EOF and BOF properties of the recordset to see if it contains records, I need to specify something other than forward only to read the records again.

Public Function RetrieveClaimantRecords(ByVal p_sHBRef As String, ByVal p_sSchemeRef As String) As ADODB.Recordset

    Dim conDB As ADODB.Connection
    Dim comDetails As ADODB.Command
    Dim objParam As ADODB.Parameter
    Dim rsClaimantDetails As ADODB.Recordset
    
    On Error GoTo ErrorHandler
    
    'Create connection and open
    Set conDB = CreateConnection
    conDB.Open
    
    'Create command object
    Set comDetails = New ADODB.Command
    comDetails.ActiveConnection = conDB
    comDetails.CommandType = adCmdStoredProc
    comDetails.CommandText = "FindClaimantRecords"
    
    'Append parameter
    Set objParam = comDetails.CreateParameter("HBReference", adVarChar, adParamInput, 9, p_sHBRef)
    comDetails.Parameters.Append objParam
    
    Set objParam = comDetails.CreateParameter("SchemeRef", adVarChar, adParamInput, 4, p_sSchemeRef)
    comDetails.Parameters.Append objParam
    'Execute command and retrieve recordset
    Set rsClaimantDetails = comDetails.Execute
    Set RetrieveClaimantRecords = rsClaimantDetails
    
ExitProc:
    'Tidy up objects
    Set rsClaimantDetails = Nothing
    Set objParam = Nothing
    Set comDetails = Nothing
    Set conDB = Nothing
    Exit Function
    
ErrorHandler:
    Err.Raise Err.Number, Err.Source, Err.Description
    Resume ExitProc
End Function

John

 

[dekcool]

try adding this one...

Dim rsClaimantDetails As New ADODB.Recordset

rsClaimantDetails.CursorType=adOpenKeyset ____________________________________________________
Invest your time in learning, Not just practicing.

DEK

 

[LazyMe]

Why can't you use the EOF and BOF properties on the ForwardOnly recordset ? They don't move your recordset pointer around, you'll still be on the first record (if there's one in the set).
Greetings,
Rick

 

[Syerston]

Rick

When I populate a listview with the recordset without checking for records it works fine. But if I add the EOF and BOF the listview populates with nothing.

Any ideas. John

 

[CCLINT]

With rs
Set .ActiveConnection = conn
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open Comm1.Execute
End With [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[Syerston]

Thank you CCLINT John