Hi! Ihave a problem in my SQL Syntax,

suppose i will search for a name which includes apostrophe (')
for ex:

SELECT * FROM tblCompany WHERE COMPNAME = 'ALYSONS' MERCHANDISE'

this will prompt an error:
     Server: Msg 170, Level 15, State 1, Line 1
     Line 1: Incorrect syntax near 'MERCHANDISE'.
     Server: Msg 105, Level 15, State 1, Line 1
     Unclosed quotation mark before the character string '
     '.

how can i search for a name with an apostrophe on it?
please help....
thank you!!!!

Hi yves,

Use this,

SELECT * FROM tblCompany WHERE COMPNAME = 'ALYSONS'+''''+'MERCHANDISE'


nalaka hewage

One way
SELECT * FROM tblCompany WHERE COMPNAME = 'ALYSONS'' MERCHANDISE'

just double the single quote

Test it out

create table #test (COMPNAME varchar(50))
insert into #test
select 'ALYSONS'' MERCHANDISE'

select * from #test


SELECT * FROM #test WHERE COMPNAME = 'ALYSONS'' MERCHANDISE'

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

Thank you so much for your replies... but the scenario is this:
suppose i have a variable containing the name:

/* ASSUMING THE USER INPUTS: ALYSONS' STORE*/
/* ACTUALLY INPUTS WILL BE PASSED TO A VARIABLE SO I HAVE NO CONTROL
   OF DOUBLING THE QUOTE, I AM ACTUALLY USING VB 6 AS FRONT END. */


s = "SELECT * FROM tblCompany WHERE COMPNAME = '" & txtCompName & "'"

/* where s is actually:
  SELECT * FROM tblCompany WHERE COMPNAME = 'ALYSONS' STORE'
*/

/*so when i call this query:*/

set rs = cn.execute (s)

/* forsure an error will come out*/

I HOPE YOU WILL STILL FIND TIME TO GIVE ME SOME SUGGESTIONS AND IDEAS WITH THIS... THANKS A LOT.

If you aren't using the quoted identifiers option then use double quotes..

s = "SELECT * FROM tblCompany WHERE COMPNAME = """ & txtCompName & """"
or
s = "SELECT * FROM tblCompany WHERE COMPNAME = \"" & txtCompName & "\""
Should give you
SELECT * FROM tblCompany WHERE COMPNAME = "ALYSONS' STORE"

Another option would be:
Create a function to parse through the txtCompName variable looking for single quotes,
if a single quote is found then replace single quote with two single quotes.

"ALYSONS' STORE" would go into the function and "ALYSONS'' STORE" would be returned.

Or you could create a function i VB that takes a string and replaces every occurance of ' with ''
.. and then use that in place of your variabel.

Gert

Alright!!!
Thank you so much guys!!!
These really helps... Thanks!!!!
God bless!!!