I don't understand why, but perhaps your need to put a '\' before the &
before including it in the query. I know you would need to do that if
the category contained a single quote like "O'Hara". There are a few
other characters that need to be escaped in sting literals for MySQL but
I didn't think & was one of them.
John Bonnett
-----Original Message-----
From: Dave Long [mailto:dave@stripped]
Sent: Wednesday, 18 April 2007 11:05 AM
To: win32@stripped
Subject: Handling data with that pesky ampersand in a WHERE clause
Wanting to sort a collection of images by category in a ColdFusion page,
I set the ID_Field to the category field as shown below:
<CFQUERY name="GetRecord" dataSource="xxxxxx">
SELECT DISTINCT Category, Category AS ID_Field
FROM gallery
WHERE gallery.Active = 1
ORDER BY gallery.Category
</CFQUERY>
and passed the appropriate RecordID on to the next page:
<a
href="gallery_category.cfm?RecordID=#ID_Field#">#Category#</a>
On the gallery_category page, I filter the records with this query:
<CFQUERY name="GetRecord" dataSource="xxxxxx">
SELECT gallery.GalleryID AS ViewField1,
gallery.ImageFile AS ViewField2, gallery.Caption AS ViewField3,
gallery.Rank AS ViewField4, gallery.Active AS ViewField5,
gallery.Category AS ViewField6, gallery.Title AS ViewField7,
gallery.GalleryID AS ID_Field
FROM gallery
WHERE gallery.Category = '#URL.RecordID#' AND
gallery.Active = 1
ORDER BY gallery.Category, gallery.Rank
</CFQUERY>
All is fine UNTIL... until the data entered in a record's category field
includes an ampersand "&". At that point the query returns 0 records.
Example: one of the categories is named "T & C Bar". That is the name of
the bar, not "T and C Bar".
Any suggestions as to how can I get around this problem?
Dave Long
Web Design, Programming, & Hosting
http://www.northgoods.com
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.