×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Recordset and command object

Recordset and command object

Recordset and command object

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

CODE

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

CODE

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

David Paulson

RE: Recordset and command object

(OP)
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

RE: Recordset and command object

>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.

RE: Recordset and command object

(OP)
Hi
This is what I have done

CODE

    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

RE: Recordset and command object


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

RE: Recordset and command object

(OP)
Thanks SBerthold

David Paulson

RE: Recordset and command object

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,

Quote:

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close