Searching for words with apostophes

[Prath]

hi , Could someone tell me how to search for words from the database with aspstrophes like hodgson's or Cabot's Tragopan

[Prath]

hi
i replaced the apostrophe in the search text with 2 single quotes and it works in the query from asp.
I have to query in a store procedure and it executes.

But when i pass the search text from asp to the store procedure it does not work.

This is how i am passing the value to the sp


search_text=Replace(Trim(Request.Form("Search_Box")),"'","''")
Response.Write search_text & "<br>"
'//Code to display the search results using the stored procedure "sp_OBC_Search".
set con = server.CreateObject("ADODB.connection")
con.Open CONNSTR

set cmd = server.CreateObject("ADODB.Command")
cmd.CommandText= "sp_OBC_Search"
cmd.CommandType = 4
cmd.ActiveConnection = con
set param = cmd.CreateParameter("SearchStr",200,1,100,search_text)
cmd.Parameters.Append param
set rsSearch= cmd.Execute



This is my sp

CREATE PROCEDURE sp_OBC_search
@SearchStr varchar(250)
AS

SELECT DISTINCT dbo.OBC_BirdInfo.Bird_ID, dbo.OBC_BirdInfo.Bird_Common_Name, dbo.OBC_BirdInfo.Bird_Scientific_Name, dbo.OBC_BirdInfo.Bird_Intro, dbo.OBC_BirdFamily.Bird_Family_ID, dbo.OBC_BirdFamily.Bird_Family_Name, dbo.OBC_BirdGroup.Bird_Group_Name
FROM dbo.OBC_BirdImages INNER JOIN dbo.OBC_BirdInfo ON dbo.OBC_BirdImages.Bird_ID = dbo.OBC_BirdInfo.Bird_ID INNER JOIN dbo.OBC_BirdFamily ON dbo.OBC_BirdInfo.Bird_Family_ID = dbo.OBC_BirdFamily.Bird_Family_ID INNER JOIN dbo.OBC_BirdGroup ON dbo.OBC_BirdFamily.Bird_Group_ID = dbo.OBC_BirdGroup.Bird_Group_ID
WHERE (dbo.OBC_BirdInfo.Bird_Common_Name LIKE '%' + @SearchStr + '%') OR (dbo.OBC_BirdFamily.Bird_Family_Name LIKE '%' + @SearchStr + '%') OR (dbo.OBC_BirdGroup.Bird_Group_Name LIKE '%' + @SearchStr + '%') OR (dbo.OBC_BirdInfo.Bird_Scientific_Name LIKE '%' + @SearchStr + '%') OR (dbo.OBC_BirdInfo.Bird_Search_KWords LIKE '%' + @SearchStr + '%')



GO

could some please help me with and tell me where iam worng

[Spudhead]

For some reason I'm not quite sure of, I'd think about taking that string concatenation out of the SQL.

So your ASP (sorry, my VBscript ain't beautiful) would read:

search_text=Replace(Trim(Request.Form("Search_Box")),"'","''")
search_text="%"&search_text&"%"

and your SQL would go a little like:

WHERE (dbo.OBC_BirdInfo.Bird_Common_Name LIKE @SearchStr)


Although I might be completely wrong.:rolleyes:

[glenngv]

this is just a guess
don't double the apostrophes!
I think you only should when you are passing the whole SQL statement in the stored procedure or doing rs.Open or conn.Execute

Although I might be also completely wrong :D

[whammy]

I was going to post about the same thing... but I hate to post something if I can't test it first. :(

Hey Mr. Vergara, do you have any recommended Stored Procedures links? :)

I need to learn that right after I cram on XML Web Services.

[glenngv]

I stand corrected. I told you I may be wrong. :D

http://www.4guysfromrolla.com/webtech/020600-1.shtml

Prath, maybe you can fix your problem by following that link.

whammy, actually I have not used Stored Procs before but searching the net, I found this very helpful

http://www.aspin.com/home/tutorial/database/storedpr

[Prath]

hi everyone
Thanks for all your help . I tried the suggestion given , but it seem to not work when i pass the value to the sp.

[glenngv]

did you read the link I gave in my previous post?
it says that:

"To execute a dynamic SQL statement in a stored procedure, you need to use the EXEC function. The EXEC function takes a SQL string as a parameter, and executes that SQL statement. "

here's the sample they gave:


CREATE PROCEDURE MyProc
(@TableName varchar(255),
@FirstName varchar(50),
@LastName varchar(50))
AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT * FROM " +
@TableName + "WHERE FirstName = '"
+ @FirstName + "' AND LastName = '"
+ @LastName + "'"

-- Execute the SQL statement
EXEC(@SQLStatement)

[Prath]

yes i did read the artcle link sent by you

The store procedure executes and work when i run in sql query analyzer , when i pass the value containg a apostrophe.

but when passed from asp it return no records

[glenngv]

can you post the modified SP?

[Prath]

This is the code
search_text=Replace(Trim(Request.Form("Search_Box"))," ' "," ' ' ")

set con = server.CreateObject("ADODB.connection")
con.Open CONNSTR

set cmd = server.CreateObject("ADODB.Command")
cmd.CommandText= "sp_OBC_Search"
cmd.CommandType = 4
cmd.ActiveConnection = con
set param = cmd.CreateParameter("SearchStr",200,1,100,search_text)
cmd.Parameters.Append param
set rsSearch= cmd.Execute

Store Procedure:

CREATE PROCEDURE sp_OBC_search
@SearchStr varchar(250)
AS
DECLARE @searchtext varchar(255)



select @searchtext = "SELECT DISTINCT dbo.OBC_BirdInfo.Bird_ID, dbo.OBC_BirdInfo.Bird_Common_Name, dbo.OBC_BirdInfo.Bird_Scientific_Name, dbo.OBC_BirdInfo.Bird_Intro, dbo.OBC_BirdFamily.Bird_Family_ID, dbo.OBC_BirdFamily.Bird_Family_Name, dbo.OBC_BirdGroup.Bird_Group_Name
FROM dbo.OBC_BirdImages INNER JOIN dbo.OBC_BirdInfo ON dbo.OBC_BirdImages.Bird_ID = dbo.OBC_BirdInfo.Bird_ID INNER JOIN dbo.OBC_BirdFamily ON dbo.OBC_BirdInfo.Bird_Family_ID = dbo.OBC_BirdFamily.Bird_Family_ID INNER JOIN dbo.OBC_BirdGroup ON dbo.OBC_BirdFamily.Bird_Group_ID = dbo.OBC_BirdGroup.Bird_Group_ID
WHERE (dbo.OBC_BirdInfo.Bird_Common_Name LIKE '%' + @SearchStr + '%') OR (dbo.OBC_BirdFamily.Bird_Family_Name LIKE '%' + @SearchStr + '%') OR (dbo.OBC_BirdGroup.Bird_Group_Name LIKE '%' + @SearchStr + '%') OR (dbo.OBC_BirdInfo.Bird_Scientific_Name LIKE '%' + @SearchStr + '%') OR (dbo.OBC_BirdInfo.Bird_Search_KWords LIKE '%' + @SearchStr + '%') "

EXEC(@SQLStatement)

Go

[glenngv]

the sql statement should look like this:

select @searchtext = "SELECT DISTINCT dbo.OBC_BirdInfo.Bird_ID, dbo.OBC_BirdInfo.Bird_Common_Name, dbo.OBC_BirdInfo.Bird_Scientific_Name, dbo.OBC_BirdInfo.Bird_Intro, dbo.OBC_BirdFamily.Bird_Family_ID, dbo.OBC_BirdFamily.Bird_Family_Name, dbo.OBC_BirdGroup.Bird_Group_Name
FROM dbo.OBC_BirdImages INNER JOIN dbo.OBC_BirdInfo ON dbo.OBC_BirdImages.Bird_ID = dbo.OBC_BirdInfo.Bird_ID INNER JOIN dbo.OBC_BirdFamily ON dbo.OBC_BirdInfo.Bird_Family_ID = dbo.OBC_BirdFamily.Bird_Family_ID INNER JOIN dbo.OBC_BirdGroup ON dbo.OBC_BirdFamily.Bird_Group_ID = dbo.OBC_BirdGroup.Bird_Group_ID
WHERE (dbo.OBC_BirdInfo.Bird_Common_Name LIKE '%" + @SearchStr + "%') OR (dbo.OBC_BirdFamily.Bird_Family_Name LIKE '%" + @SearchStr + "%') OR (dbo.OBC_BirdGroup.Bird_Group_Name LIKE '%" + @SearchStr + "%') OR (dbo.OBC_BirdInfo.Bird_Scientific_Name LIKE '%" + @SearchStr + "%') OR (dbo.OBC_BirdInfo.Bird_Search_KWords LIKE '%" + @SearchStr + "%')"

EXEC(@searchtext)


and I think no more GO command

[Prath]

Hi
I tried the exactly the way you sent , but no luck
Prath

[glenngv]

try first executing the SP in the SQL Analyzer passing parameters with and without apostrophes.

[Prath]

works fine in Sql analyzer when i pass the parameters with or without apostophes.

[aCcodeMonkey]

The underhanded trick I use for this is to replace the apostrophe not with '' but the ASCII decimal value '

myNewString = Replace(myOldString,"'","'")

When the text is passed back to the browser automaticvally interprets the ASCII Code and displays ' .
If I have to export the data to say Excel, I just add code to convert the character back to '

myNewString = Replace(oRs("myField"),"'","'")

Hope this helps :cool:

[Prath]

This worked perfectly fine.
I did not replace the apostrophy with double quotes and passed it to the Sp.

Thank you very much for your help.
Prath