Help with ADODB.Connection problem...

Discussion in 'ASP General' started by June Moore, Mar 1, 2005.

  1. June Moore

    June Moore Guest

    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.......
    June Moore, Mar 1, 2005
    #1
    1. Advertising

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

    --
    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"
    Bob Barrows [MVP], Mar 1, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. PJ
    Replies:
    1
    Views:
    1,782
    George Ter-Saakov
    Oct 30, 2003
  2. Steven Baeten

    ADODB.connection safety settings error

    Steven Baeten, May 7, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    2,815
    Mary Chipman
    May 9, 2004
  3. Andy G

    ADODB.Connection closing issues

    Andy G, Nov 14, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    4,102
    Andy G
    Nov 14, 2005
  4. Joe Befumo

    Type 'ADODB.Connection' is not defined.

    Joe Befumo, Feb 19, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    6,889
    Joe Befumo
    Feb 19, 2006
  5. Replies:
    2
    Views:
    622
    Mudcat
    Jun 3, 2005
Loading...

Share This Page