Global.asa and DSN-less database connections

[David Raskino]

OBJECTIVE: I am trying to create a connection to an access database in and then use that connection ASP pages in my application without having to re-connect to the database.

I have used the following code in the global.asa file in the Session_OnStart sub to create a connection (which works fine):
strPath = "C:\Data\WebProjects\GuestBook\db.mdb"
Set conSQLServer = Server.CreateObject("ADODB.Connection")
conSQLServer.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strPath

However, when I use the following code in another ASP file, the get an "object required" error:
Set rstData = conSQLServer.Execute ("<sql-statement>")
......which indicates that rstData is not available at application or even session level.

Any help would be greatly appreciated.

Many thanks,

David.

[Ray at]

Code in global.asa only runs on the start of a session or application
(depending on which sub you put it in) or at the end of the session or
application. Create a subroutine to create your connection in a common
include file and call it in the pages that need the connection. While it is
possible to create a connection object and store it in a session or
application variable, it is strongly discouraged. It is best to create an
adodb.connection, connect, get your recordset, close, and destroy on each
page individually. But, you can create a sub like so in an include file and
include it in all your pages that need the connection.

in include:

Dim oADO
Sub OpenData()
Set oADO = CreateObject("ADODB.Connection")
oADO.Open yourConnectionString
End Sub

Sub CloseData()
oADO.Close
Set oADO = Nothing
End Sub


in .asp file:
Call OpenData()
Set yourRS = oADO.Execute(YourQuery)
'''code
Call CloseData()


You can also do things like Function GetRecordSet() in your include that
would get a recordset, and return it as an array with .GetRows() and things
along those lines. Whatever you do, do not try to create and open a
connection and continuously leave it open.

Ray at home


"David Raskino" <DavidRaskino@woodstock.ac.in> wrote in message
news:7E26701A-4D4E-4BC8-8E09-E8A5B90AF8B8@microsoft.com...

> OBJECTIVE: I am trying to create a connection to an access database in and

then use that connection ASP pages in my application without having to
re-connect to the database.

>
> I have used the following code in the global.asa file in the

Session_OnStart sub to create a connection (which works fine):

> strPath = "C:\Data\WebProjects\GuestBook\db.mdb"
> Set conSQLServer = Server.CreateObject("ADODB.Connection")
> conSQLServer.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" &

strPath

>
> However, when I use the following code in another ASP file, the get an

"object required" error:

> Set rstData = conSQLServer.Execute ("<sql-statement>")
> ....which indicates that rstData is not available at application or even

session level.

>
> Any help would be greatly appreciated.
>
> Many thanks,
>
> David.

[Brynn]

Here is my DBConn.asp code

[url]http://www.coolpier.com/cp/cp_scripts/script.asp?file=DBConn.asp&view=code[/url]

Brynn


On Thu, 22 Jan 2004 00:01:14 -0500, "Ray at <%=sLocation%>"
<myFirstNameATlane34dotKOMM> wrote:

>Code in global.asa only runs on the start of a session or application
>(depending on which sub you put it in) or at the end of the session or
>application. Create a subroutine to create your connection in a common
>include file and call it in the pages that need the connection. While it is
>possible to create a connection object and store it in a session or
>application variable, it is strongly discouraged. It is best to create an
>adodb.connection, connect, get your recordset, close, and destroy on each
>page individually. But, you can create a sub like so in an include file and
>include it in all your pages that need the connection.
>
>in include:
>
>Dim oADO
>Sub OpenData()
> Set oADO = CreateObject("ADODB.Connection")
> oADO.Open yourConnectionString
>End Sub
>
>Sub CloseData()
> oADO.Close
> Set oADO = Nothing
>End Sub
>
>
>in .asp file:
>Call OpenData()
>Set yourRS = oADO.Execute(YourQuery)
>'''code
>Call CloseData()
>
>
>You can also do things like Function GetRecordSet() in your include that
>would get a recordset, and return it as an array with .GetRows() and things
>along those lines. Whatever you do, do not try to create and open a
>connection and continuously leave it open.
>
>Ray at home
>
>
>"David Raskino" <DavidRaskino@woodstock.ac.in> wrote in message
>news:7E26701A-4D4E-4BC8-8E09-E8A5B90AF8B8@microsoft.com...

>> OBJECTIVE: I am trying to create a connection to an access database in and

>then use that connection ASP pages in my application without having to
>re-connect to the database.

>>
>> I have used the following code in the global.asa file in the

>Session_OnStart sub to create a connection (which works fine):

>> strPath = "C:\Data\WebProjects\GuestBook\db.mdb"
>> Set conSQLServer = Server.CreateObject("ADODB.Connection")
>> conSQLServer.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" &

>strPath

>>
>> However, when I use the following code in another ASP file, the get an

>"object required" error:

>> Set rstData = conSQLServer.Execute ("<sql-statement>")
>> ....which indicates that rstData is not available at application or even

>session level.

>>
>> Any help would be greatly appreciated.
>>
>> Many thanks,
>>
>> David.

>
>

Brynn
[url]www.coolpier.com[/url]

I participate in the group to help give examples of code.
I do not guarantee the effects of any code posted.
Test all code before use!

[Tom Kaminski [MVP]]

"David Raskino" <DavidRaskino@woodstock.ac.in> wrote in message
news:7E26701A-4D4E-4BC8-8E09-E8A5B90AF8B8@microsoft.com...

> OBJECTIVE: I am trying to create a connection to an access database in and

then use that connection ASP pages in my application without having to
re-connect to the database.

>

Bad idea ...
[url]http://www.aspfaq.com/show.asp?id=2053[/url]

--
Tom Kaminski IIS MVP
[url]http://www.iistoolshed.com/[/url] - tools, scripts, and utilities for running IIS
[url]http://mvp.support.microsoft.com/[/url]
[url]http://www.microsoft.com/windowsserver2003/community/centers/iis/[/url]