while writing my first asp app i created my db connection object in every asp page that i wrote then set it to nothing at the end of each page. since then i've learned that i can create that connection object in my global.asa file and create it in the application_onStart or session_onStart events. So as I see it i'm presented with 3 options:

1 - connection object with script level scope
2 - connection object with session level scope
3 - connection object with application level scope

i only need one connection object that hooks a single access file.

what do you asp gurus suggest? something inside me is saying i need to create a single application level connection object and do away with all the script level creating and killing.

While you can do it this way, it is heavy on server resources. If you prefer to not constantly type the connection info just type it into a include file. Then, you only include it on the pages that are necessary. Also, if for some reason a user does not allow cookies the application and session events won't fire.

Finally, session on end is notorious for not firing. This will result in way to many open connections and greatly deteriorate server performance

One other thing, why have an open connection if you have pages that don't necessarily need the connection 'always open'

John

Make sure that if you use an include file that you give it an asp extension so it won't return in text the path to your database if someone guesses the name of it.

You absolutely should not store connection objects in the application or session collection. It will choke your application. Put it in an include file instead and open the connection only when you need it.

I agree with the "one connection per file" method. There are plenty of ways to simplify the use of a single connection object thruout the life of a page, but like pgtips says, you should not leave a connection object in session or application scope.

If you are concerned about connections being constantly created, don't be. Once you have created a connection and closed it, the connection will remain in connection pooling. If another page request comes long that uses the same connection string, the pooled connection will be used and that will same some time when you create the object.

Peter
------------------------------------------------------
Work smarter, not harder.

In the book on ASP I am reading, it says that having one DB connection with application scope would be better than having it open and close over and over again. (on a high traffic site with one database and many connections)
I don't understand how keeping the connection, not the recordset but just the single application level, database connection open would be more of a resource hog than having it open a hundred times or more in one minute.
If you have a website where every page uses the database then wouldn't an application level connection be the best way to go?

I may be way off on this since I just started learning asp but this just seems the way it would logically work.
if i'm wrong, please tell me.


on another note, anyone know how i can use methods and properties on session/application level variables?
Application("Connection").open ?

------------------------------
Fezzic, are there rocks ahead?
If there are, we'll all be dead.
NOW STOP THAT RHYMING, Now I Mean it.
Any-body want a Peanut?