Fog Creek Software
Discussion Board




Cancelling an Asynchronous Query in ADO

I'm writting a search facility that will be used against a large number of clinical records.  The main screen has a Stop Search button that is activated as soon as the user hits the Search button.  The code behind the Search button calls a method on a class that initiates an asynchronous search on the database (using a stored procedure). However, I have a problem because the call to the Cancel does not return sooner. This happens when the query being returns a large record set.  It seems that SQL server tries to clean up before returning.  I would have expected to get control back as soon as I made the call to the Cancel method. 

Could anybody on this forum know of a work around that can be used to fix this problem?  I'm running VB6 SP5, with SQL Server 2000 on Win XP.

Ling
Friday, September 05, 2003

DoEvents?

__
Friday, September 05, 2003

DoEvents may not solve the problem because the user will be interested in cancelling the intial search and using a different criteria.  They could also want to close the search screen entirely.  The form won't unload untill  ADO is done and unloaded.

On a different note, 'inserting' 'DoEvents on a complex system without proper design is inviting trouble.  Been there n gotten my fingers burnt.

Ling
Friday, September 05, 2003

Instantiate a search class that is independent of the form.  If the user cancels the search, unload the class and reset the form  If they want to do a new search, instantiate a new instance.

Tidy up the connection, etc behind the scenes.  This shouldn't affect clean-up, no?

__
Friday, September 05, 2003

You could try this. Take a connection object with events. Also take a boolean variable and call it something like BoolRolledBack.

When you fire your command object or open your recordset in the asynchronous execution mode, set BoolRolledBack to False and call the BeginTransaction method on the connection object. Also provide for DoEvents when you execute command object or open your recordset.

In the click of the Stop button, call the RollbackTrans method on the connection object. In the RollbackTransComplete event of the connection object, set BoolRolledBack to True. Immediately after firing your command object or opening your recordset, keep querying the State property of the recordset object or command object and in the loop, also keep checking the BoolRolledBack flag. If the BoolRolledBack flag is True, close up your recordset object and set it to nothing.

Sathyaish Chakravarthy
Saturday, September 06, 2003

Ling,

If you are interested, http://peach.ease.lsoft.com/scripts/wa.exe?A0=visbas-l#SUB would be a good list on which to get assistance with this problem.

Seeya

Matthew
Sunday, September 07, 2003

*  Recent Topics

*  Fog Creek Home