Single Quotes converted to 2 single quotes

Single Quotes converted to 2 single quotes

Post by Jon Byr » Wed, 20 Mar 2002 21:40:54



Hi,
I'm attempting to build a query from within a method that takes the text
entered by a user and removes the spaces. The spaces are replaced by + '%' +
. This is for use in a SQL Server Database and it worked fine in classic ASP
where I built it with VBScript and passed the query as one string.
Now using the SQLClient on ASP.NET, it is taking the single quotes and
replacing them with two single quotes, my guess is that it thinks the single
quote is part of the string.
So if the user types in (search text),
I want the where clause to be:

WHERE ProductNumber LIKE '%' + search + '%' + text +'%'

But what I'm getting is:

WHERE ProductNumber LIKE ' '%' ' + search + ' '%' ' + text + ' '%' '

The query is in a stored procedure, so in the above example, I'm passing (or
attempting to rather) '%' + search + '%' + text + '%' to an input parameter.
It seems not to do this when I query directly by passing an entire SQL
String from ASP.NET. It's just when passing these parameters to a stored
procedure.

And of course this returns no results. How can I prevent this from
happening?

Thanks in advance.

Jon Byrd

 
 
 

Single Quotes converted to 2 single quotes

Post by James Well » Thu, 04 Apr 2002 06:56:37


Jon,

Use the ASCII code for single quote   - CHR(39) in the SQL string to build a
single quote in .

Eg
WHERE ProductNumber LIKE  + CHR(39) + '%' + CHAR(39) etc. ......

CHR(39) should return the single quote, but check this out, because this is
from memory....

Regards

James


Quote:> Hi,
> I'm attempting to build a query from within a method that takes the text
> entered by a user and removes the spaces. The spaces are replaced by + '%'
+
> . This is for use in a SQL Server Database and it worked fine in classic
ASP
> where I built it with VBScript and passed the query as one string.
> Now using the SQLClient on ASP.NET, it is taking the single quotes and
> replacing them with two single quotes, my guess is that it thinks the
single
> quote is part of the string.
> So if the user types in (search text),
> I want the where clause to be:

> WHERE ProductNumber LIKE '%' + search + '%' + text +'%'

> But what I'm getting is:

> WHERE ProductNumber LIKE ' '%' ' + search + ' '%' ' + text + ' '%' '

> The query is in a stored procedure, so in the above example, I'm passing
(or
> attempting to rather) '%' + search + '%' + text + '%' to an input
parameter.
> It seems not to do this when I query directly by passing an entire SQL
> String from ASP.NET. It's just when passing these parameters to a stored
> procedure.

> And of course this returns no results. How can I prevent this from
> happening?

> Thanks in advance.

> Jon Byrd


 
 
 

1. Replace single quote with two single quotes

is there a T-sql replacement for the following:

sLogin = Replace(Request.Form("Login"), "'", "''")
This code in the asp page replaces the single ' with 2 '' which is accepted
by SQL if the data being passed is O'Leary it becomes O''Leary and is used
by SQL then.

I've search the BOL, TechNet and I only find examples for what I already
know...either build an asp page to parse the data or use VB.

Signed,

A good DBA that is becoming frustrated

2. add row's numbers in <select_list>

3. Single-quote and double-quote characters in fields

4. Workflow problem with SQL Server 6.5

5. help: double quote vs. single quote string delimiter

6. Preventing VB5 from Locking files

7. saving data with single quote or double quote

8. Oracle Intelligent Agent Service problem!!!!

9. rules of single quotes and double quotes

10. Convert single quote automatically?

11. Single Quotes Converted in Stored Procedure

12. Converting "Single Quotes" in Stored Procs

13. help with double and single quotes, there must be a better way