apostrophe

i'm using delphi 7 and have a query in which i'm trying to find names
that have an apostrophe in them, i.e. "o'mally". my problem is that
when i write my select statement i can't get the quotes right. i get
all types of errors no matter what i try. i get "missing right quote",
"invalid token" etc. i've tried using QuotedStr and nothing works.
here is my current attempt in which i get an "invalid use of keyword.
token: n'%'"

sSQL := 'SELECT statenotified, notary_id, LastName, FirstName,' +
' MiddleInitial, Indep, Book_number, Page_number,' +
' CloseRec, TermBegins, TermEnds, DatePickedUp,
FailedToQualify,' +
' Notes, SOSLtrSent FROM notaries WHERE LastName LIKE '''+
''+ edtLastName.Text+'+''%'' AND CloseRec = 1 order by
lastname';Michael Sterling (stermic@.gw.co.jackson.mo.us) writes:
> i'm using delphi 7 and have a query in which i'm trying to find names
> that have an apostrophe in them, i.e. "o'mally". my problem is that
> when i write my select statement i can't get the quotes right. i get
> all types of errors no matter what i try. i get "missing right quote",
> "invalid token" etc. i've tried using QuotedStr and nothing works.
> here is my current attempt in which i get an "invalid use of keyword.
> token: n'%'"
> sSQL := 'SELECT statenotified, notary_id, LastName, FirstName,' +
> ' MiddleInitial, Indep, Book_number, Page_number,' +
> ' CloseRec, TermBegins, TermEnds, DatePickedUp,
> FailedToQualify,' +
> ' Notes, SOSLtrSent FROM notaries WHERE LastName LIKE '''+
> ''+ edtLastName.Text+'+''%'' AND CloseRec = 1 order by
> lastname';

In T-SQL, you include the string delimiter in a string in the same way
as you do in Pascal - you double it. And with ' as the string delimiter
in both languages, you get more ' than is good for your health.

One idea is to declare constants the various variants of nested quotes,
so you don't have to count quotes for each query.

Better though, is to use stored procedures or prepared queries in
which case the client library will take care of the quoting for you.
Unfortunately, I don't much about Delphi, so I don't know what is
available.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You need to put it within two more apostrophes so ' becomes '''. If
you are writing your query to do this in Delphi, try adding a
parameter to the query and pass the parameter including your
apostrophe into the statement. You can put in the wildcard statement
into your query including the parameter.

Using your example this would be something like,

SELECT
statenotified, notary_id, LastName, FirstName,
MiddleInitial, Indep, Book_number, Page_number,
CloseRec, TermBegins, TermEnds, DatePickedUp, FailedToQualify,
Notes, SOSLtrSent

FROM
notaries

WHERE
LastName LIKE '%' + :myParam + '%'
/*Wildcard added to either end if you need it*/
AND CloseRec = 1

Order By
lastname

Then in your code, something like

Query.Parameters.ParamByName('myParam').Value := edtLastName.Text;

Put this in before you run your query and it should work.

This way, you pass the value which will include the apostrophe to the
parameter instead of trying to pass the text which gets a little
confusing. Using the example above, it will run the SQL for anything
in your parameter. If you wanted to find a single apostrophe, then you
can pass a single one into your parameter. So you could use :

Query.Parameters.ParamByName('myParam').Value := ''';

Best of luck

Ryan

www.ryan.dial.pipex.com

stermic@.gw.co.jackson.mo.us (Michael Sterling) wrote in message news:<f7359f44.0312180837.5f2c1b82@.posting.google.com>...
> i'm using delphi 7 and have a query in which i'm trying to find names
> that have an apostrophe in them, i.e. "o'mally". my problem is that
> when i write my select statement i can't get the quotes right. i get
> all types of errors no matter what i try. i get "missing right quote",
> "invalid token" etc. i've tried using QuotedStr and nothing works.
> here is my current attempt in which i get an "invalid use of keyword.
> token: n'%'"
> sSQL := 'SELECT statenotified, notary_id, LastName, FirstName,' +
> ' MiddleInitial, Indep, Book_number, Page_number,' +
> ' CloseRec, TermBegins, TermEnds, DatePickedUp,
> FailedToQualify,' +
> ' Notes, SOSLtrSent FROM notaries WHERE LastName LIKE '''+
> ''+ edtLastName.Text+'+''%'' AND CloseRec = 1 order by
> lastname';