Wrox Programmer Forums
|
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 12th, 2006, 05:56 AM
Authorized User
 
Join Date: Jan 2005
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADODB.Connection on every page?

Hi,

I am writing an ASP web application that accesses a SQL Server 2000 database.

I have this code:

Dim dcnDB
Set dcnDB = Server.CreateObject("ADODB.Connection")
dcnDB.Open strConnect

in a file called data.asp, which I include in each of my ASP pages using this code:



This works, but I am not sure if this is right. Is it correct to open the database connection on each page? Or should I just open it once for the session? Also, I don't actually manually close the connection anywhere, which I suspect is not right either.

Please can you tell me the correct usage for code to open and close a databse connection for a web application? The way I'm doing it feels like it must be very inefficient, but I don't know for sure.

Thanks.

 
Old September 12th, 2006, 07:04 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Never never never store a database connection in session; open the connection when you need it and close it immeadately there after.

Always close your connection objects and set that and the recordset object to Nothing.

hth.

"The one language all programmers understand is profanity."
 
Old September 12th, 2006, 07:19 AM
Registered User
 
Join Date: Sep 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I agree that you should not store a connection string in a session. I normally use a second to close the database connection as well as the recordset. The inside of my file looks like this.

<%
sqlrs.close
aconn.close
set sqlrs=nothing
set aconn=nothing
%>

Obviously if you are sending a INSERT,UPDATE or DELETE statement you will not use the "sqlrs.close" as it happens automatically.

hth



 
Old September 12th, 2006, 07:35 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I agree with karoljoc, it is much easier to declare 2 include files e.g. dbopen.inc and dbclose.inc that will handle the opening and closing of your connections, all you have to do is write the query and work with the data.

"The one language all programmers understand is profanity."
 
Old September 13th, 2006, 05:22 AM
Authorized User
 
Join Date: Jan 2005
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK - thanks - but I am not sure you have answered my question clearly.

Are you saying that ON EACH SEPARATE PAGE of my application (at least, those pages that require access to the database), I should include code at the top to open the connection, and then code on the same page (at the point where I've finished with the need for the connection) to close the connection?

If so, please can you also tell me (as an aside) what the effect of my current code is, insofar as it opens the connection on every page (due to my include) but does not ever explicitly close the connection. Does my current code result in the server holding open many connections to the database unnecessarily?

Thanks.

 
Old September 13th, 2006, 06:02 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Yes, evey page that requires a database connection, you should include code to connect to and disconnect from the database.

The current way you are doing things can cause server preformance to go through the floor because your database connections may or may not be getting closed; this is bad practice.

"The one language all programmers understand is profanity."
 
Old September 13th, 2006, 06:16 AM
Authorized User
 
Join Date: Jan 2005
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK. That's great advice. Thanks, all.

 
Old September 13th, 2006, 06:36 AM
Authorized User
 
Join Date: Jan 2005
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One further question:

If I have a (conditional) Response.Redirect command mid-way through my page, must I close the connection *before* that is executed, or can I always have the close connection code at the very bottom of the page? I am unsure how the server treats the page in this respect... after a Reponse.Redirect, are the following commands on the page ignored?

 
Old September 13th, 2006, 07:08 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

It is good practice that if you are leaving a page to manually close your connections and there is no guess work as to whether or not the ASP process closed the connection to the database.

"The one language all programmers understand is profanity."
 
Old September 13th, 2006, 07:35 AM
Authorized User
 
Join Date: Jan 2005
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I understand that, but please can you help me with the answer to my question about Reposnse.Redirect, and whether the position of the close-connection code is important in relation to it?






Similar Threads
Thread Thread Starter Forum Replies Last Post
Using DAO and ADO within ADODB.connection faridrd Access VBA 0 January 8th, 2008 10:43 PM
Help.Can i use ADODB.Connection with DetailsView? aekta ASP.NET 2.0 Professional 5 March 11th, 2007 06:08 AM
ADODB.Connection (0x800A0E7A) sumanst Classic ASP Databases 1 August 5th, 2005 02:56 PM
ADODB connection for Recordset dpatole Beginning VB 6 1 October 14th, 2003 04:24 PM
ADODB Connection String misterqj Access 2 September 30th, 2003 06:30 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.