Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Connection Obj and Recordset Obj
- Thread starter 123ASP
- Start date
stoolpigeon
Programmer
This question interested me and I did a little digging. I have not been able to find anything that explicitly spells out the difference.
In most examples you will see the clean up done in the order you post first. Apparently in ASP (I don't do any ASP work so I don't know first hand) leaving clean up to ASP can be iffy and it is a very good practice to take the steps in the right order to close and destroy the objects.
In your example above (if you are looking for brevity) setting the connection to nothing will do the same for your recordset. The same with closing it. Once you close or destroy a connection - any recordset that depended on that connection goes with it. So set con = nothing alone will accomplish the same as your other 2 examples. (I just tried that out myself to see)
Others may disagree but I've found VB to be pretty good at cleaning up recordset and connection objects. I've done work where explicitly closing a recordset and/or connection took forever- so I just didn't. It did not cause me any problems.
When I do a loop that opens a recordset on each iteration I never explicitly close the recordset- I just move from it being opened to setting it as a new recordset. It has been my experience w/VB 6.0 and SQL Server that this gives me the greatest speed.
I am sure there are folks here who have had experiences that differ. Database work seems to involve a lot of trial and error as each situation can vary to a great degree.
Lastly in my long winded remarks-- I think that the closing and setting to nothing may look strange right next to one another and one might think "then why have the close method?" but often closing a recordset or connection without destroying it would be necessary- and then having the 2 items seperated is very nice.
In most examples you will see the clean up done in the order you post first. Apparently in ASP (I don't do any ASP work so I don't know first hand) leaving clean up to ASP can be iffy and it is a very good practice to take the steps in the right order to close and destroy the objects.
In your example above (if you are looking for brevity) setting the connection to nothing will do the same for your recordset. The same with closing it. Once you close or destroy a connection - any recordset that depended on that connection goes with it. So set con = nothing alone will accomplish the same as your other 2 examples. (I just tried that out myself to see)
Others may disagree but I've found VB to be pretty good at cleaning up recordset and connection objects. I've done work where explicitly closing a recordset and/or connection took forever- so I just didn't. It did not cause me any problems.
When I do a loop that opens a recordset on each iteration I never explicitly close the recordset- I just move from it being opened to setting it as a new recordset. It has been my experience w/VB 6.0 and SQL Server that this gives me the greatest speed.
I am sure there are folks here who have had experiences that differ. Database work seems to involve a lot of trial and error as each situation can vary to a great degree.
Lastly in my long winded remarks-- I think that the closing and setting to nothing may look strange right next to one another and one might think "then why have the close method?" but often closing a recordset or connection without destroying it would be necessary- and then having the 2 items seperated is very nice.
I found this at
When using ADO to make connections to SQL Server, always be sure you explicitly close any Connection, Recordset, or Command objects you have opened. While letting an object go out of scope will in affect close the object, it is not the same as explicitly closing an object. By explicitly closing these objects and setting them to nothing, you do two things. First, you remove the object sooner than later, helping to free up resources. Second, you eliminate the possibility of "connection creep". Connection creep occurs when connection or resource pooling is used and when connections are not properly closed and released from the pool. This helps to defeat the purpose of pooling and reduces SQL Server's performance. [6.5, 7.0, 2000]
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'
When using ADO to make connections to SQL Server, always be sure you explicitly close any Connection, Recordset, or Command objects you have opened. While letting an object go out of scope will in affect close the object, it is not the same as explicitly closing an object. By explicitly closing these objects and setting them to nothing, you do two things. First, you remove the object sooner than later, helping to free up resources. Second, you eliminate the possibility of "connection creep". Connection creep occurs when connection or resource pooling is used and when connections are not properly closed and released from the pool. This helps to defeat the purpose of pooling and reduces SQL Server's performance. [6.5, 7.0, 2000]
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'
- Status
Similar threads
