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.

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

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

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

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.

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

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

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?

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

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?