Variable Scope and Functions

[tegryan]

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.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")
cn.Open "Provider=MSDASQL; Driver={Microsoft 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]]

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.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")
cn.Open "Provider=MSDASQL; Driver={Microsoft 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?

 

[tegryan]

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

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.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")
cn.Open "Provider=MSDASQL; Driver={Microsoft 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]

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...

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

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.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")
cn.Open "Provider=MSDASQL; Driver={Microsoft 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]

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

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...

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

(e-mail address removed) 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.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")
cn.Open "Provider=MSDASQL; Driver={Microsoft 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]]

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

 

[Bob Barrows [MVP]]

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

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...

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

(e-mail address removed) 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.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")
cn.Open "Provider=MSDASQL; Driver={Microsoft 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]

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]]

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.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")
cn.Open "Provider=microsoft.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=rs(0).value
end function
response.write useconnection
rs.close:set rs=nothing
cn.close:set cn=nothing
%>

I'll try your other scenario later.

 

[Bob Barrows [MVP]]

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.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")
cn.Open "Provider=microsoft.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=rs(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
%>

 

[tegryan]

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]]

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.

 

[Bob Barrows [MVP]]

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(sql,,1)
useconnection=rs(0).value
else
useconnection="cn.state=" & cn.state
end if
end function

 

[tegryan]

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.asp"... 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

 

[Bob Barrows [MVP]]

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.asp"... 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.

 

[tegryan]

Those are all good suggestions. The requirements on this project moved
so fast I haven't changed the plans to accomodate a lot of it, hence
the scrambled code and scrambled brain. I'm a walking ad for planning
in programming...

Thanks for your help Bob!!!

Teg Ryan