Help with ADODB.Connection problem...

[June Moore]

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

 

[Bob Barrows [MVP]]

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

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-us/dnmdac/html/pooling2.asp
http://support.microsoft.com/?scid=kb;en-us;Q176056
http://support.microsoft.com/default.aspx?scid=kb;en-us;191572
http://support.microsoft.com/default.aspx?scid=kb;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