Variable Scope and Functions

[tegryan@gmail.com]

Hey All,

I'm trying to do something that should be very simple, but I think I've
been up for too many hours or something.

Basically, I have an asp application that uses a database connection,
and I want to declare (create) that connection in an include and use it
throughout the application in various functions. I've tried making the
connection object a public variable, a constant, making the functions
public, passing the connection object in byRef, byVal, etc... I just
can't get it to work. Is this not possible in classic ASP?

I should also mention that if I take this code out of the function it
works as expected.

Thanks!

Teg Ryan

Here is a sample of what i'm trying to do:

Include:

<%

public cn
public rs
public sql

set cn = Server.CreateOb ject("ADODB.Con nection")
set rs = Server.CreateOb ject("ADODB.Rec ordSet")
cn.Open "Provider=MSDAS QL; Driver={Microso ft Access Driver (*.mdb)};
DBQ=[[path]];"

%>

Then here's one of the functions in the ASP file that has the include
at the top:

Function SQLSelect()

sql = "select * from TABLE"
rs.open sql,cn,3,3
... etc

End function

[Bob Barrows [MVP]]

Re: Variable Scope and Functions

tegryan@gmail.c om wrote:

Hey All,
>
I'm trying to do something that should be very simple, but I think
I've been up for too many hours or something.
>
Basically, I have an asp application that uses a database connection,
and I want to declare (create) that connection in an include and use
it throughout the application in various functions. I've tried
making the connection object a public variable, a constant, making
the functions public, passing the connection object in byRef, byVal,
etc... I just can't get it to work. Is this not possible in classic
ASP?
>
I should also mention that if I take this code out of the function it
works as expected.
>
Thanks!
>
Teg Ryan
>
Here is a sample of what i'm trying to do:
>
Include:
>
<%
>
public cn
public rs
public sql
>
set cn = Server.CreateOb ject("ADODB.Con nection")
set rs = Server.CreateOb ject("ADODB.Rec ordSet")
cn.Open "Provider=MSDAS QL; Driver={Microso ft Access Driver (*.mdb)};
DBQ=[[path]];"

Nothing to do with your problem, but:

http://www.aspfaq.com/show.asp?id=2126

>
%>
>
Then here's one of the functions in the ASP file that has the include
at the top:
>
Function SQLSelect()
>
sql = "select * from TABLE"
rs.open sql,cn,3,3
... etc
>
End function

This should work. What happens when you try it?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[tegryan@gmail.com]

Re: Variable Scope and Functions


Hey Bob, thanks for the quick reply!

Firstly, I thought I was using a native OLEDB provider....lol ! What am
I doing? I haven't created a DSN for that box, and it doesn't look
like the DSNless connections I'm used to...

Secondly and more importantantly, I forgot to leave out the error
message I am getting:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'rs'

I am going to double check right now, but I believe that if I copy the
connection code into the function in question, it works fine.

Thanks!

Teg

Bob Barrows [MVP] wrote:

tegryan@gmail.c om wrote:

Hey All,

I'm trying to do something that should be very simple, but I think
I've been up for too many hours or something.

Basically, I have an asp application that uses a database connection,
and I want to declare (create) that connection in an include and use
it throughout the application in various functions. I've tried
making the connection object a public variable, a constant, making
the functions public, passing the connection object in byRef, byVal,
etc... I just can't get it to work. Is this not possible in classic
ASP?

I should also mention that if I take this code out of the function it
works as expected.

Thanks!

Teg Ryan

Here is a sample of what i'm trying to do:

Include:

<%

public cn
public rs
public sql

set cn = Server.CreateOb ject("ADODB.Con nection")
set rs = Server.CreateOb ject("ADODB.Rec ordSet")
cn.Open "Provider=MSDAS QL; Driver={Microso ft Access Driver (*.mdb)};
DBQ=[[path]];"

>
Nothing to do with your problem, but:

http://www.aspfaq.com/show.asp?id=2126

>

%>

Then here's one of the functions in the ASP file that has the include
at the top:

Function SQLSelect()

sql = "select * from TABLE"
rs.open sql,cn,3,3
... etc

End function

>
This should work. What happens when you try it?
>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[tegryan@gmail.com]

Re: Variable Scope and Functions


yup, works just fine when I put the connection information in the
function.

Also, I should mention that I'm using Access, not SQL Server. Not sure
if that matters...

tegr...@gmail.c om wrote:

Hey Bob, thanks for the quick reply!
>
Firstly, I thought I was using a native OLEDB provider....lol ! What am
I doing? I haven't created a DSN for that box, and it doesn't look
like the DSNless connections I'm used to...
>
Secondly and more importantantly, I forgot to leave out the error
message I am getting:
>
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'rs'
>
I am going to double check right now, but I believe that if I copy the
connection code into the function in question, it works fine.
>
Thanks!
>
Teg
>
Bob Barrows [MVP] wrote:

tegryan@gmail.c om wrote:

Hey All,
>
I'm trying to do something that should be very simple, but I think
I've been up for too many hours or something.
>
Basically, I have an asp application that uses a database connection,
and I want to declare (create) that connection in an include and use
it throughout the application in various functions. I've tried
making the connection object a public variable, a constant, making
the functions public, passing the connection object in byRef, byVal,
etc... I just can't get it to work. Is this not possible in classic
ASP?
>
I should also mention that if I take this code out of the function it
works as expected.
>
Thanks!
>
Teg Ryan
>
Here is a sample of what i'm trying to do:
>
Include:
>
<%
>
public cn
public rs
public sql
>
set cn = Server.CreateOb ject("ADODB.Con nection")
set rs = Server.CreateOb ject("ADODB.Rec ordSet")
cn.Open "Provider=MSDAS QL; Driver={Microso ft Access Driver (*.mdb)};
DBQ=[[path]];"

Nothing to do with your problem, but:

http://www.aspfaq.com/show.asp?id=2126

>
%>
>
Then here's one of the functions in the ASP file that has the include
at the top:
>
Function SQLSelect()
>
sql = "select * from TABLE"
rs.open sql,cn,3,3
... etc
>
End function

This should work. What happens when you try it?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[tegryan@gmail.com]

Re: Variable Scope and Functions


Shoot, one more thing. The file with the function in it is also
another include. In my head this shouldn't matter, but maybe one of
you jeanyuses can help me out here....

The basic structure is this:

main.asp (contains include statements to the other two)
include1.asp (the connection string, set as public)
include2.asp (the function using the connection string that doesn't
work)

Thanks,

Teg Ryan

tegryan@gmail.c om wrote:

yup, works just fine when I put the connection information in the
function.
>
Also, I should mention that I'm using Access, not SQL Server. Not sure
if that matters...
>
tegr...@gmail.c om wrote:

Hey Bob, thanks for the quick reply!

Firstly, I thought I was using a native OLEDB provider....lol ! What am
I doing? I haven't created a DSN for that box, and it doesn't look
like the DSNless connections I'm used to...

Secondly and more importantantly, I forgot to leave out the error
message I am getting:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'rs'

I am going to double check right now, but I believe that if I copy the
connection code into the function in question, it works fine.

Thanks!

Teg

Bob Barrows [MVP] wrote:

tegryan@gmail.c om wrote:
Hey All,

I'm trying to do something that should be very simple, but I think
I've been up for too many hours or something.

Basically, I have an asp application that uses a database connection,
and I want to declare (create) that connection in an include and use
it throughout the application in various functions. I've tried
making the connection object a public variable, a constant, making
the functions public, passing the connection object in byRef, byVal,
etc... I just can't get it to work. Is this not possible in classic
ASP?

I should also mention that if I take this code out of the function it
works as expected.

Thanks!

Teg Ryan

Here is a sample of what i'm trying to do:

Include:

<%

public cn
public rs
public sql

set cn = Server.CreateOb ject("ADODB.Con nection")
set rs = Server.CreateOb ject("ADODB.Rec ordSet")
cn.Open "Provider=MSDAS QL; Driver={Microso ft Access Driver (*.mdb)};
DBQ=[[path]];"
>
Nothing to do with your problem, but:

http://www.aspfaq.com/show.asp?id=2126

>

%>

Then here's one of the functions in the ASP file that has the include
at the top:

Function SQLSelect()

sql = "select * from TABLE"
rs.open sql,cn,3,3
... etc

End function
>
This should work. What happens when you try it?
>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Re: Variable Scope and Functions

No, the data source should not matter.
I'm going to try and reproduce this.

tegryan@gmail.c om wrote:

yup, works just fine when I put the connection information in the
function.
>
Also, I should mention that I'm using Access, not SQL Server. Not
sure if that matters...
>
tegr...@gmail.c om wrote:

>Hey Bob, thanks for the quick reply!
>>
>Firstly, I thought I was using a native OLEDB provider....lol ! What
>am I doing? I haven't created a DSN for that box, and it doesn't
>look like the DSNless connections I'm used to...
>>
>Secondly and more importantantly, I forgot to leave out the error
>message I am getting:
>>
>Error Type:
>Microsoft VBScript runtime (0x800A01A8)
>Object required: 'rs'
>>
>I am going to double check right now, but I believe that if I copy
>the connection code into the function in question, it works fine.
>>
>Thanks!
>>
>Teg
>>
>Bob Barrows [MVP] wrote:

>>tegryan@gmail.c om wrote:
>>>Hey All,
>>>>
>>>I'm trying to do something that should be very simple, but I think
>>>I've been up for too many hours or something.
>>>>
>>>Basically, I have an asp application that uses a database
>>>connection , and I want to declare (create) that connection in an
>>>include and use it throughout the application in various
>>>functions. I've tried making the connection object a public
>>>variable, a constant, making the functions public, passing the
>>>connection object in byRef, byVal, etc... I just can't get it to
>>>work. Is this not possible in classic ASP?
>>>>
>>>I should also mention that if I take this code out of the function
>>>it works as expected.
>>>>
>>>Thanks!
>>>>
>>>Teg Ryan
>>>>
>>>Here is a sample of what i'm trying to do:
>>>>
>>>Include:
>>>>
>>><%
>>>>
>>>public cn
>>>public rs
>>>public sql
>>>>
>>>set cn = Server.CreateOb ject("ADODB.Con nection")
>>>set rs = Server.CreateOb ject("ADODB.Rec ordSet")
>>>cn.Open "Provider=MSDAS QL; Driver={Microso ft Access Driver
>>>(*.mdb)}; DBQ=[[path]];"
>>>
>>Nothing to do with your problem, but:
>>http://www.aspfaq.com/show.asp?id=2126
>>>
>>>>
>>>%>
>>>>
>>>Then here's one of the functions in the ASP file that has the
>>>include at the top:
>>>>
>>>Function SQLSelect()
>>>>
>>> sql = "select * from TABLE"
>>> rs.open sql,cn,3,3
>>> ... etc
>>>>
>>>End function
>>>
>>This should work. What happens when you try it?
>>>
>>--
>>Microsoft MVP -- ASP/ASP.NET
>>Please reply to the newsgroup. The email account listed in my From
>>header is my spam trap, so I don't check it very often. You will
>>get a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Re: Variable Scope and Functions

tegryan@gmail.c om wrote:

Hey Bob, thanks for the quick reply!
>
Firstly, I thought I was using a native OLEDB provider....lol !

Nope. You're using the MSDASQL provider, which is the provider for ODBC
databases.

What
am I doing? I haven't created a DSN for that box, and it doesn't look
like the DSNless connections I'm used to...

Well, that's what it is: a DSNless connection

>
Secondly and more importantantly, I forgot to leave out the error
message I am getting:
>
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'rs'
>
I am going to double check right now, but I believe that if I copy the
connection code into the function in question, it works fine.
>
Thanks!
>
Teg

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[tegryan@gmail.com]

Re: Variable Scope and Functions


Lol! Well if you can't solve this for me, at least you've taught me
something already.

To replicate it, all you need to do is create a DSNless connection like
the one I have, put it in an include and try to use the rs/cn object
from inside a function. I've narrowed it down to that behaviour, but
can't get past it without doing something ugly....

Bob Barrows [MVP] wrote:

tegryan@gmail.c om wrote:

Hey Bob, thanks for the quick reply!

Firstly, I thought I was using a native OLEDB provider....lol !

>
Nope. You're using the MSDASQL provider, which is the provider for ODBC
databases.
>

What
am I doing? I haven't created a DSN for that box, and it doesn't look
like the DSNless connections I'm used to...

>
Well, that's what it is: a DSNless connection

Secondly and more importantantly, I forgot to leave out the error
message I am getting:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'rs'

I am going to double check right now, but I believe that if I copy the
connection code into the function in question, it works fine.

Thanks!

Teg

>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Re: Variable Scope and Functions

tegryan@gmail.c om wrote:

yup, works just fine when I put the connection information in the
function.
>

Here's my first repro attempt, which works with no error:

ins.asp:
<%
option explicit

public cn
public rs
public sql

set cn = Server.CreateOb ject("ADODB.Con nection")
set rs = Server.CreateOb ject("ADODB.Rec ordSet")
cn.Open "Provider=micro soft.jet.oledb. 4.0;" & _
"data source=" & server.MapPath( "xxxxx.mdb" )
%>

insuse.asp:
<!--#include file="ins.asp"-->
<%
function useconnection()
sql="select top 1 * from mytable"
rs.open sql,cn,,,1
useconnection=r s(0).value
end function
response.write useconnection
rs.close:set rs=nothing
cn.close:set cn=nothing
%>

I'll try your other scenario later.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Re: Variable Scope and Functions

tegryan@gmail.c om wrote:

yup, works just fine when I put the connection information in the
function.
>
Also, I should mention that I'm using Access, not SQL Server. Not
sure if that matters...
>

And here is my second repro attempt, which again works just fine:
ins.asp:
<%
option explicit

public cn
public rs
public sql

set cn = Server.CreateOb ject("ADODB.Con nection")
set rs = Server.CreateOb ject("ADODB.Rec ordSet")
cn.Open "Provider=micro soft.jet.oledb. 4.0;" & _
"data source=" & server.MapPath( "xxxxx.mdb" )
%>

ins2.asp:
<%
function useconnection()
sql="select top 1 * from mytable"
rs.open sql,cn,,,1
useconnection=r s(0).value
end function
%>



insuse.asp:
<!--#include file="ins.asp"-->
<!--#include file="ins2.asp"-->
<%
response.write useconnection
rs.close:set rs=nothing
cn.close:set cn=nothing
%>


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[tegryan@gmail.com]

Re: Variable Scope and Functions


Hey Bob,

I'm getting more and more baffled. I tried it again with the preferred
connection method you pointed me to (even tho I didn't expect it to
change anything) and it still doesn't work.

Your example and mine are almost identical. There are two things I see
different:

One is the rs line:

rs.open sql,cn,,,1

vs what I use

rs.open sql,cn,3,3

Could that be causing the problems?

The only other thing I could think of is a setting on the server (which
is IIS) but I have to believe that's highly unlikely.

To confuse and baffle me more, I tried the exact same thing with a
simple string, and I could access it no problem within the function.
So it has to be something with my ADO, I would imagine.

Thanks,

Teg

Bob Barrows [MVP] wrote:

tegryan@gmail.c om wrote:

Hey Bob, thanks for the quick reply!

Firstly, I thought I was using a native OLEDB provider....lol !

>
Nope. You're using the MSDASQL provider, which is the provider for ODBC
databases.
>

What
am I doing? I haven't created a DSN for that box, and it doesn't look
like the DSNless connections I'm used to...

>
Well, that's what it is: a DSNless connection

Secondly and more importantantly, I forgot to leave out the error
message I am getting:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'rs'

I am going to double check right now, but I believe that if I copy the
connection code into the function in question, it works fine.

Thanks!

Teg

>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Re: Variable Scope and Functions

tegryan@gmail.c om wrote:

Lol! Well if you can't solve this for me, at least you've taught me
something already.
>
To replicate it, all you need to do is create a DSNless connection
like the one I have, put it in an include and try to use the rs/cn
object from inside a function. I've narrowed it down to that
behaviour, but can't get past it without doing something ugly....
>

I would prefer to use a function to get the connection so you are only
opening the connection when you need it, rather than opening it when the
page loads.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Re: Variable Scope and Functions

tegryan@gmail.c om wrote:

Hey Bob,
>
I'm getting more and more baffled. I tried it again with the
preferred connection method you pointed me to (even tho I didn't
expect it to change anything) and it still doesn't work.
>
Your example and mine are almost identical. There are two things I
see different:
>
One is the rs line:
>
rs.open sql,cn,,,1
>
vs what I use
>
rs.open sql,cn,3,3

All right, I'll try that ... nope, still works fine.

>
Could that be causing the problems?

I don't think so. You should try copying and pasting my code,
substituting the name of your database into the connection string.

Also, try verifying that the connection is open before opening the
recordset.

>
The only other thing I could think of is a setting on the server
(which is IIS) but I have to believe that's highly unlikely.

ditto

>
To confuse and baffle me more, I tried the exact same thing with a
simple string, and I could access it no problem within the function.
So it has to be something with my ADO, I would imagine.
>

I don't think so ... at least I can;'t imagine anything in ADO that
would affect variable scope.

Try this:
function useconnection()
sql="select top 1 * from mytable"
if cn.state= 1 then
'rs.open sql,cn,3,3,1
set rs=cn.execute(s ql,,1)
useconnection=r s(0).value
else
useconnection=" cn.state=" & cn.state
end if
end function


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[tegryan@gmail.com]

Re: Variable Scope and Functions


omg Bob, I really want to msg you in private about this, but anyone
reading should probably know the conclusion to this. I have a block of
15 or so includes, to sort of replicate OOP as much as possible with
classes and whatnot, and the last one in the list is
"DBCloseConn.as p"... They ALL are at the top of my script, needless to
say trying to execute a sql statement on a closed connection is not
going to work. I think the code worked when I put it directly in the
page because as force of habit I put ASP code above all else, including
that include.

Argh, sorry to waste your time, but I believe it's resolved.

Thanks for your effort!

Teg Ryan

tegryan@gmail.c om wrote:

Lol! Well if you can't solve this for me, at least you've taught me
something already.
>
To replicate it, all you need to do is create a DSNless connection like
the one I have, put it in an include and try to use the rs/cn object
from inside a function. I've narrowed it down to that behaviour, but
can't get past it without doing something ugly....
>
Bob Barrows [MVP] wrote:

tegryan@gmail.c om wrote:

Hey Bob, thanks for the quick reply!
>
Firstly, I thought I was using a native OLEDB provider....lol !

Nope. You're using the MSDASQL provider, which is the provider for ODBC
databases.

What
am I doing? I haven't created a DSN for that box, and it doesn't look
like the DSNless connections I'm used to...

Well, that's what it is: a DSNless connection

>
Secondly and more importantantly, I forgot to leave out the error
message I am getting:
>
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'rs'
>
I am going to double check right now, but I believe that if I copy the
connection code into the function in question, it works fine.
>
Thanks!
>
Teg

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Re: Variable Scope and Functions

tegryan@gmail.c om wrote:

omg Bob, I really want to msg you in private about this, but anyone
reading should probably know the conclusion to this. I have a block
of 15 or so includes, to sort of replicate OOP as much as possible
with classes and whatnot, and the last one in the list is
"DBCloseConn.as p"... They ALL are at the top of my script, needless
to say trying to execute a sql statement on a closed connection is not
going to work. I think the code worked when I put it directly in the
page because as force of habit I put ASP code above all else,
including that include.
>
Argh, sorry to waste your time, but I believe it's resolved.
>
Thanks for your effort!
>

And now we see the importance of providing repro code instead of
describing what you are doing ...

I would prefer to use a subroutine called CloseConnection to do what
that one is doing. I can see you are trying to eliminate the possibility
of forgetting to close the connect at the end of the page, but have you
considered the possibility of an error preventing that code from
running? If you used a subroutine, you could trap your errors, and in
the error-handler, call the CloseConnection sub.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.