Help! Can't pass names with apostrophe from ASP to SQL7 proc

C

crab.dae

I have a proc that does wildcard searches on names which works fine
expect with names that contain an apostrophe like O'Neil.

When O'Neil is sent to the proc, I get "Incorrect syntax near 'Neil'."
This tells me I've got a delimiter issue.

I've tried replacing the single apostrophe with a double or even using
quotes as the delimiter and still get an error. What should I try; can
anyone help?

BTW, here's an example of the proc.
=====================================================

CREATE proc getName
@CUSTNAME varchar(15)
as
declare @SQL varchar(4000)

set @SQL = 'select PARENT_NAME, CITY, STATE, ZIP from
[CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%' + @CUSTNAME + '%'''
exec (@SQL)

======================================================

I've passed the name as below and still get errors.

getName O'Neil
getName 'O'Neil'
getName ''O'Neil'' - All Single quotes chr (39)
getName "O'Neil" - Quotes on the outside chr(34)
getName 'O''Neil' - All Single quotes chr (39)
getName "O''Neil" - Quotes on the outside chr(34), and double singles
in the name chr(39)

Nothing works due to the syntex. What can I do, please HELP!!

Thank you,

Dae
 
G

Gary Walter

PARENT_NAME like ''%'' + @CUSTNAME + ''%'''

getName "O'Neil" - Quotes on the outside chr(34)
 
G

Gary Walter

on further thinking (sorry)

PARENT_NAME like ''%''' + @CUSTNAME + '''%''')

or at least this is how I look at it:

CREATE proc getName
@CUSTNAME varchar(15)
as

exec ( 'select
PARENT_NAME,
CITY, STATE, ZIP
from [CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%''' + @CUSTNAME + '''%''')

all are single quotes

you want double single quotes
around each %
because they are within
a string delimited by
single quotes


plus you want to separate

+ @CUSTNAME +

from the string sql
with single quotes
since it was declared
outside of the exec



Gary Walter said:
PARENT_NAME like ''%'' + @CUSTNAME + ''%'''

getName "O'Neil" - Quotes on the outside chr(34)


I have a proc that does wildcard searches on names which works fine
expect with names that contain an apostrophe like O'Neil.

When O'Neil is sent to the proc, I get "Incorrect syntax near 'Neil'."
This tells me I've got a delimiter issue.

I've tried replacing the single apostrophe with a double or even using
quotes as the delimiter and still get an error. What should I try; can
anyone help?

BTW, here's an example of the proc.
=====================================================

CREATE proc getName
@CUSTNAME varchar(15)
as
declare @SQL varchar(4000)

set @SQL = 'select PARENT_NAME, CITY, STATE, ZIP from
[CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%' + @CUSTNAME + '%'''
exec (@SQL)

======================================================

I've passed the name as below and still get errors.

getName O'Neil
getName 'O'Neil'
getName ''O'Neil'' - All Single quotes chr (39)
getName "O'Neil" - Quotes on the outside chr(34)
getName 'O''Neil' - All Single quotes chr (39)
getName "O''Neil" - Quotes on the outside chr(34), and double singles
in the name chr(39)

Nothing works due to the syntex. What can I do, please HELP!!

Thank you,

Dae
Click to expand...
Click to expand...
 
C

crab.dae

Gary said:
on further thinking (sorry)

PARENT_NAME like ''%''' + @CUSTNAME + '''%''')

or at least this is how I look at it:

CREATE proc getName
@CUSTNAME varchar(15)
as

exec ( 'select
PARENT_NAME,
CITY, STATE, ZIP
from [CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%''' + @CUSTNAME + '''%''')

all are single quotes

you want double single quotes
around each %
because they are within
a string delimited by
single quotes


plus you want to separate

+ @CUSTNAME +

from the string sql
with single quotes
since it was declared
outside of the exec



Gary Walter said:
PARENT_NAME like ''%'' + @CUSTNAME + ''%'''

getName "O'Neil" - Quotes on the outside chr(34)
Click to expand...
Click to expand...

Gary,

Do you have any other ideas? I tried it and now I get:
Incorrect syntax near 'O'.
Unclosed quotation mark before the character string ''.

If I try an name like Smith, I get the error:
Incorrect syntax near 'Smith'.


Thanks,

Dae
 
C

crab.dae

[email protected] said:
Gary said:
on further thinking (sorry)

PARENT_NAME like ''%''' + @CUSTNAME + '''%''')

or at least this is how I look at it:

CREATE proc getName
@CUSTNAME varchar(15)
as

exec ( 'select
PARENT_NAME,
CITY, STATE, ZIP
from [CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%''' + @CUSTNAME + '''%''')

all are single quotes

you want double single quotes
around each %
because they are within
a string delimited by
single quotes


plus you want to separate

+ @CUSTNAME +

from the string sql
with single quotes
since it was declared
outside of the exec



Gary Walter said:
PARENT_NAME like ''%'' + @CUSTNAME + ''%'''

getName "O'Neil" - Quotes on the outside chr(34)
Click to expand...
Click to expand...

Gary,

Do you have any other ideas? I tried it and now I get:
Incorrect syntax near 'O'.
Unclosed quotation mark before the character string ''.

If I try an name like Smith, I get the error:
Incorrect syntax near 'Smith'.


Thanks,

Dae
Click to expand...

Gary,

I just got a reply back on another group that told me to use four
single quotes in the name and it works perfectly. Thanks for your
help.

- Dae
 
Top