From Command object to recordset

[Andrew Kiss]

When using a command object to execute a stored procedure and assigning it to a recordset, the recordset properties don't work e.g recordcount or movelast etc.

The cursor is client side and dynamic, if I don't use a command object everythings fine but I want to be able to use stored procedures.

Any tips would be much appreciated, cheers.

[rikshawdriver]

Hint: The problem may be with LockType. You have not mentioned it.

[mick lennon]

rikshaw
What lock type would you suggest?
Ive tried to fill a datagrid with the results of a query contained in a stored procedure and called through a command object.
I get a "recordset not bookmarkable" error

[Lethal]

One more thing, you can't have a client side cursor and it be dynamic, it is static..

[Lethal]

Here's what I use and works with all recordset methods.

 Set CN = New ADODB.Connection   ' Establish a connection to the datasource
    strConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" & _
                    "Initial Catalog=ICE.NET;Data Source=MPNETICE2000"
    CN.Open strConnection
   
    Set RS = New ADODB.Recordset  ' Initialize cursor type and lock type
    With RS
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .CursorLocation = adUseClient
    End With

    Set CMD = New ADODB.Command
    With CMD
        .CommandText = "Invoice_All"
        .CommandType = adCmdStoredProc
    End With
     RS.Open CMD.CommandText, CN