By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,854 Members | 849 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,854 IT Pros & Developers. It's quick & easy.

Help with ADODB.Connection problem...

P: n/a
Hi,
I am getting an error with the following code.
Error is: ADODB.Connection error '800a0e78' - The operation requested
by the application is not allowed if the object is closed.

Any ideas how the error can be fixed?
Extract of the ASP code below...

<%
dim dbprop, dbconn, dbsessionname
dbprop = "dsn=stock;uid=test;pwd=mem"
dbsessionname = "stock_conn"
monsql = "select count(*) from test_monitor"

' Check database connection
if isobject(session(dbsessionname)) then
reuseDBConnection()
else
doDBConnection()
end if

sub doDBConnection()
set dbconn = server.createobject("ADODB.connection")

' turn on error handling
on error resume next

' reset error
set err.number = 0

' open connection
dbconn.open dbprop

if err.number <> 0 then
Session.Contents.Remove(dbsessionname)
else
set session(dbsessionname) = dbconn
end if

' turn off error handling
on error goto 0

end sub

sub reuseDBConnection()
set dbconn = session(dbsessionname)

' turn on error handling
on error resume next

' reset error
set err.number = 0

' test if connection is valid
rsmon = dbconn.execute(monsql)
''rsmon = null

' if connection is invalid, we create a fresh connection.
if err.number <> 0 then
Session.Contents.Remove(dbsessionname)
doDBConnection()
end if

' turn off error handling
on error goto 0

end sub

%>

Thanks,
June.......
Jul 22 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
June Moore wrote:
Hi,
I am getting an error with the following code.
Error is: ADODB.Connection error '800a0e78' - The operation requested
by the application is not allowed if the object is closed.

Any ideas how the error can be fixed?
It would help if you pointed out the line that generates the error. However
....
Extract of the ASP code below...

<%
dim dbprop, dbconn, dbsessionname
dbprop = "dsn=stock;uid=test;pwd=mem"
dbsessionname = "stock_conn"
monsql = "select count(*) from test_monitor"

' Check database connection
if isobject(session(dbsessionname)) then
Uh-oh ....
reuseDBConnection()
else
doDBConnection()
end if <snip> sub reuseDBConnection()
set dbconn = session(dbsessionname)

I knew it!!!
BAD. Bad, very bad
http://www.aspfaq.com/show.asp?id=2053

ADO uses OLE DB to connect to data sources. OLE DB has a feature called
Session Pooling which greatly increases performance and minimizes the number
of connections to your databases.

http://msdn.microsoft.com/library/en...l/pooling2.asp
http://support.microsoft.com/?scid=kb;en-us;Q176056
http://support.microsoft.com/default...b;en-us;191572
http://support.microsoft.com/default...b;en-us;324686
Storing your connection in session prevents you from taking advantage of
pooling.
Simplify your code. Use an OLE DB connection string
(http://www.aspfaq.com/show.asp?id=2126). Open your connection immediately
before using it. Close it immediately when finished using it.

<%
dim cn, rs, sSQL
sSQL="select count(*) from test_monitor"
set cn=createobject("adodb.connection")
cn.open "<OLE DB connection string>"
set rs=cn.Execute(sSQL,,1)
if not rs.eof then
Response.Write "test_monitor contains " & rs(0) & "row(s)"
end if
rs.close:set rs=nothing
cn.close:set cn=nothing
%>

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.