Solved

ADODB.Connection error '800a0e78'

Posted on 2002-03-04
30
346 Views
Last Modified: 2012-05-05
I call the following function to open a database connection.

Function CLib_OpenForumDB()
     On Error Resume Next
     Set conn = Server.CreateObject("ADODB.Connection")
     conn.ConnectionTimeout = 15
     conn.CommandTimeout = 30

     With conn
          .Open Application("conn")
     End With
     
     If Err Then
          Response.Clear
          Response.Redirect ("/outage.asp")
     End If

     set cmd = Server.CreateObject("ADODB.Command")
     set rs = Server.CreateObject("ADODB.Recordset")
     set cmd.activeconnection = conn
End Function

My connection is obviously stored in an application variable (Application("conn")), which looks like this:

conn = Application("conn")
If IsEmpty(conn) Then
Application("conn") = "Provider=MSDASQL.1; Persist Security Info=False; Data Source=DSNName"
End If

Here's where the problem starts. When I download my database from the server, make changes (or don't make any changes), then upload it back to the server, I get the following error.

ADODB.Connection error '800a0e78'
Operation is not allowed when the object is closed.
Default.asp, line 15

Line 15 looks like:

Line 13  vSQL = "SELECT ID FROM SaleCount_VW"
Line 14  CLib_OpenForumDB()
Line 15  Set rsResults = oConn.Execute (vSQL)

This error will exist for HOURS, sometimes more, unless I can get a tech support guy to unload all the DB objects. For some reason that fixes the problem. I created outage.asp so people do not see this ugly error, but if I comment that code out, you see the above error. Can someone suggest to me a way to avoid this? I am desperate.

Thanks,
Frank
0
Comment
Question by:ftbadolato
  • 9
  • 6
  • 5
  • +6
30 Comments
 
LVL 20

Expert Comment

by:jitganguly
ID: 6839348
>>conn.ConnectionTimeout = 15
    conn.CommandTimeout = 30

Could be the culprits . Since you are downloading/uploading, it is eitehr taking longer than 15/30 minutes.
I would suggest you to close once you are done and reopen when you again want to do db activities.
0
 

Author Comment

by:ftbadolato
ID: 6839359
Well...I actually have another function that I use and it happens with that one as well.  Here it is:

Function CLib_OpenDB()
     On Error Resume Next
     Set oConn = Server.CreateObject("ADODB.Connection")

     With oConn
          .Open Application("oConn")
     End With
     If Err Then
          Response.Clear
          Response.Redirect ("/outage.asp")
     End If
End Function

There are no timeouts specified with this connection. Do you still think that could be a problem?

Frank
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6839363
If you do not specify then its default which are 15 and 30 respectively. But is it taking more than 15/30 ? WHy not close and reopen whenever required.
0
 

Author Comment

by:ftbadolato
ID: 6839375
Are you asking if it is taking more than 15 minutetes to download/make changes/upload? Sometimes it does, but there are times that I can do everything in 5 or 10 minutes as well.

At the end of every page, I do the following.

If oConn <> "" Then
     oConn.Close
     Set oConn = Nothing
End If

So, unless I am missing something, I believe I am closing and reopening whenever required, am I not? I prefer to keep the connection in an app variable b/c it is much faster.

Frank
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6839397
>>If oConn <> "" Then
    oConn.Close
    Set oConn = Nothing
End If

Does it really get fired ? Try

If not isObject(oConn) Then
    oConn.Close
    Set oConn = Nothing
End If

Also
if RS.State = &H00000001 then 'its open
  RS.Close
end if
         
0
 
LVL 2

Expert Comment

by:jsmckenzie
ID: 6839484
Looks to me that the scope of the Connection object is local to the Function CLib_OpenDB() function, so the oConn variable never gets assigned anything.

Try setting the value of the function to the object, then oConn to the value of the function, such as:

Function CLib_OpenDB()
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open Application("oConn")
    CLib_OpenDB = oConn
End Function

Then change line 14 to:

oConn = CLib_OpenDB()


Hope that helps.

Jeff

0
 

Author Comment

by:ftbadolato
ID: 6839763
I arranged the closing code like the following:

If oConn <> "" Then
     Response.Write ("close it!")
     oConn.Close
     Set oConn = Nothing
End If

If Not IsObject(oConn) Then
     Response.Write ("close it again!")
     oConn.Close
     Set oConn = Nothing
End If

"close it" was written to the screen. Then, I rearanged them to:

If Not IsObject(oConn) Then
     Response.Write ("close it!")
     oConn.Close
     Set oConn = Nothing
End If

If oConn <> "" Then
     Response.Write ("close it again!")
     oConn.Close
     Set oConn = Nothing
End If

"close it again!" was written to the screen.

This tells me that my original way of closing oConn was working, but the way you suggested did not close anything. Agree?

Frank
0
 

Author Comment

by:ftbadolato
ID: 6839771
jsmckenzie,

If oConn never got assigned anything, the code would never work. The only time it does not work is from the period that it gets uploaded to the server until the tech support guys unload all the db objects for me, or they unload themselves.

Thanks,
Frank
0
 
LVL 1

Expert Comment

by:SuperLeon
ID: 6839902
Can you show us lines 1-12 also?
0
 

Author Comment

by:ftbadolato
ID: 6840046
Sure, they look something like:

<%@ ENABLESESSIONSTATE=False %>
<% Option Explicit
Response.Buffer = True %>

<!--#include virtual="/includes/Lib.asp"-->

<% Dim vSQL
Dim rsResults
Dim rsCount,intRowNum,intRowCount

vSQL = "SELECT ID FROM SaleCount_VW"

CLib_OpenDB()

Set rsResults = oConn.Execute (vSQL)
0
 
LVL 1

Expert Comment

by:loveneesh_bansal
ID: 6840916
Actualy when your conn is empty then you are passing the connection information once again. But you are not opening your connection that is the only problem simplt check if conn.state=1 then
else
open the connection

end if

bye
0
 
LVL 2

Expert Comment

by:manihopever
ID: 6841563
make sure you are using two times the CLib_OpenForumDB().
i just changed your code that checks for connection already exist.

Function CLib_OpenForumDB()
    On Error Resume Next
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.ConnectionTimeout = 15
    conn.CommandTimeout = 30
   
    'checking whether connection already exist or not
    if conn.state<>1 then  
      With conn
         .Open Application("conn")
      End With
    end if
    If Err Then
         Response.Clear
         Response.Redirect ("/outage.asp")
    End If

    set cmd = Server.CreateObject("ADODB.Command")
    set rs = Server.CreateObject("ADODB.Recordset")
    set cmd.activeconnection = conn
End Function


try this and get back to me soon...!
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 6841892
Are you opening two connections to two different databases?

If so, check that you are using the right connection with the right query. On line 14 you are calling a sub that set upp the connection conn, then on line 15 you are using the connection oConn...

If you are creating two connections to the same database... dont!
0
 

Author Comment

by:ftbadolato
ID: 6842050
I see where you are getting that, but no, just one connection.  Typo on my part.

0
 
LVL 2

Expert Comment

by:jsmckenzie
ID: 6842085
very true. :P

Does oConn get assigned in the include file?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Expert Comment

by:jsmckenzie
ID: 6842124
Another try...

Since you are using one connection object in an Application variable for every connection, it's possible that one web user closes the connection while another user is attempting to execute.
0
 

Author Comment

by:ftbadolato
ID: 6842294
Yeah, I am not sure how the app variable works.  I thought that having it stored in global.asa meant that once it was opened once, it was always open until the server gets reset.  So, the connection is reused.  If this is true, then why do I close oConn at the bottom of every page.  I read everywhere that this is what I am supposed to do, but I am not sure if they were considering that I might be storing the connection in an application variable.

Frank
0
 
LVL 2

Expert Comment

by:jsmckenzie
ID: 6842459
The application variable does hold its initial value until the web server is reset, but storing objects in them can create performance bottlenecks, thread contention, and other unexpected behavior.

The usual recommendation is to store the connection string, rather than the object, in an application variable.  Opening and closing an ADO Connection on the same page frees up connections to be used in the connection pool, which is a more efficient process.

Try creating and closing a connection on each page to see if that helps.

Jeff
0
 

Author Comment

by:ftbadolato
ID: 6842513
Well, I made the "checking" changes, and it seemed to work at first.  I am usually only able to open one page before it bombs after I upload the database back to the server, but this time I opened 3 or 4.  

Oh, and yes, oConn is defined in the include file.

This is an issue between the app variable, the server (which I have no control over), and the connection object. I am going to up the points to see if I can get any additional attention.  :)
0
 
LVL 2

Accepted Solution

by:
jsmckenzie earned 300 total points
ID: 6842851
Here's how I might do it; maybe it will clear up what I'm talking about.

In the global.asa you have:

Application("conn") = "Provider=MSDASQL.1; Persist Security Info=False; Data Source=DSNName"

then,

Function CLib_OpenForumDB(conn, rs, cmd)
    On Error Resume Next
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.ConnectionTimeout = 15
    conn.CommandTimeout = 30

    conn.Open Application("conn")
   
    If Err Then
         Response.Clear
         Response.Redirect ("/outage.asp")
    End If

    set cmd = Server.CreateObject("ADODB.Command")
    set rs = Server.CreateObject("ADODB.Recordset")
    set cmd.activeconnection = conn

End Function

Function CLib_CloseForumDB(conn, rs)
    On Error Resume Next
   
    rs.Close
    conn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing

End Function


<%@ ENABLESESSIONSTATE=False %>
<% Option Explicit
Response.Buffer = True %>


Dim oConn
Dim rsResults
Dim oCmd
Dim oRS
Dim vSQL

vSQL = "SELECT ID FROM SaleCount_VW"

CLib_OpenDB(oConn, rsResults, oCmd)

Set rsResults = oCmd.Execute(vSQL)

CLib_CloseDB(oConn, rsResults, oCmd)


Hope this helps some...

Jeff
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 6844959
That looks excelent. That's more or less how I do all database calls. I never use a command object, and mostly use disconnected recordsets, but that's about it.

What's stored in the application variable is just the connection string. Perfect.

Function CLib_CloseForumDB(conn, rs)
should be:
Function CLib_CloseForumDB(conn, rs, cmd)
0
 
LVL 1

Expert Comment

by:SuperLeon
ID: 6845110
Suggestion:

Put the code below in an include file called db_functions.asp and include it on ALL your pages.

<%
'db_functions.asp
'---------------------------------------------------------
sub open_DBConn()
     
  set dbConn= server.CreateObject("ADODB.CONNECTION")
  dbConn.Provider = "SQLOLEDB"

  'you will have to change the connection string for your db
  dbConn.ConnectionString = "Driver={SQL Server};server="myServer;databasemyDB;uid=blah;pwd=blahblah;"
 
  dbConn.Open
end sub

sub close_DBConn()
  dbConn.close
  set dbConn= nothing
end sub

'---------------------------------------------------------

'subroutines that open and close the database RS object
sub open_myRS(ByRef rsName, strSQL)
  set rsName = server.CreateObject("ADODB.RECORDSET")
  rsName.Open strSQL, dbConn
end sub

sub close_myRS(ByRef rsName)
  rsName.close
  set rsName = nothing
end sub

'---------------------------------------------------------

'subroutine that executes a command on the database
sub run_cmd(strSQL)
  set DBcmd = server.CreateObject("ADODB.COMMAND")
  DBcmd.ActiveConnection = dbConn
  DBcmd.CommandText = strSQL
  DBcmd.Execute
  set DBcmd = nothing
end sub
%>

In each page that accesses your database, you can use the following code.

<%
call open_DBConn()  'opens connection object

'opening and using a recordset
SQL = "SELECT * FROM MYTABLE"
call open_myRS(rsFoo, SQL)  'opens recordset object
do while not rsFoo.EOF
  'do whatever here
  rsFoo.MoveNext
loop
call close_myRS(rsFoo)  'closes recordset obj specified

'running a command
SQL = "INSERT TEST(FIELD1) VALUES('BLAH')"
call run_cmd(SQL)  'runs sql statement specified

call close_DBConn()  'closes connection object

%>

just a suggestion...speeds coding up and has never caused me a problem.

not sure if it'll solve your issue though

-Leon
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 6845441
I believe that Jeff is already using an include file for his database functions.

(Come to think of it, those functions should be Sub's, Jeff, as the don't return any values.)

Dont take this personal, Leon, but I am kind of alergic to using global variables inside subs... Jeff's extensive use of parameters makes the code much easier to follow.

(As I am currently working in a huge project that some other people has built, I really love code that is easy to read... for a change... ;)
0
 
LVL 1

Expert Comment

by:SuperLeon
ID: 6845544
Understandable, but (unless I'm not seeing something) what if you had to open and close many recordsets on a page...like say 20, then with those functions, wouldn't you have to open 20 different connections to the database [ie: a seperate one for each recordset]? Would that be efficient?
0
 
LVL 2

Expert Comment

by:jsmckenzie
ID: 6845588
Those open/close functions would definitely be in an include file.  I'd also normally return true or false from the function to catch connection errors, such as

If CLib_OpenDB(oConn, rsResults, oCmd) Then
   Set rsResults = oCmd.Execute(vSQL)
Else
   Do error stuff...
End If

And I wrote the functions assuming there was only one recordset, otherwise I'd create an OpenConn function or something like that.  Just tried to make it simple for this example.  I agree that it wouldn't be efficient to open/close a connection 20 times in a page.
   


0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 6845602
No, Leon, you only have to open one connection. You can use the same command object to create any number of recordsets. But you made me notice something else:

Actually, Jeff, the recordset you create in the CLib_OpenDB function is not used:

' This call creates, among others, a recordset object:
CLib_OpenDB(oConn, rsResults, oCmd)

' Here, the Execute function returns a recordset object,
' so the object created above is discarded, and replaced
' by the new object:
Set rsResults = oCmd.Execute(vSQL)
0
 

Author Comment

by:ftbadolato
ID: 6847602
I wish I better understood a lot of this.  :)  I am willing to try the best suggesiton(s).  Can anyone compile all these suggestions into one really good one so I can follow it better?  Right now, I store all my functions in an include file.

They are as follows:

Function CLib_OpenDB()
     On Error Resume Next
     Set oConn = Server.CreateObject("ADODB.Connection")

     If oConn.State <> 1 Then
          With oConn
               .Open Application("oConn")
          End With
     End If
     
     If Err Then
          Response.Clear
          Response.Redirect ("/outage.asp")
     End If
End Function

The app variable "oConn" is on global.asa as:

Sub Application_OnStart()    
     oConn = Application("oConn")
     If IsEmpty(oConn) Then
          Application("oConn") = "Provider=MSDASQL.1; Persist Security Info=False; Data Source=DSNName"
     End If
End Sub

I have a footer sub routine that I run at the end of every page that contains:

If oConn <> "" Then
     oConn.Close
     Set oConn = Nothing
End If

I never close the record set anywhere.  Should I be?  Should I be closing oConn before the end of the page? I often have multiple record sets opening on one page, and they often have different names. I almost always have rsResults, but there are others at times.
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 6847772
There is no need to check if the application variable contains anything before you put the string into it. Just do like this:

Sub Application_OnStart()    
    Application("oConn") = "Provider=MSDASQL.1; Persist Security Info=False; Data Source=DSNName"
End Sub

---

It's good to pass the connection object to the function that opens it, for two reasons: It's easier to follow the code, and if you ever want to open more than one connection, you can still use the same code. Also, when you create a connection object, it's never connected, so you don't have to check that.

Sub CLib_OpenDB(byRef oConn)
    On Error Resume Next
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open Application("oConn")
    If Err Then
         Response.Clear
         Response.Redirect "/outage.asp"
    End If
End Sub

Use it like:

Dim oConn
Call CLib_OpenDB(oConn)

---

It's good to close the recordsets as soon as you don't need them any more. It preserves resources for the database server, and in some cases the recordsets doesn't automatically get closed properly when the page code ends.
0
 
LVL 6

Expert Comment

by:Programming_Gal
ID: 8707548
Why this is not closed yet???

http://www.aspfaq.com/show.asp?id=2307
0
 
LVL 7

Expert Comment

by:lavinder
ID: 8855129
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

accept a comment - jsmckenzie
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
lavinder
EE Cleanup Volunteer
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now