|
| |
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
 |
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 12:35:56
Hi Gege,
How are you searching for O' brien?
Are you typing the name itself manually or are you searching through a text field. Are you using a DRW?
Hisham
|
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 12:42:44
Hi,
If you are using a DRW using Frontpage
01- Insert
02- Database Results
03- Select Database > Next
04- Select the Table > Next
05- More options
06- Click Criteria > Add
07- Select the field name that has the O' Brien inside, might be LastName
08- Click OK then OK Then Next
09- Click Next
10- Click Display all records
11- Keep Add Searched form clicked and click Finish
12- Save your page
13- Browse it and type O' Brien in the search field
The SQL For that would be: (in my example I selected ContactName from customers)
SELECT * FROM Customers WHERE (ContactName = ' ::ContactName::' )
Best regards
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 12:54:40
Hi again,
If you are manually typing the SQL for some reason, you have to put it this way:
SELECT CustomerID, ContactName
FROM Customers
WHERE ContactName=' o' ' brien' ;
Note that it has 4 apostrophies 1 before the O the other two are after the O and the last is after the n. Note that after the O there are two apostrophies, not one quotaion mark.
Best regards
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 12:56:22
Hi,
You can also create a query in Access and selected instead of the Table in the DRW. Then search it from you form.
Regards
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 12:59:51
Hi again,
In fact I am not sure how you are searching. Try putting another apostrophe after the first one.
May be you are searching a text field that required that. Anyway type the name as O' brien.
The previous steps I gave you before the manual one can search and find for O' Brien without typing an extra apostrophe.
Hisham
| |
|
|
|
Gege
Posts: 13
Joined: 1/11/2003
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 13:13:25
Thanks for replying Hisham.
That works fine. 
What if I want to use " LIKE" . It seens like for the script to be efficient, the keyword has to excatly match the record.
example :
SELECT lastname, firstname, position From office WHERE lastname LIKE ' ::keyword::' OR firstname LIKE ' ::keyword::' ORDER by lastname DESC
This query works fine when you type (in the search field) a keyword with or without apostrophe. BUT, the LIKE function seems to work as a EQUAL function. Which means that if in the firstname field you have a first name such as " tommy lee" and the keyword is " tommy" , the query returns 0 records!
On the other hand, if the query looks like this:
SELECT lastname, firstname, position From office WHERE lastname LIKE ' %::keyword::%' OR firstname LIKE ' %::keyword::%' ORDER by lastname DESC
It works fine: when you type " Tommy" as a keyword (or lee) , it returns " tommy lee" as a result. HOWEVER[:j][:j], don' t try any search with a word containing an apostrophe cause it will result in " syntax error ..."  
Hope you understood what i mean (sometimes I have a hard time understanding myself[:j]
I would LOOOOOOOOOVE  to have the best of the two which is being able to deal with the apostrophe and still be able to use the LIKE function. [;)
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 13:21:50
Hi,
This searches for O' brien with no problem:
SELECT * FROM Customers WHERE (ContactName LIKE ' %::ContactName::%' )
I tried it with a name that has an apostrophe.
This searches for the starting of the name with no problem, like your example Tommy Lee.
SELECT * FROM Customers WHERE (ContactName LIKE ' ::ContactName::%' )
Regards
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 13:58:29
Hi,
Since you are using more than one table you have to use DISTINCTROW instead of DISTINCT:
SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.Country, Orders.EmployeeID
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Since I am using two tables I like to use the tablename.fieldname in order to track what I am selecting.
Best regards
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 14:41:35
Hi Gege,
I have to agree with spooky. I gave you lot of options on the top of the post and all of them worked on my DRW (Database Results Wizard) which you can create by selecting Insert/Database Results.
From your queries I suppose that you are using the DRW.
I tried the ' O' ' Brien' , Like .....% ane like %.... and like % ....% all worked just fine, I don' t know why they are not working for you. Besides as spooky noted, the DRW handles the apostrophe with no problem.
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 15:06:28
Ok,
Are you posting the form to the DRW page?
Create the form yourself, right click the form, select properties, Options, Action. Then in the box next to action type the path of the ASP page that hosts the DRW, let is be results.asp and try.
ELSE
You can email me the database with a few dummy records, tell me what you want and I will do it for you.
Best regards
Hisham
| |
|
|
|
Gege
Posts: 13
Joined: 1/11/2003
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 15:31:59
Can' t figure out how to send database to you
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/11/2003 15:59:35
Hi Gege,
You can send it to this email.
hhammash@hotmail.com
This is not my forum email, but you can send it here. Send me an email through the forum when you email it.
Thanks
Hisham
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/12/2003 15:15:45
This is a well known and well documented problem. Have you seen this yet?
| |
|
|
|
Gege
Posts: 13
Joined: 1/11/2003
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/12/2003 19:05:07
OK! but how do you do that in a DRW like query?
| |
|
|
|
Gege
Posts: 13
Joined: 1/11/2003
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/13/2003 10:49:36
Thanks a lot. I' ll give it a try and come back to you.
Only thing is that i remember trying smthing like that before. Problem was the query was " cut" by the quotes (" ). The query made by FP DRW is between quotes (" ) and looks like s-sql = " select * ...... where .... order by ..."
Anyways. I' ll try it and give you feedback.
Thanks so much for your help
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/13/2003 11:39:45
Yup, understand about the quotes. The FP DRW uses the SQL string in quotes so it can pass the string to the DB engine. We are passing a similar string just we' re building it dynamically using Request.Form and ' massaging' it using Replace.
Make sense?
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/14/2003 8:23:41
quote:
the query simply won,t accept quotes . the query is cut right after a quote. Then I get obviously a syntax error!
Can you clarify for me? Are we talking about double quotes (" ) or single quotes (' )? I thought we were talking about single but I could have been mistaken....
As to the single quote issue, it does accept them in a query. Here is an exact copy of the line I sucessfully used in a test I just did...
fp_sQry=" SELECT * FROM Table1 WHERE Text LIKE ' %" & Replace(Request.Form(" Text" ), " ' " , " ' ' " ) & " %' "
In fact, I tried it by just submitting an apostrophe and it still worked. Again, are we talking single (apostrophe) or double quotes. If we' re talking single, there must be something else wrong. Can you post your code????
| |
|
|
|
Gege
Posts: 13
Joined: 1/11/2003
Status: offline
|
RE: APOSTROPHE CAUSING SYNTAX ERROR IN SEARCH QUERY - 1/18/2003 14:24:32
Here is my code:
fp_sQry=" SELECT DISTINCT titre, artiste_ex, type_fichier, annee_album, genre_ex FROM extraits WHERE (((titre LIKE ' ::keyword::%' ) OR (artiste_ex LIKE ' ::keyword::%' )) AND (genre_ex LIKE ' ::keyword1::' )) OR (((titre LIKE ' ::keyword::%' ) OR (artiste_ex LIKE ' ::keyword::%' )) AND (' ::keyword1::' = ' all' ))) AND (type_fichier = ' video.gif' ) ORDER BY annee_album DESC, titre ASC"
The above works fine: no problem with searches involving single quotes
But, the problem is that il you are searching for let' s say " George Bush" and you type the keyword " Bush" , it won' t find it (it will find it if the keyword is " George" .
If I query ... LIKE ' %::keyword::%, a search with keyword = george (or bush) will find George Bush. BUT, any single quote in the serach will trigger " syntax error" .
That' s my problem!
PS: If I use double quotes, the query is cut as if the (" ) marks the end of the query.
thanks for looking at it.
PS2: Hammish, have you recieved my sample database?
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 