Database Results problem - apostrophe?

  • Thread starter Thread starter Stephen Green
  • Start date Start date
S

Stephen Green

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!
***************************
 
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 said:
With SQL Server, the WHERE can use REPLACE(searchValue, '''','''''')

---

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

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

Stephen Green said:
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
Click to expand...
Click to expand...
 
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
 
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 said:
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 said:
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
Click to expand...
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
Click to expand...
Click to expand...
 
Back
Top