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
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?
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
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
ASKER
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?
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?
ASKER
Sorry, a typo: FOR DNS, read DSN...
I would suggest ditching the DSN and using connection code as follows:
if not IsObject(ojbConnection) then
strDataPath = server.MapPath("YourDataba
set objConnection=Server.Creat
strConnectString = "Provider=Microsoft.Jet.OL
+ " Data Source= " & strDataPath & ";"_
+ " Mode=Share Deny None;User Id=admin;PASSWORD=;"
objConnection.ConnectionTi
objConnection.CommandTimeo
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
if not IsObject(ojbConnection) then
strDataPath = server.MapPath("YourDataba
set objConnection=Server.Creat
strConnectString = "Provider=Microsoft.Jet.OL
+ " Data Source= " & strDataPath & ";"_
+ " Mode=Share Deny None;User Id=admin;PASSWORD=;"
objConnection.ConnectionTi
objConnection.CommandTimeo
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
ASKER
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
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=<location><database>.
objConn.Open
Is there a benefit to changing it to your version with Microsoft Jet OLEDB?
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
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=<location><database>.
objConn.Open
Is there a benefit to changing it to your version with Microsoft Jet OLEDB?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
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
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!...