|
Home > Archive > IIS ASP > February 2005 > Help with ADODB.Connection problem...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Help with ADODB.Connection problem...
|
|
| June Moore 2005-02-28, 8:50 pm |
| 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] 2005-02-28, 8:50 pm |
| 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/e...ml/pooling2.asp
http://support.microsoft.com/?scid=kb;en-us;Q176056
http://support.microsoft.com/defaul...kb;en-us;191572
http://support.microsoft.com/defaul...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
--
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"
|
|
|
|
|