I have a Data Results Page that works fine, except for one search. The only
reason I can think of for this failure is that the search term includes an
apostrophe (Raven's Tale).

The query is below. It's looking for e-mail addresses of writers attached
to particular projects (mostly names of plays). I gather that the issue is
with the WHERE clause toward the end.

SELECT Projects.ProjectID, Projects.ProjectType, Projects.ProjectGroup,
Projects.ProjectName, Projects.ProjectDescription, Projects.[Project
Credit], Project_Writers.ID, Project_Writers.Book, Project_Writers.Lyrics,
Project_Writers.Music, Members.LastName, Members.FirstName, Members.Address,
Members.City, Members.State, Members.Code, Members.HomePhone,
Members.WorkPhone, Members.CellPhone, Members.EmailAddress
FROM Projects INNER JOIN (Members INNER JOIN Project_Writers ON Members.ID =
Project_Writers.ID) ON Projects.ProjectID = Project_Writers.ProjectID WHERE
(Projects.ProjectID = ::ProjectID: ORDER BY LastName ASC,FirstName ASC;

Is there a way to adjust the query to account for possible apostrophes?

Thanks.

Stephen

With SQL Server, the WHERE can use REPLACE(searchValue, '''','''''')

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

"Stephen Green" wrote:

> I have a Data Results Page that works fine, except for one search. The only
> reason I can think of for this failure is that the search term includes an
> apostrophe (Raven's Tale).
>
> The query is below. It's looking for e-mail addresses of writers attached
> to particular projects (mostly names of plays). I gather that the issue is
> with the WHERE clause toward the end.
>
> SELECT Projects.ProjectID, Projects.ProjectType, Projects.ProjectGroup,
> Projects.ProjectName, Projects.ProjectDescription, Projects.[Project
> Credit], Project_Writers.ID, Project_Writers.Book, Project_Writers.Lyrics,
> Project_Writers.Music, Members.LastName, Members.FirstName, Members.Address,
> Members.City, Members.State, Members.Code, Members.HomePhone,
> Members.WorkPhone, Members.CellPhone, Members.EmailAddress
> FROM Projects INNER JOIN (Members INNER JOIN Project_Writers ON Members.ID =
> Project_Writers.ID) ON Projects.ProjectID = Project_Writers.ProjectID WHERE
> (Projects.ProjectID = ::ProjectID: ORDER BY LastName ASC,FirstName ASC;
>
> Is there a way to adjust the query to account for possible apostrophes?
>
> Thanks.
>
> Stephen
>
>
>

Gregory!

Thanks for the reply. I'm having trouble reading the quotes/apostrophes on
my monitor. Are there single and double quotes there? The only offending
title at the moment is A Raven's Tale.

You know, now that I think of it, I included the wrong query. The query
that deals with the actual ProjectName field is a drop down SELECT that
currently reads:

SELECT ProjectID, ProjectName FROM Projects

The ProjectName is displayed/selected and the respective ProjectID is stored
to a variable.

Can I use the REPLACE in the SELECT statement?

Thanks again.

Stephen


"Cowboy (Gregory A. Beamer) - MVP" <(E-Mail Removed)> wrote
in message news:5CFEA842-0CBF-46AB-BFC2-(E-Mail Removed)...
> With SQL Server, the WHERE can use REPLACE(searchValue, '''','''''')
>
> ---
>
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ***************************
> Think Outside the Box!
> ***************************
>
> "Stephen Green" wrote:
>
> > I have a Data Results Page that works fine, except for one search. The
only
> > reason I can think of for this failure is that the search term includes
an
> > apostrophe (Raven's Tale).
> >
> > The query is below. It's looking for e-mail addresses of writers
attached
> > to particular projects (mostly names of plays). I gather that the issue
is
> > with the WHERE clause toward the end.
> >
> > SELECT Projects.ProjectID, Projects.ProjectType, Projects.ProjectGroup,
> > Projects.ProjectName, Projects.ProjectDescription, Projects.[Project
> > Credit], Project_Writers.ID, Project_Writers.Book,
Project_Writers.Lyrics,
> > Project_Writers.Music, Members.LastName, Members.FirstName,
Members.Address,
> > Members.City, Members.State, Members.Code, Members.HomePhone,
> > Members.WorkPhone, Members.CellPhone, Members.EmailAddress
> > FROM Projects INNER JOIN (Members INNER JOIN Project_Writers ON
Members.ID =
> > Project_Writers.ID) ON Projects.ProjectID = Project_Writers.ProjectID
WHERE
> > (Projects.ProjectID = ::ProjectID: ORDER BY LastName ASC,FirstName
ASC;
> >
> > Is there a way to adjust the query to account for possible apostrophes?
> >
> > Thanks.
> >
> > Stephen
> >
> >
> >

Sure Stephen,

In SQL, the single quote (apostrophe) is the delimiter for a string, or text
value, such as:

SELECT * FROM MyTable WHERE LastName = 'Smith'

To insert a literal single quote into a string in a SQL Statement, you
escape it by doubling:

SELECT * FROM MyTable WHERE LastName = 'O''Toole'

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Stephen Green" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a Data Results Page that works fine, except for one search. The
only
> reason I can think of for this failure is that the search term includes an
> apostrophe (Raven's Tale).
>
> The query is below. It's looking for e-mail addresses of writers attached
> to particular projects (mostly names of plays). I gather that the issue
is
> with the WHERE clause toward the end.
>
> SELECT Projects.ProjectID, Projects.ProjectType, Projects.ProjectGroup,
> Projects.ProjectName, Projects.ProjectDescription, Projects.[Project
> Credit], Project_Writers.ID, Project_Writers.Book, Project_Writers.Lyrics,
> Project_Writers.Music, Members.LastName, Members.FirstName,
Members.Address,
> Members.City, Members.State, Members.Code, Members.HomePhone,
> Members.WorkPhone, Members.CellPhone, Members.EmailAddress
> FROM Projects INNER JOIN (Members INNER JOIN Project_Writers ON Members.ID
=
> Project_Writers.ID) ON Projects.ProjectID = Project_Writers.ProjectID
WHERE
> (Projects.ProjectID = ::ProjectID: ORDER BY LastName ASC,FirstName ASC;
>
> Is there a way to adjust the query to account for possible apostrophes?
>
> Thanks.
>
> Stephen
>
>

Thanks, Kevin!

But what if the apostrophe isn't what's used in the comparison?

I have a drop down from which to select Project Names (one of which is A
Raven's Tale) - the related ProjectID is saved.

Then a Database Result form that displays data from a different table based
on a comparison of that Project ID - [(WHERE (Projects.ProjectID =
::ProjectID:.

I'm not sure that it is the cause of the malfunction, but I suppose that
since the comma is in the Project Name, the query fails.

All of the other titles (none of the others have apostrophes) work fine.

Thanks so much for helping.

Stephen


"Kevin Spencer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sure Stephen,
>
> In SQL, the single quote (apostrophe) is the delimiter for a string, or
text
> value, such as:
>
> SELECT * FROM MyTable WHERE LastName = 'Smith'
>
> To insert a literal single quote into a string in a SQL Statement, you
> escape it by doubling:
>
> SELECT * FROM MyTable WHERE LastName = 'O''Toole'
>
> --
> HTH,
> Kevin Spencer
> .Net Developer
> Microsoft MVP
> I get paid good money to
> solve puzzles for a living
>
> "Stephen Green" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I have a Data Results Page that works fine, except for one search. The
> only
> > reason I can think of for this failure is that the search term includes
an
> > apostrophe (Raven's Tale).
> >
> > The query is below. It's looking for e-mail addresses of writers
attached
> > to particular projects (mostly names of plays). I gather that the issue
> is
> > with the WHERE clause toward the end.
> >
> > SELECT Projects.ProjectID, Projects.ProjectType, Projects.ProjectGroup,
> > Projects.ProjectName, Projects.ProjectDescription, Projects.[Project
> > Credit], Project_Writers.ID, Project_Writers.Book,
Project_Writers.Lyrics,
> > Project_Writers.Music, Members.LastName, Members.FirstName,
> Members.Address,
> > Members.City, Members.State, Members.Code, Members.HomePhone,
> > Members.WorkPhone, Members.CellPhone, Members.EmailAddress
> > FROM Projects INNER JOIN (Members INNER JOIN Project_Writers ON
Members.ID
> =
> > Project_Writers.ID) ON Projects.ProjectID = Project_Writers.ProjectID
> WHERE
> > (Projects.ProjectID = ::ProjectID: ORDER BY LastName ASC,FirstName
ASC;
> >
> > Is there a way to adjust the query to account for possible apostrophes?
> >
> > Thanks.
> >
> > Stephen
> >
> >
>
>