Deleting multiple records using a recordset filter

Hi there,


I'm trying to delete multiple records from a access table in ASP... using
the recordsets Filter property and adAffectGroup command.

It doesn't work, yet according to the reference book I have, it should.
Since the reference does not of course say what to do if it doesn't!
work.... I need your help to understand it ;)

This is the code:

getAccessLogs.Open "SELECT * FROM Access_Log ORDER BY lg_DateTime DESC",
connect, adOpenDynamic, adLockOptimistic
getAccessLogs.Filter = "lg_DateTime < #20/08/04#"
getAccessLogs.Delete adAffectGroup
getAccessLogs.Filter = ""

The error I get is:

ADODB.Recordset (0x800A0C93)
Operation is not allowed in this context.
/xxx/xxxxx/sec_view_acc_logs.asp, line 25

Now.... it is written exactly as the reference says, without specifying I
have to worry about the connection method ssoooo.. what is the problem with
the 'context'?

Thanks for any help,

Jeremy.

Re: Deleting multiple records using a recordset filter

This is not the correct way to delete records. You're loading all the
records in your table into a recordset just so that you can use a filter
method to get the ones you want, only to then delete them. It's highly
inefficient. Try this instead:

sSQL = "DELETE FROM Access_Log WHERE lg_DateTime < #2004-08-20#
YourADOConnection.Execute sSQL,,129
yourADOConnection.Close
Set yourADOConnection = Nothing

Note that no recordsets are created, no gobs of data containing entire
tables are loaded into memory or anything like that.

Ray at home


"Infidel" wrote in message
news:eZAHegqzEHA.4004@tk2msftngp13.phx.gbl...
> Hi there,
>
>
> I'm trying to delete multiple records from a access table in ASP... using
> the recordsets Filter property and adAffectGroup command.
>
> It doesn't work, yet according to the reference book I have, it should.
> Since the reference does not of course say what to do if it doesn't!
> work.... I need your help to understand it ;)
>
> This is the code:
>
> getAccessLogs.Open "SELECT * FROM Access_Log ORDER BY lg_DateTime DESC",
> connect, adOpenDynamic, adLockOptimistic
> getAccessLogs.Filter = "lg_DateTime < #20/08/04#"
> getAccessLogs.Delete adAffectGroup
> getAccessLogs.Filter = ""
>
> The error I get is:
>
> ADODB.Recordset (0x800A0C93)
> Operation is not allowed in this context.
> /xxx/xxxxx/sec_view_acc_logs.asp, line 25
>
> Now.... it is written exactly as the reference says, without specifying I
> have to worry about the connection method ssoooo.. what is the problem
> with the 'context'?
>
> Thanks for any help,
>
> Jeremy.
>

Re: Deleting multiple records using a recordset filter

Hi Ray,


Thanks for responding so quickly, I must apologise though since I
neglected to mention that I knew the other ways to do it... but I wanted
to understand why this particular way wasn't working.

I will be updating code in several pages on the site in question to
utilise the exact method you provided.


Thanks,

Jeremy.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Deleting multiple records using a recordset filter

Infidel wrote:
> Thanks for responding so quickly, I must apologise though since I
> neglected to mention that I knew the other ways to do it... but I
> wanted to understand why this particular way wasn't working.
>
Please quote a little of the message to which you are replying so we can see
the context. Most of us are reading these messages using news readers, so we
don't have the benefit of scrolling up in the web page to see the context.

The method you are attempting, and I will repeat it here:

> getAccessLogs.Filter = "lg_DateTime < #20/08/04#"
> getAccessLogs.Delete adAffectGroup

is failing because it is not intended to work in this circumstance.
According to the ADO documentation * the adAffectGroup setting:

Affects only records that satisfy the current Filter property setting. You
must set the Filter property to a FilterGroupEnum value or an array of
Bookmarks to use this option.

The FilterGroupEnum values are:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstfilt ergroupenum.asp


HTH,
Bob Barrows
* which you can find at msdn.micriosoft.com/library - just drill down into
the "Data Access" node in the TOC or search for the term for which you are
looking
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"