I receive the error "Connection is either closed or invalid in this 

context" when trying READ my database from the Global.ASA file onEnd() 

subroutine. 



Everything points to the definition I have, yet my other 10 pages that 

perform database access, do so as expected whether using CommandType of 

Text or Table.



I've checked the definition of the table (Access95) and even redefined the 

field PH_Idx from Byte to Integer. But the Err.Msg returned is always the 

same.  The strConnect is in Upper and Lowercase as the Win2000 Server 

defined it that way.  I discovered earlier that Win2000 or ADODB is case 

sensitive in this area.



The only difference between pages that work and the global.asa is that 

they all use a common ServerSideInclude with strConnect defined and asa 

file is defined as below.



I'll appreciate any assistance on this problem.  It's got me stumped. 



Thank you



Global.asa extract

--------------------------------

Sub Session_onEnd()

	On Error Resume Next

	Dim objRS, objCmd, strConnect, ...



	If Request.ServerVariables("HTTP_HOST") = "stokes" Then

'		Setup for Home Server

            strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    	             "Data Source=D:\Inetpub\wwwroot\data\abc.mdb;" & _

        	         "Persist Security Info=False"

	Else 

'		Setup for website Server

	    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    	             "Data Source=D:\WEBS\DATA\abc.mdb;" & _

        	         "Persist Security Info=False"

	End If



	Application.Lock

		Application("UsersActive") = Application("UsersActive") - 1

	Application.Unlock



'	Update the "Page Hits" table with all relevant Session vars

  

	Set objCmd = Server.CreateObject("ADODB.Command")

	Set objRS  = Server.CreateObject("ADODB.Recordset")



	objCmd.ActiveConnection = strConnect

	objCmd.CommandType	= adCmdText

	objCmd.CommandText 	= "Select * FROM PageHits WHERE PH_Idx = 1"



	set objRS = objCmd.Execute



	If Err.Number <> 0 Then

'		Send the error to the admin address



1st piece of code I tried which does not define the RS explicitly also 

fails:

---------------------------------------------------------------------

	...

	Set objCmd = Server.CreateObject("ADODB.Command")

	objCmd.ActiveConnection = strConnect

	objCmd.CommandType	= adCmdText

	objCmd.CommandText 	= "Select * FROM PageHits WHERE PH_Idx = 1"



	set objRS = objCmd.Execute

	...



Upon executing either of the above I receive the following message (via a 

CDONTS Send):

---------------------------------------------

CommandText :-- Select * FROM PageHits WHERE PH_Idx = 1 --:

Details of the error that occurred:

Err.Number     : 3709  (0xE7D)

Err.HelpContext: 0

Err.Description: The connection cannot be used to perform this operation.

It is either closed or invalid in this context.

Err.Source     : ADODB.Command

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Deryck Stokes" <stokes@c...>

Subject: [asp_database_setup] ADODB Connection Closed or Invalid





: I receive the error "Connection is either closed or invalid in this

: context" when trying READ my database from the Global.ASA file onEnd()

: subroutine.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Indicates that the connection string is not valid.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: The strConnect is in Upper and Lowercase as the Win2000 Server

: defined it that way.  I discovered earlier that Win2000 or ADODB is case

: sensitive in this area.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Actually, it's not...you might want to check this again. Perhaps you are

running on a *nix box which is case sensitive, but Windows does not have

case sensitive file/folder names...



Suggestions:

a) Put your connection string into an application variable (in

Application_OnStart)

Application("strDBConnect") = "...."

This way, you can use the same connection string in all your pages.

Obviously the string on the server will be different to the one you have on

your local machine, but once you've set the global.asa up once, you never

need to worry about it.



b) Check for differences between what is in your server-side include, and

what you have below



c) DON'T IMPLICITLY create connection objects (which is what you are doing

before). You loose the benefits of OLEDB/ODBC connection pooling:

http://support.microsoft.com/support/kb/articles/Q191/5/72.ASP



Always explicitly create a connection object, and then set the recordset's

active connection to that connection object



Cheers

Ken

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Deryck Stokes" <stokes@c...>

> Subject: [asp_database_setup] ADODB Connection Closed or Invalid

> 

> 

> : I receive the error "Connection is either closed or invalid in this

> : context" when trying READ my database from the Global.ASA file onEnd()

> : subroutine.

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> 

> Indicates that the connection string is not valid.

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> : The strConnect is in Upper and Lowercase as the Win2000 Server

> : defined it that way.  I discovered earlier that Win2000 or ADODB is 

case

> : sensitive in this area.

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> 

> Actually, it's not...you might want to check this again. Perhaps you are

> running on a *nix box which is case sensitive, but Windows does not have

> case sensitive file/folder names...

> 

> Suggestions:

> a) Put your connection string into an application variable (in

> Application_OnStart)

> Application("strDBConnect") = "...."

> This way, you can use the same connection string in all your pages.

> Obviously the string on the server will be different to the one you have 

on

> your local machine, but once you've set the global.asa up once, you never

> need to worry about it.

> 

> b) Check for differences between what is in your server-side include, and

> what you have below

> 

> c) DON'T IMPLICITLY create connection objects (which is what you are 

doing

> before). You loose the benefits of OLEDB/ODBC connection pooling:

> http://support.microsoft.com/support/kb/articles/Q191/5/72.ASP

> 

> Always explicitly create a connection object, and then set the 

recordset's

> active connection to that connection object

> 

> Cheers

> Ken

> 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

Ken,



Thanks for the reply. I'm now set straight with the EXPLICIT connections.  

Changed the loading times of some of my pages dramatically.  I missed it 

in the ASP book I read due to it being covered by a very small paragraph 

and that it didn't seem important at the time.  Your recommendation was 

greatly appreciated.



Back to the original problem.  I made reference "adCmdText" but didn't 

include the library did I!   So after hardcoding the "adCmdText" 

as "&H0001" all work as expected.  I was able to locate the correct error 

by stripping the code from the ASA and creating a displayable ASP/HTML 

page with "On Error Resume Next" commented out. 



Thanks again for your help.

Deryck