Object declarations in ASP global.asa

[SoftwareMaker]

Hi fellow developers,

Put your thinking caps on...

I have read advice from many experts in the industry advising developers not to use ADODB.CONNECTION Objects in the global.asa file as they keep connections open for a long time and doesnt use connection pooling
ie. declared in the global.asa file
<OBJECT RUNAT=SERVER PROGID=ADODB.CONNECTION ID=MYCONN SCOPE=SESSION or APPLICATION>
REM OBJECT SCRIPT
</OBJECT>

Say I have 30 pages that uses the ADODB.CONNECTION object and instead of writing Server.CREATEOBJECT("ADODB.CONNECTION") for each and every 30 pages. I resort to using the above-mentioned object in the global.asa file.

BUT this is not advised according to books and experts, what is the next best alternative ? the INCLUDE DIRECTIVE doesnt help much as I still have to update 30 pages to include the file and if the INC File is moved, I still have to update 30 pages.

Tell me, if I close my connection explicitly by setting MYCONN.CLOSE after every page is executed, will it resolve the problems keeping connections open for a long time and also the problems of connection pooling ???

If i create a page-level adodb.connection using server.createobject then open and close it explicitly, I can set the page-level connection object to nothing at the end, of course I cant set a <OBJECT> that runs in Session or application scope to nothing, does that have any effect though even if I close all open connections at the end of every page ?

What I am trying to ask is that, even if the above-mentioned method of setting a Connection object at the session or application level is frowned upon as they keep connections open for a long time and doesnt use connection pooling
ie. declared in the global.asa file
<OBJECT RUNAT=SERVER PROGID=ADODB.CONNECTION ID=MYCONN SCOPE=SESSION or APPLICATION>
REM OBJECT SCRIPT
</OBJECT>
Will closing them explicitly at the end of every page (even though I CANNOT set them to nothing) rectify those problems ? If not, what do I do if I always have to Server.createObject for like 30-50 webpages ?

Thanks for any advice and tips

[Jerry Grant]

Keep your connection details in Application objects

<SCRIPT LANGUAGE=VBScript RUNAT=Server>

Sub Application_OnStart
	'==Visual InterDev Generated - startspan==
	'--Project Data Connection
		Application("cn_ConnectionString") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\DB\myDatabase.mdb") & ";Persist Security Info=False"
		Application("cn_ConnectionTimeout") = 15
		Application("cn_CommandTimeout") = 30
		Application("cn_CursorLocation") = 3
		Application("cn_RuntimeUserName") = "admin"
		Application("cn_RuntimePassword") = ""
	'==Visual InterDev Generated - endspan==
End Sub
</SCRIPT>

These can then be used like session objects anywhere in you pages without the need for server side includes. Call as follows for each ASP page requiring Database access:

' Create Objects
set cn = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")
rqs = "SELECT * FROM myTable WHERE myID = " & myValue
' Open Connection
cn.Open Application("cn_ConnectionString")
' Set up Recordset 
rs.CursorLocation = adUseClient
' Get required data
if Len(rqs) > 0 then
	rs.Open rqs, cn, adOpenKeyset, adLockBatchOptimistic
                'Proccess the data to HTML etc
end if
' Tidy Up
rs.Close
set rs=nothing
cn.Close
set cn=nothing

This will always allow you to keep your connection details without session objects (when client user has 'No Cookies' selected on their browser). The added advantage is that connections will be kept to a minimum (only on demand).

[SoftwareMaker]

Thanks Jerry for your reply...

But that means I have to add the Server.CreateObject on all my 50 webpages...What happens if I want to change the driver name to ADODB.Connection.2.7...I'll have to change all 50 pages

I know a Include file will solve this problem...But I need to know why declaring this object at the Global.asa file is not recommended ie...
<OBJECT RUNAT=SERVER PROGID=ADODB.CONNECTION ID=MYCONN SCOPE=SESSION or APPLICATION>
REM OBJECT SCRIPT
</OBJECT>

Thanks for all your help Jerry

[Jerry Grant]

OK

The obvious would be make the connection (with whatever CreateObject reference you want) and then put the connection into a session variable.

This has its limitations though:

• The session object will not be available to 'No Cookie' selected browsers.
• The session object will only be destroyed 20mins after the session has ended (if the user leaves the site prematurely).

I personally think that the server side includes is the best way to go, as this common file can also hold other shared procedures and functions.

Although a pain initially, you can build a template for any new pages, which will always specify the include file.

My site, only uses one ASP page, but loads the data from other files into a DIV block. This is via a database held reference which gets called every time the ASP page is requested, so my include files only need to be referenced from one ASP page.

I am not suggesting you go down this route, but it works well for me as all I need to do is update data in the database to change my site content from the ASP page.