ADO

[Raptors Fan]

Hi,

Is there a way to cancel a query? What I mean is that when I open a recordset, I would like to have the option to cancel the action if it takes too long.

I see that there is a cancel method for a recordset but I can't seem to execute while the recorset is being opened.

I know that you can use command timeout, but that doesn't give you the option to wait for as long as it takes or stop the query whenever you please. It only waits for a determined time.

Many thanks,

RF

[Cakkie]

You can open a recordset asyncronous, which means control is given to the program immedaitly, enabling you to cancel it. Also, this way your app doesn't freeze, allowing you to do some other stuff (if needed).
I'm not absolutely sure, but I think you can cancel execution using a method of the connection object (Cancel, or CancelExecution or something like that. To open a recordset asyncronous, you need to specify the option parameter of the OpenMethod, giveing along adAsyncExecute.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Cimperiali]

...Declare your Adodb WithEvents to take advantage of a lot of Event that may help you in this job...
(Side effect: you cannot use array of WithEvents objects)

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[Raptors Fan]

Thanks, I didn't know about "WithEvents" and "adAsyncExecute". Now, this is what I changed:

private withevents rstDeals as ADODB.Recordset

private Sub OpenRS()
set rstDeals = new ADODB.Recordset

With rstDeals
.ActiveConnection = cnnOPTEX
.Source = strSQL
.LockType = adLockReadOnly
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open Options:=adAsyncExecute
End With
End Sub

private Sub rstDeals_FetchComplete(byval pError as ADODB.error, adStatus as ADODB.EventStatusEnum, byval pRecordset as ADODB.Recordset)
' testing
MsgBox "Fetch Complete"
End Sub

private Sub rstDeals_FetchProgress(byval Progress as Long, byval MaxProgress as Long, adStatus as ADODB.EventStatusEnum, byval pRecordset as ADODB.Recordset)
' testing
MsgBox "Fetch in progress"
End Sub




The above are incomplete samples taken from my code.

The problem I have now is that the events don't trigger. I tried a query that takes about 10 secs, waited for a min, and no msgboxes came up... meaning that the events were no triggered?

What am I missing? or what needs to be changed for the events to trigger?

Many thanks,

RF

[Cimperiali]

'From msdn:
'The FetchProgress and FetchComplete events only work properly in MDAC 2.5 or later.
'You can download the latest version of the Microsoft Data Access Components from
'the following Microsoft Web site:

'Need text1, DataGrid, Commandbutton
'do not use breakpoint,do not use msgBox



option Explicit

Const strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Programmi\Microsoft Visual Studio\VB98\Biblio.mdb;Persist Security Info=false"
Const strDefaultSQL = "select * from authors"

Dim cn as ADODB.Connection
Dim withevents rs as ADODB.Recordset

private Sub Form_Load()
Command1.Caption = "Go"
Text1.Text = strDefaultSQL

set cn = new ADODB.Connection
cn.Open strConn
End Sub

private Sub Command1_Click()
Dim strSQL as string
strSQL = Text1.Text

set rs = new ADODB.Recordset
With rs
.CursorLocation = adUseClient

.Properties("Initial Fetch Size") = 2
.Properties("Background Fetch Size") = 4

Debug.print "Start"
Debug.print "Initial Fetch Size: " & _
.Properties("Initial Fetch Size")
Debug.print "Background Fetch Size" & _
.Properties("Background Fetch Size")

.Open strSQL, cn, , , adAsyncFetch
End With
End Sub

private Sub rs_FetchProgress(byval Progress as Long, _
byval MaxProgress as Long, _
adStatus as ADODB.EventStatusEnum, _
byval pRecordset as ADODB.Recordset)

Debug.print "Fetch: " & Progress & _
" Max: " & MaxProgress

End Sub

private Sub rs_FetchComplete(byval pError as ADODB.error, _
adStatus as ADODB.EventStatusEnum, _
byval pRecordset as ADODB.Recordset)

If adStatus <> adStatusOK then
Debug.print "Failed"
Debug.print "error: " & pError.Number & " - " & pError.Description
else
set DataGrid1.DataSource = pRecordset
Debug.print "Done"
End If

End Sub






Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.