?
Solved

Database connection not closing with objConn.Close & Set objConn=Nothing

Posted on 2004-11-12
12
Medium Priority
?
958 Views
Last Modified: 2008-05-21
I have an asp page that connects to a MS Access Database using dsn.
Upon opening the page for the first time, the page opens successfully.
Opening the same page at any point in the next 60 seconds produces a:
"HTTP 500.100 - Internal Server Error - ASP error" message
After 60 seconds, the first subsequesnt refresh of the page will work.


I'd originally thought I'd forgotten to include database closing code along the lines of
  objConn.Close
  Set objConn = Nothing

       - but they're there.


I've commented out all relevant code now except declarations and the following:

  Set objConn = Server.CreateObject("ADODB.Connection")
  objConn.ConnectionString = "DSN=xxx.dsn"
  objConn.Open
  'commented out code
  objConn.Close
  Set objConn = Nothing

The problem still persists.
I suspect this relates to some form of timeout property either in IIS or within the MS Access database but I'd rather resolve it properly than muck around with setting timeouts to near zero and calling that a fix.

One bit to add: the remaining code writes files to disk but at present has absolutely nothing to do with the portion of code which opens/closes the database connection - not sure if that has any impact.

Any ideas?

0
Comment
Question by:Beamson
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 12565059
it could be a loop

have u tried

<% on error resume next %> - but i suppose using this will just make the page hand til it times out

I experience the same problem yesterday strangely enough but i cant remember for the life of me how i over came it, ill try and rememebr and post back....Damn!...
0
 
LVL 1

Author Comment

by:Beamson
ID: 12565169
Thanks apresto,

I think this might be barking up the wrong tree.
After commenting code out, what remains is so minimal that it simply opens a connection to a database, closes it, then outputs some plain HTML.  There simply is nothing to loop through.

I'm sure the fairly regular 60 second delay between it working is the key to this.
Whilst it's not working, something is happening which takes 60 seconds to time out.
I just can't work out what.
0
 
LVL 9

Accepted Solution

by:
rg20 earned 600 total points
ID: 12565467
I know this you probably did this.  

Turn off "friendly http error messages" in your internet explorer window.  This might give you a more detailed message.

Second is try the connection using a dsnless connection, this will rule out the dsn problem and possibly tie it to IIS.

If it iis, then in the IIS administration, click on the properties of the "Default Web Site", "Home Directory" Tab, "Configuration" Button, and "App Options" tab.  There you will find a session timeout.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Beamson
ID: 12565694
Hi RG20 (that's not a name based on postcodes is it? - That's the prefix to our works postcode in the small village of Burghclere!)

I've turned off friendly http error messages but the quality of error message doesn't appear to have changed.

I will try the DSN-less connection in a few minutes once I've remembered how to (been using DSN for far too long...).

I really want to avoid reducing session timeout values if they're only going to tackle the result of the problem rather than the problem itself.  If my code is leaving some form of connection open which should be closed, I'd rather fix that than tell IIS to disconnect me earlier.
0
 
LVL 9

Expert Comment

by:rg20
ID: 12565750
no no postcodes.  Just my initials with a number at the end.  I picked it a long time ago and continue to use it where I can.

I am not suggesting that you shorten the timeout values, just to verify that they are not short already.  The default is 20 minutes.

The other thing I do in IIS administration is

In the IIS administration, click on the properties of the "Default Web Site", "custom errors" Tab, Select all the http errors and click "Set to Default"

I don't like IIS 5 settings as they are trying to hide the ugly asp errors from the user.
0
 
LVL 1

Author Comment

by:Beamson
ID: 12566052
99% SUCCESS!

The timeout values on IIS are all at default values.
I made the suggested change to default error messages which resulted in the problem still happening but with a simpler error message pointing to the line which has "objConn.Open" on it.

Then I replaced the lines specifying the DNS connection with the non-DNS equivalent - and it's started working again!  I can refresh the page as often as I like now and it works properly each time.

Whilst I don't need DNS, it would be interesting to know why it seems to be causing this problem.  Anyone know the ins and outs of DNS and how one tinkers with it's settings?

0
 
LVL 1

Author Comment

by:Beamson
ID: 12566075
Sorry, a typo: FOR DNS, read DSN...
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12566080
I would suggest ditching the DSN and using connection code as follows:

if not IsObject(ojbConnection) then
      strDataPath = server.MapPath("YourDatabase.mdb")
      set objConnection=Server.CreateObject("ADODB.Connection")
      strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
                        + " Data Source= " & strDataPath & ";"_
                        + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
      objConnection.ConnectionTimeout = 15
      objConnection.CommandTimeout =  10
      objConnection.Mode = 3 'adModeReadWrite
      if objConnection.state = 0 then
            objConnection.Open strConnectString
      end if
end if


'commented out code goes here

objConnection.Close
set objConnection = Nothing
0
 
LVL 1

Author Comment

by:Beamson
ID: 12566421
Hi Fritz_the_Blank,

For now, I'm going to ditch DSN (at least until I know why it's causing problems)
In terms of alternatives though, I'm currently using:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
                           "DBQ=<location><database>.mdb"
objConn.Open

Is there a benefit to changing it to your version with Microsoft Jet OLEDB?
0
 
LVL 46

Assisted Solution

by:fritz_the_blank
fritz_the_blank earned 400 total points
ID: 12566472
Yes, the Jet OLEDB causes the least number of problems when working with Access. Indeed, if you have a relatively recent version of MDAC and you are using Access 2003 or above, you can get reasonably good results on sites with low to moderate traffic.

So in the code that I gave you, just change:

     strDataPath = server.MapPath("YourDatabase.mdb")

to

     strDataPath = "C:\YourPath\YourDatabase.mdb"

and you should be set.

Generally, I put this into an include file so I only have to manage this code in one place.

FtB
0
 
LVL 1

Author Comment

by:Beamson
ID: 12566686
Thanks for the quick response guys.

If either of you work out what might be wrong with DSN, I'd still like to know out of curiosity.
However, explicitly stating the connection sounds like the way to go.

Thanks.
0
 
LVL 9

Expert Comment

by:rg20
ID: 12566805
I would suggest staying with the dsnless connection, If you ever want to move to a different server, there is one less setup go handle
Also it is a lot faster as there are more layers that the DSN goes through to get to the databse.

Good Luck and thanks
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

862 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