Differences between "rst .Open" and "Set rst = cmd.Execute" in VB
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.CreateADOConne
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?
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.CreateADOConne
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"?
"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"?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx!
ASKER
I wish I could've give more points! This have been very informative & helpful.
Thanx
Thanx
its ok...
im glad i could help you in my little way...:-)
game-master
ASKER
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?