adCommand.Cancel doesn't seem to work with SELECT INTO query 
Author Message
 adCommand.Cancel doesn't seem to work with SELECT INTO query

   adoConnection.Open "Provider=Microsoft.JET.OLEDB.4.0;Data
Source=c:\projects\mis_v2.0\database\mis.mdb"

    Set adoCommand.ActiveConnection = adoConnection
    adoCommand.CommandText = "SELECT tblAudit_13.* INTO tmpAudit FROM
tblAudit_13 WHERE CreatedDate > #25/09/2001# AND TradeID=2 AND Barcode
='2036494389463'"
    adoCommand.CommandType = adCmdText

    adoCommand.Execute , , adAsyncExecute

When I later come to execute the adoCommand.Cancel method, all it appears to
do is disable the async mode and lock the current app while the execution of
the above mentioned query still executes.

Is this a problem with ADO or is it actually possible to really cancel a
query that does not return a recordset?

All help is greatly appreciated.

Thanks in advance,

Ian Ashworth



Fri, 16 Apr 2004 22:53:05 GMT  
 adCommand.Cancel doesn't seem to work with SELECT INTO query
Hi Ian,

You can resort to Transaction methods of Connection object along with
Cancel method of Command object to terminate execution of an asynchronous
method call, no matter if the command returns a recordset or not. I hope
the following snippet can help you.

   'Begin a transaction, then execute a command asynchronously
   adoConnection.BeginTrans
   adoCommand.Execute , , adAsyncExecute
   ?-..
   ' If the command has NOT completed, cancel the execute and roll back the
transaction; otherwise, commit the transaction

   If CBool(Cnxn.State And adStateExecuting) Then
      adoConnection.Cancel
      adoConnection.RollbackTrans
      MsgBox "Update canceled."
   Else
      adoConnection.CommitTrans
      MsgBox "Update complete."
   End If

For more information on ADO transaction, please read the following article:
BeginTrans, CommitTrans, and RollbackTrans Methods
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthbegintrans.asp

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copy Right, 2001 Microsoft Corporation. All rights reserved.

Regards,
(space)
Kally Wang
Microsoft Support Engineer


Subject: adCommand.Cancel doesn't seem to work with SELECT INTO query
Date: Mon, 29 Oct 2001 14:53:05 -0000
Newsgroups: microsoft.public.vb.database.ado

   adoConnection.Open "Provider=Microsoft.JET.OLEDB.4.0;Data
Source=c:\projects\mis_v2.0\database\mis.mdb"

    Set adoCommand.ActiveConnection = adoConnection
    adoCommand.CommandText = "SELECT tblAudit_13.* INTO tmpAudit FROM
tblAudit_13 WHERE CreatedDate > #25/09/2001# AND TradeID=2 AND Barcode
='2036494389463'"
    adoCommand.CommandType = adCmdText

    adoCommand.Execute , , adAsyncExecute

When I later come to execute the adoCommand.Cancel method, all it appears
to
do is disable the async mode and lock the current app while the execution
of
the above mentioned query still executes.

Is this a problem with ADO or is it actually possible to really cancel a
query that does not return a recordset?

All help is greatly appreciated.

Thanks in advance,

Ian Ashworth



Mon, 19 Apr 2004 16:58:10 GMT  
 adCommand.Cancel doesn't seem to work with SELECT INTO query
Kally,

Thank you for your reply, the cancel event is now working and not
automatically commiting to the db.  However, when the .cancel event it
initiated it seems to take forever to cancel and rollback.

Is it possible to do a .Cancel Asynchronously as users don't want to wait
while something is cancelled.

Thanks in advance

Ian Ashworth


Quote:
> Hi Ian,

> You can resort to Transaction methods of Connection object along with
> Cancel method of Command object to terminate execution of an asynchronous
> method call, no matter if the command returns a recordset or not. I hope
> the following snippet can help you.

>    'Begin a transaction, then execute a command asynchronously
>    adoConnection.BeginTrans
>    adoCommand.Execute , , adAsyncExecute
>    ?-..
>    ' If the command has NOT completed, cancel the execute and roll back
the
> transaction; otherwise, commit the transaction

>    If CBool(Cnxn.State And adStateExecuting) Then
>       adoConnection.Cancel
>       adoConnection.RollbackTrans
>       MsgBox "Update canceled."
>    Else
>       adoConnection.CommitTrans
>       MsgBox "Update complete."
>    End If

> For more information on ADO transaction, please read the following
article:
> BeginTrans, CommitTrans, and RollbackTrans Methods
> http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthbegintrans.asp

> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copy Right, 2001 Microsoft Corporation. All rights reserved.

> Regards,
> (space)
> Kally Wang
> Microsoft Support Engineer


> Subject: adCommand.Cancel doesn't seem to work with SELECT INTO query
> Date: Mon, 29 Oct 2001 14:53:05 -0000
> Newsgroups: microsoft.public.vb.database.ado

>    adoConnection.Open "Provider=Microsoft.JET.OLEDB.4.0;Data
> Source=c:\projects\mis_v2.0\database\mis.mdb"

>     Set adoCommand.ActiveConnection = adoConnection
>     adoCommand.CommandText = "SELECT tblAudit_13.* INTO tmpAudit FROM
> tblAudit_13 WHERE CreatedDate > #25/09/2001# AND TradeID=2 AND Barcode
> ='2036494389463'"
>     adoCommand.CommandType = adCmdText

>     adoCommand.Execute , , adAsyncExecute

> When I later come to execute the adoCommand.Cancel method, all it appears
> to
> do is disable the async mode and lock the current app while the execution
> of
> the above mentioned query still executes.

> Is this a problem with ADO or is it actually possible to really cancel a
> query that does not return a recordset?

> All help is greatly appreciated.

> Thanks in advance,

> Ian Ashworth



Sun, 18 Apr 2004 20:07:48 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. STMAdmin.dll to query NT Log doesn't seem to work properly

2. Cancel doesn't work on async queries

3. Cancel doesn't work on async queries

4. select query doesn't always work

5. HitTest doesn't seem to work as expected

6. FormField.EntryMacro doesn't seem to work

7. AddHandler doesn't seem to work

8. AppActivate doesn't seem to work with SendKeys

9. AddHandler doesn't seem to work

10. Adding CookieCollection to WebRequest doesn't seem to work

11. close method on RegistryKey.OpenRemoteBaseKey doesn't seem to work

12. End of File doesn't seem to work

 

 
Powered by phpBB® Forum Software