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.
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.
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.
You could try this. Take a connection object with events. Also take a boolean variable and call it something like BoolRolledBack.
Fog Creek Home