Recordset and command object 1

[dpaulson]

What kind of recordset is created when set to a command.execute statement?

Set cmdGSTSalesAmount = New ADODB.command
    cmdGSTSalesAmount.ActiveConnection = cnAccounting
    cmdGSTSalesAmount.CommandText = "SELECT sum(TotalGSTSales) as MonthTotalGSTSales,sum(TotalNonGSTSales) as MonthTotalNonGSTSales, sum(GSTAmount) as MonthGSTTotal, sum(DebitTotal) as MonthInvoiceAmount FROM Invoices WHERE Printed = True AND  DebitTransType = 'I' AND InvoiceDate BETWEEN ? AND ?"
    cmdGSTSalesAmount.CommandType = adCmdText
    cmdGSTSalesAmount.Prepared = True
    Set prm = cmdGSTSalesAmount.CreateParameter("Start", adDate, adParamInput)
    cmdGSTSalesAmount.Parameters.Append prm
    Set prm = cmdGSTSalesAmount.CreateParameter("End", adDate, adParamInput)
    cmdGSTSalesAmount.Parameters.Append prm

And then a recordset is created with

    cmdGSTSalesAmount.Parameters("Start").Value = StartDate
    cmdGSTSalesAmount.Parameters("End").Value = EndDate
    Set rsGSTAmount = cmdGSTSalesAmount.Execute

David Paulson

 

[dpaulson]

Well, it looks like you can navigate forward and backwards, but unfortunately it is not an updateable one. Is there a way to create an updateable recordset in this fashion?

David Paulson

 

[ChrisHirst]

.CursorType = adOpenDynamic

.LockType = adLockPessimistic



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[SBerthold]

>What kind of recordset is created when set to a command.execute statement?

Probably a static cursor with a cursor location of adUseServer.


Alot of overhead using an adOpenDynamic cursor type.

Probably better to use adKeySet for the CursorType and a adLockOptimistic LockType, and adUseServer for the CursorLocation.

 

[dpaulson]

Hi
This is what I have done

    cmdGSTSalesAmount.Parameters("Start").Value = StartDate
    cmdGSTSalesAmount.Parameters("End").Value = EndDate
    rsGSTAmount.CursorType = adOpenKeyset
    rsGSTAmount.LockType = adLockOptimistic
    Set rsGSTAmount = cmdGSTSalesAmount.Execute

adopenkeyset has a value of 1
adlockoptimistic has a value of 3

When the execute statement has been issued the cursortype now has a value of 3 or adopenstatic and the locktype has a value of 1 or adlockreadonly. So these values are inherited from the configuration of the command. Can I change these somehow?

David Paulson

 

[SBerthold]

You would then need to use it as follows:

'(The following two settings can also be changed to adUseClient and adLockOptimistic or adLockBatchOptimistic, and then used with an INSERT statement or UpdateBatch, as it would then always be a client static cursor)

rsGSTAmount.CursorLocation = adUseServer
rsGSTAmount.LockType = adLockOptimistic

rsGSTAmount.Open cmdGSTSalesAmount, , adOpenKeyset

 

[dpaulson]

Thanks SBerthold

David Paulson

 

[JerryKlmns]

David

Why to use the command parameters since you build the SQL statement on the fly? Even more why use the command object?
A simple recordset could do the job, although I cant say if it would be slower. About the LockType =adLockOptimistic or adLockPessimistic,

Queries with aggregated data are not updatable see here

If you don't need to move back and forth, use a CursorType forward-only & LockType read-only for a cursor location of adUseServer. Much faster.

------------------------
Happy New Year to All