How to I terminate ADO Query on ASP page unload

Here is your problem:
MyConn.CommandTimeout = 300

You have just told it you want to wait 5 minutes.

I need the CommandTimeout to let the query complete under normal conditions without timing out, but if a user terminates the program before the query completes I need to stop the execution and free up the connection.  any suggestions?

If this was a Windows app you could use the asynchonous property for commands, this allows you to cancel long running queries.  Since it is not, other than to optimize the query (and 5 minutes is excessive and tells me there is a problem) there is not a lot you can do about it.

Why don't you post the value of mySQL for a typical query that takes a long time?  Perhaps we can suggest a workaround.

>>If the query itself also takes a significant amount of time then asynchronous execution is your only option (other than simple sql tuning) and I'm not sure that's possible with ado and asp.<<
As I previously stated asynchronous execution is not possible with ASP scripting.

Ok I will rephrase:
then asynchronous execution is your only option (other than simple sql tuning) and I'm not sure that's possible with ONLY ado and asp.

If this fix is worth the effort and other options have been exausted, maybe the asynchronous database calls could be wrapped in a vb6 com object?  It's not exactly a simple fix, but I think it's possible.

dnorvellDate, I thought I should also ask about this:
"We have Response.Buffer = false to eliminate buffer overrun errors, in case that matters"

Buffering is a good thing - even with no IsClientConnected, it should let your script finish faster (which means you can release resources sooner).  I've never seen buffer overruns with this.  How many rows are you writing to the client exactly?  If we're talking hundreds or thousands then an app change to do some paging might be the better option than worrying about an abandoned request.  You can approach this from another angle and ask why is the user leaving?  Are they sick of waiting?  Is this something that needs to be generated on the fly (most reports can be generated or cached after hours)?

First of all, you guys are awesome!  Thanks so much for the help.
Since you asked for the queries I've pasted them in below.  I know nested queries are bad, and I can probably find a way to combine both or alll three loops by adding some logic to detect a change in Jobcode and change in user id in the inside loop.  I've had to do something like that in a screen that uses paging to let users page through data.  This however, is a report with selectable ranges of users and jobcodes.

I like the isClientConnected approach, assuming it works and could put inside loop 1 or 2 to cut down on overhead.  

Regarding buffering:  We don't normally turn it off, but a couple of reports, depending on selction range, can cause a buffer overflow.  I was looking for a way to set the buffer value but suspect that is an IIS setting not accessable from ASP.  If you know anything different I would love to be able to turn buffering back on.

Thanks, again.

'Loop1: Get list of all Job Descriptions to include
mySQL = "SELECT * from JobCodes WHERE JobCode >= 1800.001 AND JobCode <= 1800.024 ORDER BY JobCode ASC"
Set RS1 = MyConn.Execute(mySQL)
While NOT RS1.EOF
    '
    'Format and display Jobcode description info
    '
 
    'Loop2: Get list of all users in the Job description
    mySQL = "SELECT JCU.U_ID, JCU.Status, U.FirstName, U.LastName, U.DeptID from JobCodeUsers JCU INNER JOIN Users U ON JCU.U_ID = U.U_ID WHERE JCU.JobCode = 1800.002 ORDER BY JCU.U_ID"
    Set RS_U = MyConn.Execute(mySQL)
    While NOT RS_U.EOF
        '
        'Format and display user information
        '
 
        'Loop 3: get all assignment/completion records for this user & jobcode
        mySQL = "SELECT JCI.ItemNo, JCI.EnrollCode, JCI.Method, JCI.DueDate, JCI.PlusDays, JCI.FTMethod, JCI.FTDays, CI.Name AS CName, CI.CatID, CI.RefreshDays, E.EnrollText, M.MDescription, Cat.Name AS CatName FROM (((JobCodeItems JCI INNER JOIN CourseInfo CI on CI.[K-BookID] = JCI.ItemNo) INNER JOIN EnrollmentType E ON E.EnrollCode = JCI.EnrollCode) INNER JOIN Methods M ON M.MethodID = JCI.Method) INNER JOIN Categories Cat ON CI.CatID = Cat.CatID WHERE JCI.JobCode = 1800.002 ORDER BY CI.CatID ASC, JCI.ItemNo ASC"
        Set RS = MyConn.Execute(mySQL)
        While NOT RS.EOF
            '
            'Calculate status
            'Format & display results
            '
            RS.MoveNext
        wend
        RS_U.MoveNext
    wend
    RS1.MoveNext
wend

Select allOpen in new window

Well this says a lot ;).  Why the nested executes?  The sql strings don't appear to be related to the parent loops in anyway which means you could be executing these queries one time and then use the recordset over and over.  You'd need to do a Recordset.Open vs a Connection.Execute so you could open the recordset with a client side cursor (Connection.Execute opens a server side cursor) but if you did you'd be caching the data used for your internal loops and your page would probably complete in a fraction of the time.

Actually Queries 2 & 3 are based on RS1("JobCode").  When I captured the output previously the variable had been replaced by the value of the variable.  I reattached the code below.

I've been playing with your earlier suggestion of testing presence of the client and came up with this as a test.  The first "IF" doesn't ever execute as true but the second one works beautifully and terminates after 200 users are processed.

Client_Present = true
Record_Count = 0

while NOT RS.EOF AND Client_Present
        if not Response.IsClientConnected then
           'Close when client goes away:  
           'Not working - think the whole session has to terminate to trigger this
           Client_Present = false
        end if

       if Record_Count > 200 then
           Client_Present = false
        end if

        Record_Count = Record_Count + 1
        '
        ' Process, processs
        '
       RS.MoveNext
wend
 RS.Close
 set RS = nothing
 Response.End

Since the second "IF" above works I'm currently looking for some way to actaully get the status of the page or connection or someway to set a variable, session variable, session parameter, anything that I can test at runtime.

'Loop1: Get list of all Job Descriptions to include
mySQL = "SELECT * from JobCodes WHERE JobCode >= 1800.001 AND JobCode <= 1800.024 ORDER BY JobCode ASC"
Set RS1 = MyConn.Execute(mySQL)
While NOT RS1.EOF
    '
    'Format and display Jobcode description info
    '
 
    'Loop2: Get list of all users in the Job description
    mySQL = "SELECT JCU.U_ID, JCU.Status, U.FirstName, U.LastName, U.DeptID from JobCodeUsers JCU INNER JOIN Users U ON JCU.U_ID = U.U_ID WHERE JCU.JobCode = " & RS1("JobCode") & " ORDER BY JCU.U_ID"
    Set RS_U = MyConn.Execute(mySQL)
    While NOT RS_U.EOF
        '
        'Format and display user information
        '
 
        'Loop 3: get all assignment/completion records for this user & jobcode
        mySQL = "SELECT JCI.ItemNo, JCI.EnrollCode, JCI.Method, JCI.DueDate, JCI.PlusDays, JCI.FTMethod, JCI.FTDays, CI.Name AS CName, CI.CatID, CI.RefreshDays, E.EnrollText, M.MDescription, Cat.Name AS CatName FROM (((JobCodeItems JCI INNER JOIN CourseInfo CI on CI.[K-BookID] = JCI.ItemNo) INNER JOIN EnrollmentType E ON E.EnrollCode = JCI.EnrollCode) INNER JOIN Methods M ON M.MethodID = JCI.Method) INNER JOIN Categories Cat ON CI.CatID = Cat.CatID WHERE JCI.JobCode = " & RS1("JobCode") & " ORDER BY CI.CatID ASC, JCI.ItemNo ASC"
        Set RS = MyConn.Execute(mySQL)
        While NOT RS.EOF
            '
            'Calculate status
            'Format & display results
            '
            RS.MoveNext
        wend
        RS_U.MoveNext
    wend
    RS1.MoveNext
wend

Select allOpen in new window

Ok that makes me feel better than.  IsClientConnected should fix things as far as abandoned connections.

Yep, that certainly explains why it is taking so long.  You really need to re-think your approach as to how you are returning your results.  The biggest improvement would be to wrap all those queries into a single Stored Procedure.  I would lose the Command Timeout setting of 5 minutes.  No one in their right mind are going to wait that long for an ASP page and it certainly explains the users impatience.  Verify that all tables are adequately indexed.  And finally and as a last resort, if after making all these changes you are still hitting the network limit and you are finding that users are cancelling out, consider putting a limit on the number of rows returned.  That has to be better than a user cancelling out of frustration.

The isClientConnected  approach worked, but only after I removed my response.buffer = false statement and added a response.flush inside one of the loops (at the same place I test IsClientConnected).

The final solution is below.  It's a big report and it takes awhile.  It won't be run frequently but when needed it must run and the user must be able to exit without the process continuing to run.

I will also look into the stored procedure comment.

Thanks for all your help.

Client_Present = true
'Loop1: Get list of all Job Descriptions to include
mySQL = "SELECT * from JobCodes WHERE JobCode >= 1800.001 AND JobCode <= 1800.024 ORDER BY JobCode ASC"
Set RS1 = MyConn.Execute(mySQL)
While NOT RS1.EOF and Client_Present
    '
    'Format and display Jobcode description info
    '
    'Loop2: Get list of all users in the Job description
    mySQL = "SELECT JCU.U_ID, JCU.Status, U.FirstName, U.LastName, U.DeptID from JobCodeUsers JCU INNER JOIN Users U ON JCU.U_ID = U.U_ID WHERE JCU.JobCode = " & RS1("JobCode") & " ORDER BY JCU.U_ID"
    Set RS_U = MyConn.Execute(mySQL)
    While NOT RS_U.EOF AND Client_Present
        'Check for disconnected or closed program
        if not Response.IsClientConnected then
           Client_Present = false   'Set flag to abort if connection is lost
           Response.Write("<br>Canceled by user.<br>")
        end if
        
        'Avoid Buffer Overruns
        response.Flush
 
        '
        'Format and display user information
        '
 
        'Loop 3: get all assignment/completion records for this user & jobcode
        mySQL = "SELECT JCI.ItemNo, JCI.EnrollCode, JCI.Method, JCI.DueDate, JCI.PlusDays, JCI.FTMethod, JCI.FTDays, CI.Name AS CName, CI.CatID, CI.RefreshDays, E.EnrollText, M.MDescription, Cat.Name AS CatName FROM (((JobCodeItems JCI INNER JOIN CourseInfo CI on CI.[K-BookID] = JCI.ItemNo) INNER JOIN EnrollmentType E ON E.EnrollCode = JCI.EnrollCode) INNER JOIN Methods M ON M.MethodID = JCI.Method) INNER JOIN Categories Cat ON CI.CatID = Cat.CatID WHERE JCI.JobCode = " & RS1("JobCode") & " ORDER BY CI.CatID ASC, JCI.ItemNo ASC"
        Set RS = MyConn.Execute(mySQL)
        While NOT RS.EOF AND Client_Present
            '
            'Calculate status
            'Format & display results
            '
            RS.MoveNext
        wend
        RS_U.MoveNext
    wend
    RS1.MoveNext
wend
 
RS.Close
RS1.Close
RS_U.Close
set RS = nothing
set RS1 = nothing
set RS_U = nothing

Select allOpen in new window

I wanted to stay on target of your original "cancel" question and that is probably the simpler fix.  However, I do agree with acperkins - I'd bet you could do that whole statement in a single statement or proc so if you have time it might be worth that effort.  You could always start a new question if you need help with the sql.  Your concerns with "buffer overruns" still concern me as well.  I'd have to guess you must be writting a very large amout of html to the client so some "paging" might also be in order.  Either way, good luck.