Apostrophes in search

[Trillian]

In my asp FP web (linked to access db) I have one page where users can input
and/or look up film titles from a drop down to display a film review. The
relevant code is below, where FTLIST is the "name" of the dropdown box with
values. This works fine regardless of what the title contains, i.e. the
apostrophe shown in the second example below poses no problem.

fp_sQry="SELECT * FROM TFQFILMS WHERE (FILMTITLE = '::FTLIST::') ORDER BY
FILMTITLE ASC"
fp_sDefault="FTLIST=''"

On my home page I want to display a certain film review based on a title
that I hard code each week. I have confirmed the apostrophe in "king's" is
the problem.

fp_sQry="SELECT * FROM TFQFILMS WHERE (FILMTITLE = 'ALL THE KING'S MEN
(1949)')"
fp_sDefault="'ALL THE KING'S MEN (1949)'"

How do I code this to make it work? Or would I be better off including
another field in my db "selecting" a particular film and then just running a
query to display it on the home page? Since the look-up seems to work this
seems like a solution.

TIA

 

[Andrew Murray]

Remembering from some years ago, I did VB programming classes, and I think
in those cases you use 2 apostrophes side by side like - '' - rather than
one.

Note, 2 x the apostrophe character, not double quotes (") - there is a
difference in how they look. Because I believe the ' is used in ASP coding
as per your query Filmtitle='::ftlist::'). but making it '' it recognises
this as one apostrophe in situations where you need them in correct grammar.

so the query would be


fp_sQry="SELECT * FROM TFQFILMS WHERE (FILMTITLE = 'ALL THE KING''S MEN

(1949)')"
fp_sDefault="'ALL THE KING'S MEN (1949)'"

Note the two apostrophes in "King's" between "G" and the "S".

Try that and see.

If you had a text field and the user types the film's title then clicks
"search" I'm sure the query will return the correct result if the user typed
"All the King's Men" rather than "King''s Men (they won't know to do that
anyway, or understand why they would have to) Use the '' only when
hard-coding the particular film title (as per this specific case).


Regarding how to make the query work, couldn't you base the query on some
other field liek say the "ID" field rather than worrying about how to code
it so it works correctly?

Select * from TFQFILMS where (ID = '::ID::') ORDER BY FILMTITLE ASC
etc.

You can still hard code that, or rerun the database results wizard with the
new query each time.

Hope that makes sense...

 

[Jens Peter Karlsen [FP-MVP]]

Or change it to:
fp_sQry="SELECT * FROM TFQFILMS WHERE (FILMTITLE = 'ALL THE [KING'S]
MEN (1949)')"
fp_sDefault="'ALL THE KING'S MEN (1949)'"

[] is used to enclose words that are either reserved by the dbms (like
join, in, select and so on) or contains special characters.

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

 

[Trillian]

Thanks that did the trick.

You're right, I should be using the ID field...next rewrite that will be the
plan

 

[Trevor L.]

Thanks that did the trick.

You're right, I should be using the ID field...next rewrite that will
be the plan

From my *very* limited use of VBScript, this also applies to double quotes,
e.g. an extract from one of my ASP files of a string used when no records
found:
fp_sNoRecords="<tr><td colspan=7 align=""LEFT"" width=""100%"">No records
returned.</td></tr>"

The align= and width= normally require one double quote, but because they
are inside VBScript, two are needed which are interpreted as one, i.e. the
HTML code is rendered as:
<tr><td colspan=7 align="LEFT" width="100%">No records returned.</td></tr>