Problem with global.asa database and Session OnEnd

[Mike Sidler]

Agggghhhh....
I've read countless posting and still can't get an answer that works.

I'm trying to update a record when a session ends. Here's the code:

Sub Application_OnStart
Application("strConn") = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\inetpub\wwwroot\mydb.mdb"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("strConn")
End Sub

Sub Application_OnEnd
Conn.Close
End Sub

Sub Session_OnStart
Session.Timeout = 1
End Sub

Sub Session_OnEnd
Session("strSQL") = "UPDATE Accounts SET AccountLocked = False" & "
WHERE AccountID = " & Session("AccountID") & ";"
Conn.Execute Session("strSQL")
End Sub

Please don't tell me OnEnd is unreliable because I have confirmed that
it executes everytime. If you response either indicate exactly what
I'm doing wrong OR better yet provide a code sample that updates a DB
on Session OnEnd AND IT WORKS.

Thanks for help!
Mike

[Aaron Bertrand [MVP]]

> Please don't tell me OnEnd is unreliable because I have confirmed that

> it executes everytime.

In the limited cases you've tested, yes. There are definitely documented
scenarios where it *won't* fire, so please don't rely on this is as your
sole "reliable" method for updating your database. Set up a page somewhere,
once you get your kludge methodology working, and tell me where it is. I
bet within session.timeout I can get a row to be abandoned and say
AccountLocked = true until the next time someone logs in.

As it were.

There are two problems with your approach.

One is that you are expecting the server to remember, throughout the whole
application lifetime, to remember that during application_onStart you opened
a connection object. Guess what? That connection doesn't exist anymore.

The second problem is that you will probably expect to fix this by opening a
connection in application_onstart, storing a reference to that connection in
an application object, and referring to it constantly throughout the life of
the application. This isn't the best way to do it; in fact it's a surefire
way to kill scalability.

Instead, you should do this in session_onEnd:

set conn = CreateObject("ADODB.Connection")
conn.open
conn.execute "UPDATE ... ", , 129
conn.close: set conn = nothing

The key is to open and close a connection EVERY time you need it, and keep
it open for as short a time as possible. Especially when using Access.
This is what connection pooling is for, and trust me, IIS is better at it
than you are.

(And as a side note, .close isn't good enough. Any object created should
also be set = nothing.)

> If you response either indicate exactly what
> I'm doing wrong OR better yet provide a code sample that updates a DB
> on Session OnEnd AND IT WORKS.

My, aren't we a little demanding? Please remember that the people who share
advice and answers here are *VOLUNTEERS.* Be happy you're getting any
assistance at all with those very particular requirements.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Mike Sidler]

Aaron,

First thanks for your help.

I've tried all the things you suggested without success. Here's a
different version of the same code but include some debug stmts. With
the debug stmts I have confirmed the OnEnd fires but in the case below
the OnEnd stops CreateObject(Application("Status")="2").

Can you suggest what is required to make this work or provide a
different example that does works?

I've look at over 20 posts and tried many combinations - nothing seems
to work. Just frustrated with this. Thanks!

==========================================
Sub Application_OnStart
Application("strConn") = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\inetpub\wwwroot\mydb.mdb"
Application("Status") = "1"
End Sub

Sub Application_OnEnd
End Sub

Sub Session_OnStart
Session.Timeout = 1
End Sub

Sub Session_OnEnd
Application("Status") = "2"
Set Conn = Server.CreateObject("ADODB.Connection")
Application("Status") = "3"
Conn.Open Application("strConn")
Application("Status") = "4"
Session("strSQL") = "UPDATE Accounts SET AccountLocked = False WHERE
AccountID = " & Session("AccountID") & ";"
Application("Status") = "5"
Conn.Execute Session("strSQL")
Application("Status") = "6"
Conn.Close
Application("Status") = "7"
End Sub
==========================================



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Aaron Bertrand [MVP]]

Try using just CreateObject as opposed to Server.CreateObject.

Also, no need to create session("StrSQL"). Why not just strSQL = ?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Mike Sidler" <mike_sidler@hotmail.com> wrote in message
news:eBQ4bvv1DHA.1184@TK2MSFTNGP10.phx.gbl...

> Aaron,
>
> First thanks for your help.
>
> I've tried all the things you suggested without success. Here's a
> different version of the same code but include some debug stmts. With
> the debug stmts I have confirmed the OnEnd fires but in the case below
> the OnEnd stops CreateObject(Application("Status")="2").
>
> Can you suggest what is required to make this work or provide a
> different example that does works?
>
> I've look at over 20 posts and tried many combinations - nothing seems
> to work. Just frustrated with this. Thanks!
>
> ==========================================
> Sub Application_OnStart
> Application("strConn") = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=c:\inetpub\wwwroot\mydb.mdb"
> Application("Status") = "1"
> End Sub
>
> Sub Application_OnEnd
> End Sub
>
> Sub Session_OnStart
> Session.Timeout = 1
> End Sub
>
> Sub Session_OnEnd
> Application("Status") = "2"
> Set Conn = Server.CreateObject("ADODB.Connection")
> Application("Status") = "3"
> Conn.Open Application("strConn")
> Application("Status") = "4"
> Session("strSQL") = "UPDATE Accounts SET AccountLocked = False WHERE
> AccountID = " & Session("AccountID") & ";"
> Application("Status") = "5"
> Conn.Execute Session("strSQL")
> Application("Status") = "6"
> Conn.Close
> Application("Status") = "7"
> End Sub
> ==========================================
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Mike Sidler]

Doesn't work - tried:

Set Conn = CreateObject("ADODB.Connection")
and
Conn = CreateObject("ADODB.Connection")

Session OnEnd fails on this statement.



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!