Problems pulling records containing Single Quotation marks

[Rexolio]

I have an ASP that is supposed to allow the user to click on a Client's Business name, which will return all location information from any and all records for that business name and business address.

The problem I'm having is that the script wouldn't return Business names with single quotation marks (i.e. Sam's Club). I thought I understood the reason, so I went from this...

SQL="SELECT DISTINCT Location FROM tblDirectory WHERE CompanyName = '" & CompanyName & "' AND Address = '" & Address & "' ORDER BY Location"

to this...

CompanyName = Replace(CompanyName,"'","&quot
LoSQL="SELECT DISTINCT Location FROM tblDirectory WHERE CompanyName = '" & CompanyName & "' AND Address = '" & Address & "' ORDER BY Location"

Now, what the new, second script does is only pull records for CompanyNames that HAVE single quotation marks. If the CompanyName doesn't have single quote marks, it doesn't return anything.

I've tried the following also without a change

CompanyName = Replace(CompanyName,"'","''&quot

PLEASE HELP!

 

[Olav]

Try this:

CompanyName = Replace(CompanyName,"'","?&quot

before you set up the SQL

and in your SQL

... where CompanyName like '" & CompanyName & "' .....

Olav

 

[sweevo]

You do need to change the single quote to two single quotes (like you tried... replace(CompanyName, "'", "''&quot)

but use LIKE in your SQL:

LoSQL="SELECT DISTINCT Location FROM tblDirectory WHERE CompanyName LIKE '" & CompanyName & "' AND Address = '" & Address & "' ORDER BY Location"

That should work, at least in SQL Server or Access.