ADODB.Connection error '800a0e78'

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

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

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.

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

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

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

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

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

Can you show us lines 1-12 also?

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)

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

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

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!

I see where you are getting that, but no, just one connection.  Typo on my part.

very true. :P

Does oConn get assigned in the include file?

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.

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

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

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

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)

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

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

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?

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.
   

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)

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.

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.

Why this is not closed yet???

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

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