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

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?

LVL 1
BeamsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aprestoCommented:
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
BeamsonAuthor Commented:
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
rg20Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

BeamsonAuthor Commented:
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
rg20Commented:
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
BeamsonAuthor Commented:
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
BeamsonAuthor Commented:
Sorry, a typo: FOR DNS, read DSN...
0
fritz_the_blankCommented:
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
BeamsonAuthor Commented:
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
fritz_the_blankCommented:
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
BeamsonAuthor Commented:
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
rg20Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.