SQL doesn"t work in ASP but does in Access Query

Hello all!

I was wondering whether anyone could shed any light on why my SQL update
query works when run in an Access query, but doesn't work on the same
database when run through ASP and ODBC.

Here's the code:

UPDATE tbl_events SET tbl_events.description = 'Meeting - Teaching and
Learning' WHERE (((tbl_events.description) Like '*meeting*') AND
((tbl_events.event_date)>#2007/04/24# And
(tbl_events.event_date)<#2007/07/18#) AND ((Weekday(event_date))=4));

Here's the idea... all of our Wednesday meetings before 18/07 have been
changed to "Teaching and Learning" meetings, but one or two Wednesdays have
other things so I don't want to update them. I'm looking for events on a
Wednesday with the word "meeting" in the description. There are only two
fields in tbl_events.

If I create a new query in Access XP, paste in the SQL and run it, it works
perfectly. I have a file called update_records.asp that I use to update the
database without downloading the database from our host, so I know that the
rest of the code apart from the SQL should be OK. I paste the SQL into
update_records.asp and run it and nothing happens - no errors, just nothing.

In the end I downloaded the database, amended it and put it back - but why
doesn't it work?

Andrew

Re: SQL doesn"t work in ASP but does in Access Query

Andrew Virnuls wrote:
> Hello all!
>
> I was wondering whether anyone could shed any light on why my SQL
> update query works when run in an Access query, but doesn't work on
> the same database when run through ASP and ODBC.
>
> Here's the code:
>
> UPDATE tbl_events SET tbl_events.description = 'Meeting - Teaching and
> Learning' WHERE (((tbl_events.description) Like '*meeting*') AND
> ((tbl_events.event_date)>#2007/04/24# And
> (tbl_events.event_date)<#2007/07/18#) AND ((Weekday(event_date))=4));

A query that works in Query builder but not from ADO usually has at
least one of these two problems:

1. Using Jet wildcards (*,?) instead of ODBC wildcards (%,_). This
appears to be the problem with your query. Replace the * with %

2. Using reserved keywords for table or field names
(http://www.aspfaq.com/show.asp?id=2080)



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.