single to double quotes to input parameter of a stored procedure...does not work

[rk325]

Hi all,

I'm using VB.NET for this one, and it is straight forward.
I have a sp that takes an input parameter (a string) for querying the
db. Here is the sp:

CREATE PROCEDURE [dbo].[web_SearchByName]
@LastName varchar(80)
AS
SELECT Parcel.[Parcel ID],[Parcel Number],
RTRIM([address Number]) + ' ' + RTRIM([Address Street Name])
As [Address], [Last Name]
FROM Parcel INNER JOIN [Ownership] on
[Ownership].[Parcel Id]= [Parcel].[Parcel Id]
INNER JOIN [Entity] ON
[Entity].[Entity ID]=[Ownership].[Entity ID]
WHERE [TO DATE] IS NULL and [Primary owner]=1 and
[Last Name] LIKE @LastName
ORDER BY [Last Name]
--REPLACE(@LastName, CHAR(39), CHAR(39) + CHAR(39))
GO

Note, I even used the REPLACE statement after LIKE instead of the plain
@LastName, but still does not work.

In my code, I format the input string to have doubel quotes if there is
a single quote:

myString = Replace(mysring, "'", "''")

and I do verify that the input parameter contains double quotes when
running from source code ......but I still do not get results.

Any ideas why?

I also tried replacing each sinque quote by 4 consecutive single
quotes, and nothing.

Please your quick will be appreciated.

 

[Elton W]

Hi Ocio,

Do you use Wildcards for @LastName?
WHERE [Last Name] LIKE 'katsanis' doesn't work.
WHERE [Last Name] LIKE '%katsanis%' works.

HTH

Elton Wang
(e-mail address removed)

 

[rk325]

Yes I do.
My code inserts a wildchar at the end of the input string before it is
passed to the actual sp.
It works fine with searches that do not include quotes.
For example if I want to search all lastnames that start with "o", the
code searches as "o%".
But if I try to serahc for "o'neal" , the code searches as "o''neal%"
(note the 2 single quotes between the o and the n)...but it returns
nothing.
When I search under "o" only I do get a bunch of "o'neal"

Do not understand what the problem could be.

 

[Jim Hughes]

Don't double the single quotes when using a parameterized query. Using a
parameter takes care of that for you!

Doubling quotes is only necessary if you are using a literal sql commandtext
statement.