Asp to Access connection problems.

[Gavuk]

Hiya all,

I have inherited this problem from someone else. We have asp pages that call an access database. Every 2 -3 days we have to close the web server down (asp and access db on same server - i know!).

I am assumming that the problem lies with connections not being closed to the server correctly, but i am not sure how to verify this!!! the connection used are as follows:

Sub Initialize_Page()
Call ToDo()
End Sub

Sub ToDo()
If Session("ID") = "" Then Exit Sub
'### INITIALISE VARIABLES ###
Requisitions = 0
Invoices = 0
POs = 0
ReqAuthorisations = 0
InvAuthorisations = 0
InvPrint = 0
Assets = 0

'### FETCH COUNT VALUES OF OUTSTANDING TASKS FOR THE USER ###
' this is an DSN less connection
Call OpenDB()
With rsDBdata
strSQL = "SELECT Count" 'etc etc
.Open strSQL
If .RecordCount > 0 Then
Requisitions = .Fields("Total").value
Else
Requisitions = 0
End If
.Close

strSQL = "SELECT Count(Tracer.SERIAL_NO) AS Total FROM" 'etc etc
.Open strSQL
If .RecordCount > 0 Then
WaitingInvoices = .Fields("Total").value
Else
WaitingInvoices = 0
End If
.Close


'Under here there are another 5 select statements

End With
Call DataDB()
End Sub

%>


When eventually the system grinds to a halt it fails in the following routine: (at the .Open command).

Private Sub OpenDB()
Set conDB = Server.CreateObject("ADODB.Connection")
With conDB
.ConnectionString = GetConnectionString("SOme connection string")
.ConnectionTimeOut = 30 'seconds
.Open
End With
Set cmdALL = Server.CreateObject("ADODB.Command")
With cmdALL
.ActiveConnection = conDB
.CommandTimeOut = 300 'seconds
End With
Set rsDB = Server.CreateObject("ADODB.Recordset")
With rsDB
.ActiveConnection = conDB
.CursorLocation = 3
.CursorType = 3
.LockType = 1
End With
Set rsDBTemp = Server.CreateObject("ADODB.Recordset")
With rsDBTemp
.ActiveConnection = conDB
.CursorLocation = 3
.CursorType = 3
.LockType = 1
End With
End Sub

Any thoughts would be much appreciated.

Cheers

Gav

 

[Sheco]

The last line in your ToDo() Sub is: Call DataDB()

Does DataDB() close the ADO connection?

 

[Gavuk]

Yes unfortunately it does with the following:

Private Sub CloseDB()
On Error Resume Next
rsDB.Close
Set rsDB = Nothing
Set rsDBTemp = Nothing
Set cmdALL = Nothing
conDB.Close
Set conDB = Nothing
Err.Clear
On Error GoTo 0
End Sub

Checked how many users were in the actual database and that returns that 0 users are in the database (funky little app!) thats why i believe that it is something to do with the connections not being closed correctly somewhere in the asp code. This i believe is leaving connections open to the database (but not the database itself being open) does this sound possible?

Any thoughts would be much appreciated.

Cheers

Gav

 

[Chopstik]

As Sheco asked previously, what is the code for DataDB()? Presumably (as you just posted), it calls the CloseDB() function, but does it do something else as well?

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[Sheco]

So are you saying that DataDB() calls the sub CloseDB() ?

Also I notice that inside CloseDB that rdDBTemp is not closed before it is set to nothing, although THAT is probably not what is causing your problem.

If you have session state enabled you could try closing everything inside the session end event. The default for inactivity is only 20 minutes so that would probably do the trick.

Are you sure this is the cause of your problem?

 

[Sheco]

Sorry, I meant the Session_OnEnd event in the global.asa

 

[Gavuk]

No sorry for the confusion CloseDB() is actually directly called from call DataDB() (was trying to change the names!).

Other people have suggested server permissions for the folder where the database lives (any thoughts) but that would not explain why it stops every 2-3 days would it? If that is the case surely it would drop a connection anyway and leave it free for other calls to it?

 

[Sheco]

Folder permissions wouldn't really explain why it dies for everyone... I assume when it stops working that you can't even get it to run from a browser located on the actual server machine.

 

[Gavuk]

No it just returns the same error. Once we reboot he machine it works fine (until about 2-3 days later that is!).

 

[Sheco]

"When eventually the system grinds to a halt it fails in the following routine: (at the .Open command)."

What error exactly do you see?

 

[Gavuk]

Sheco,

In the global.asa is the following (dont like the look of it lol!! as i said i inherited this!) anything there that looks bad to you??

<script language="vbscript" runat="server">

Sub Application_OnStart
Application("Visitors")=0
End Sub

Sub Session_OnStart
Application.Lock
Application("Visitors")=Application("Visitors") + 1
Application.UnLock
End Sub

Sub Session_OnEnd
Application.Lock
Application("Visitors")=Application("Visitors") - 1
Application.UnLock
End Sub

</script>

 

[Gavuk]

The exact error message is:

provider error '80004004'

Unspecified error
/nameoffolder/_classes/_dependancies/something.asp, line 98

Thats the line that is the .open command.

 

[Sheco]

Oh well I notice that your session start event doesn't have copies of the ADO objects in a session variable so there is no use closing anything in the OnEnd event.

 

[Sheco]

I don't know what else to say, it looks like you've got your recordset and connection objects closing as they should.

To be any more sure about your connections you'd almost have to put the connection code into an ActiveX DLL and put it into a COM+ package.

 

[Gavuk]

Me neither been banging my head against a brick wall here but thank you for your comments anyway.

 

[steven290]

maybe i'm wrong but i don't see "rsDBdata" being set anywhere
all i see is

with rsDBdata

dont' you have to do something like this

set rsDBdata = OpenDB()
with rsDBdata

also you opendb subroutine is a mess

 

[Sheco]

Eagle Eye!

You are right, OpenDB() instatiates rsDB and rsDBTemp but not rsDBData.

 

[Gavuk]

Cheers for all this by the way, I hear what you are saying about the code but the thing is it works for 2-3 days and then stops (thats the problem) - the code has been cut and paste to protect the innocent thats why the names dont match up, was doing this on the fly as i was juggling other things but for rsDBdata read rsDB in the connect (i know its slightly confusing just read it back myself).

 

[steven290]

starting and shut down the server shouldn't be an issue, why do you shut down every couple of days

 

[Sheco]

haha yeah that is one way to fix it!

Just use the task scheduler to reboot it every night at 3AM!

/snicker