Global Database Connection

[doramsey]

Is there a way to create one "global" database connection and reference this connection throughout an ASP page? I have to retrieve different recordsets to populate listboxes depending on the user selections. My recordsets change but I am also having to create my connection (MS Access MDB) each time. Thanks.

 

[MerlinB]

Add a connection string (NOT A CONNECTION OBJECT) in an application item:

Application("dbConnection&quot = "DSN=MyAccessDatabase"
Application("dbUser&quot = "fred"
Application("dbPassword&quot = "secret"

Do this in the GLOBAL.ASA file (there should be one for every ASP web). It cannot be made visible via IIS - so it is secure from prying eyes!

And put it in the Application_onstart subroutine - so it only gets called once, regardless of the number of users.

In your page do:

Dim cnMyConnection
set cnMyConnection = Server.CreateObject("ADODB.Connection&quot
cnMyConnection.Open Application("dbConnection&quot _
, Application("dbUser&quot _
, Application("dbPassword&quot


BUT DO NOT save this connection object (cnMyConnection) in the Application or Session collections. It just will not work. However, a single page can make and use just one connection - that is up to you.

OR you can use DTC recordsets - they just make the whole thing a doddle (mainly!)

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[doramsey]

Thanks Merlin,
This works until I call a Sub that creats another recordset. Then I get the error message "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" if I don't create another connection within the Sub. Thanks for your help.

 

[MerlinB]

Within a page, declare the cnMyConnection at page level - i.e. outside of any sub or function.

You can even open the connection outside of any sub or function. You just need to make sure that it is open before any other code tries to use it.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)