Differences between "rst .Open"  and "Set rst = cmd.Execute" in VB

jana
jana used Ask the Experts™
on
I have notice that some of my programmer differ the manner in which they execute thier SQL statement in VB.  Upto now, I have found 2 ways:  

   sSql = "select * from table1"
   rst .Open sSql, cnn, adOpenKeyset, adLockOptimistic

and

   sSql = "select * from table1"
   cmd.CommandText = sSql
   Set rst = cmd.Execute

Here is in more detail with their variables declared:

OPTION A:
   Public cnn As ADODB.Connection
   Public rst As ADODB.Recordset
   ::
   ::
   sSql = "select * from table1"
   rst.Open sSql, cnn, adOpenKeyset, adLockOptimistic

and
 
OPTION B:
   Dim cn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim cmd As New ADODB.Command
   Dim sqlstring As String
   Set cn = UserInfoGet.CreateADOConnection
   cn.CursorLocation = 3
   cn.DefaultDatabase = UserInfoGet.IntercompanyID
   cmd.ActiveConnection = cn
   cmd.CommandType = 1
   ::
   ::
   sSql = "select * from table1"
   cmd.CommandText = sSql
   Set rst = cmd.Execute

When they close the SQL, they also use different ways:

For OPTION A:
   ::
   ::
   rst.Close

For OPTION B:
   ::
   ::
   If rst.State = adStateOpen Then rst.Close
   If cn.State = adStateOpen Then cn.Close
   Set cn = Nothing
   Set rst = Nothing
   Set cmd = Nothing

I notice that the close is done after every ".Open" in OPTION A and in OPTION B, is done at the end of the VB apps.


FYI: the VB apps just do read & write to MS SQL tables; nothing fancy.

Three questions:

1. Whats the difference between the manner the SQL is being executed?
2. Which one is best for use?
3. Should there be a "CLOSE" for every SQL statement executed in both manner?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
What you see is the difference between two different coding styles.  Option B is a little more robust.  You should be aware that UserInfoGet has to do with Microsoft Dynamics and not alot to do with generic database access.

It is good practice to Close every item that you open (or the result set of an execute).  You may or maynot want to close the connection to the server, depending on what other work you need to do.

Closes will be implied if the variable goes out of scope, or if you set it to nothing, but it could cause memory leaks if some drivers aren't written in a robust manner.

Author

Commented:
Your are correct with with UserInfoGet.

So if I were to work with various tables, I should declare the following once:

   Dim cn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim cmd As New ADODB.Command
   Dim sqlstring As String
   cn.CursorLocation = 3
   cmd.ActiveConnection = cn
   cmd.CommandType = 1

And for every read/write use:

   ::
   ::
   sSql = "select * from table1"
   cmd.CommandText = sSql
   Set rst = cmd.Execute

After the finishing the all read/write of the corresponding tables within the project, I should execute the close:

   If rst.State = adStateOpen Then rst.Close
   If cn.State = adStateOpen Then cn.Close
   Set cn = Nothing
   Set rst = Nothing
   Set cmd = Nothing

Am I correct?
Commented:
That would work just fine.
You might consider mixing the two a little bit...

-- if you are doing statements like Update, Delete, Insert
   sSql = "select * from table1"
   cmd.CommandText = sSql
   Set rst = cmd.Execute

-- if you are doing selects
   rst.Open sSql, cn, adOpenKeyset, adLockOptimistic

Things will read a bit clearer to another programmer, and you have direct access to the cursor modes and lock modes, clearly on the same line.

Don't forget you'll need to open the connection to your database too:

cn.ConnectionString = "DSN=Pubs;UID=MyUserName;PWD=MyPassword;"
cn.Open

or for sql server:

cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
 cnn.Open


So if I were to work with various tables, I should declare the following once:
 
   Dim cn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim cmd As New ADODB.Command
   Dim sqlstring As String
   cn.CursorLocation = 3
   cmd.ActiveConnection = cn
   cmd.CommandType = 1
 
And for every read/write use:
 
   ::
   ::
-- if you are doing statements like Update, Delete, Insert
   sSql = "select * from table1"
   cmd.CommandText = sSql
   Set rst = cmd.Execute
-- if you are doing selects
   rst.Open sSql, cn, adOpenKeyset, adLockOptimistic
 
After the finishing the all read/write of the corresponding tables within the project, I should execute the close:
 
   If rst.State = adStateOpen Then rst.Close
   If cn.State = adStateOpen Then cn.Close
   Set cn = Nothing
   Set rst = Nothing
   Set cmd = Nothing

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I tried it but the "rst.Open" requires a close for every statement executed; i get error message:

"Operation not allowed when the object is open"

So I have to execute a "rstRecord.Close" after every "Select" or use.

With "Set rst = cmd.Execute", I don'e have to execute a "Close" after every execution.

So you think we could stick with "Set rst = cmd.Execute" for every Select, Update, Delete and Insert?

One last thing, whats the purpose of adOpenKeyset, adLockOptimistic in "rst.Open"?

good afternoon...

that is the cursors of the recordset (cursortype, locktype)

read this one....

Working with cursors
A cursor is a set of records that represent the results of a query. Cursors can contain the actual data or just pointers to records in the database, but the mechanism that retrieves the data is transparent to the programmer. You can specify where the cursor should be created (on the client or on the server workstation), the cursor type, and the locking option.

The CursorLocation property specifies where the cursor is to be created. This property can have one of two values: 2-adUseServer or 3-adUseClient. The value of this property is inherited from the Connection object and can be changed only for closed Recordsets. When you're working with the OLE DB Provider for ODBC Drivers and for SQL Server, the default cursor is a forward-only cursor created on the server. (This type of cursor is also the most efficient.) You need to switch to client-side cursors if you want to create dissociated Recordsets and use an optimistic batch update strategy. Client-side cursors are often a good choice when you have a DataGrid control or another complex control that is bound to the Recordset. In all other cases, server-side cursors are often preferable because they offer better performance and additional cursor types.

The CursorType property tells which type of cursor should be created and is one of the following constants: 0-adOpenForwardOnly, 1-adOpenKeyset, 2-adOpenDynamic, or 3-adOpenStatic. Server-side cursors support all these options, whereas client-side cursors support only 3-adOpenStatic. But if you use a different setting for a client-side cursor, a static cursor is automatically created without raising an error.

A forward-only cursor is the default for server-side cursors and is available only for server-side cursors. As I just mentioned, this type of cursor is the most efficient, especially if you set LockType = adReadOnly and CacheSize = 1. Many programmers and authors refer to this last type of cursor as a "noncursor." In The Hitchhiker's Guide to Visual Basic and SQL Server, William R. Vaughn defines this as a "fire-hose" cursor, to emphasize how fast it is at tossing data to the client application. You don't have to do anything special to use this cursor (or noncursor) because it's the default for ADO. You can navigate a forward-only Recordset only by using the MoveNext method. If you want to get the best performance for an application that needs to update records, you should do all your updates through SQL commands or stored procedures.

Dynamic cursors consist of a set of bookmarks to the actual data in the data source. Any time the client requests a record, ADO uses the bookmark to read the current value, which means that the application always reads the latest value stored by other users. Dynamic cursors are automatically updated when other users add or delete a record or change any record already in the Recordset. Not surprisingly, these cursors are the most expensive cursors in terms of performance and LAN traffic because any time you move to another record a trip to the server is required to retrieve the current values. You can always update data and perform all kinds of navigational methods on dynamic Recordsets, including using bookmarks if the provider supports them. This type of cursor is available only as server-side cursors.



game-master
Working with concurrency
All multiuser databases enforce some type of locking strategy. Locks are necessary to prevent multiple users from performing changes on the same record at the same moment, which would probably result in an inconsistent database. Locks are extremely expensive in terms of scalability; when a lock is enforced on a record being modified by a user, no other user can access the same record. Depending on how you write your applications, a lock can significantly degrade performance and can even cause fatal errors if you don't implement a good lock-resolving strategy.

The LockType property indicates which type of lock should be enforced on the data in the database. This enumerated property can be assigned one of the following values: 1-adLockReadOnly, 2-adLockPessimistic, 3-adLockOptimistic, and 4-adLockBatchOptimistic.

The default value for this property is adLockReadOnly, which creates nonupdatable Recordsets. This is the most efficient option because it doesn't impose a write lock on data. It's also the best choice as far as scalability is concerned. Again, a good strategy is to rely on forward-only, read-only noncursors (the default in ADO) when reading data and to do all updates through SQL statements or stored procedures.

When you're using pessimistic locking, ADO tries to lock the record as soon as you enter edit mode, which occurs when you modify one field in the Recordset. It releases the lock only when you issue an Update method or move to another record. While a record is locked, no other user can access it for writing, which severely reduces the potential for the scalability of the application. For this reason, you should never use pessimistic locking when the user interface of your application lets the user freely navigate in the Recordset (unless you want to block all users when any one of them takes a coffee break!). Pessimistic locking is available only for server-side cursors.

Optimistic locking scales up better than pessimistic locking does, but it requires more attention from the programmer. With optimistic locking, ADO locks the current record only while it's being updated, which usually takes a small amount of time.

Optimistic batch locking is a special mode that's available only for client-side static cursors. In optimistic batch locking, you download all the data on the client machine, let the user perform all the necessary changes (including adding and deleting records), and then resubmit all the changes in one single operation. If you decided to go with client-side cursors, optimistic batch locking is the most efficient mode because it reduces network traffic. However, you'll need to implement a strategy for handling conflicts (for example, when two users update the same record), which won't be a trivial task.

Author

Commented:
Thanx for the info.

I think I understand the CursorType, but the specifics of adOpenKeyset, what's it for? In other words, what does adOpenKeyset means when used in the ".Open"

Also I see that you say adLockReadOnly is the most efficient (so I assume is better than using adLockOptimistic), but I don't understand what does "it doesn't impose a write lock on data".  Can you explain?

Opening a dynamic cursor allows you to scroll backwards and forwards through the records and see all changes made by other users, including newly added records. A keyset cursor also allows forward and backward scrolling, but only allows you to see changes made to records that where in the recordset when it was generated i.e. you can see edits and deletes made by other users but not new records that have been added, that would otherwise appear in your recordset.


CursorType specifies the type of cursor used in a Recordset object.

adOpenDynamic - Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them.

adOpenForwardOnly - Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset.

adOpenKeyset - Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.

adOpenStatic - Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.

==========================

LockType specifies the type of lock placed on records during editing.

adLockBatchOptimistic - Indicates optimistic batch updates. Required for batch update mode.

adLockOptimistic - Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.

adLockPessimistic - Indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing.

adLockReadOnly - Indicates read-only records. You cannot alter the data.

If you still have questions I'll be happy to help



gam-master

Author

Commented:
Listen Thanx Lots!!  very helpful.

In conclusion, my line:

"rst.Open sSql, cnn, adOpenKeyset, adLockOptimistic"

Should I keep both adOpenKeyset and adLockOptimistic or what would be your recommendations as optimal?


it depends on how u used ur recordset, if just need to read only recordset use adLockReadOnly,

if for viewing only use the adOpenForwardOnly because it is much faster...


but if u want to edit, update ur recordset use the adLockOptimistic...


on my side, i've been using ur approach...
"rst.Open sSql, cnn, adOpenKeyset, adLockOptimistic"



game-master

Author

Commented:
Thanx!

Author

Commented:
I wish I could've give more points!  This have been very informative & helpful.

Thanx


its ok...

im glad i could help you in my little way...:-)



game-master

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial