I am querying a Microsoft Access database on an Active Server Page using FrontPage 2000 as my authoring software. Using the Database Results component, I have set everything up and all is working well, with one exception. Using the component wizard, I created an input form to allow users to query the database.
First, here's the SQL statement that I'm using.
SELECT * FROM Available WHERE ((BotanicName LIKE '%::BotanicName::%' OR CommonName LIKE '%::CommonName::%') AND Size LIKE '%::Size::%') ORDER BY BotanicName ASC,Size ASC,CommonName ASC
I am new to SQL and it appears that this statement uses the single-quote character as the delimiter. Therein lies my problem, because my data contains this character, as well as double-quotes. My application queries data from a horticultural database. The industry norm for botanic name descriptions of plant material uses single quotes and size description uses both single and double-quotes.
Here are some examples:
Botanic: ACER PLATANOIDES 'CRIMSON KING'
Size : #5 or 12' or 20-24"
My question is this. Can I construct an SQL statement, that will retrieve this information? Right now I get this error, when using a single quote in the search.
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '((BotanicName LIKE '%'CRIMSON KING'%' OR CommonName LIKE '%~%') AND Size LIKE '%.%')'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
As I mentioned, other that this specific problem, everything is working exactly as desired.
I did pose this same question in another part of EE and the suggestion was made to use a function that replaces the single-quote with two single-quote characters. I wasn't able to make this work. I first tried to add the function code inside of the ASP code generated by the Database Results component and FrontPage won't allow this. I then placed the function outside of the component code and it wasn't recognized when the component code executed. However, if I manually enter two single-quotes on my input form, the search executes correctly.
So, I need to be able to delimit a single-quote, which is entered by the user on an input form created by the FrontPage 2000 Database Results wizard.
Thanks for your help.