Problem with searching a table containing apostrophes
-
This problem is driving me mad. If I had hair I'd have pulled it out by now
I have a table which contains thousands of book titles. A high percentage of them contain apostrophes.
Now, from my search web page the visitor can type in a book title and my code goes off and tries to find it.
The problem I'm having is if the database contains a value like Harry Potter's Adventure (with an apostrophe) and the visitor types in a book title to search on like Harry Potters Adventure (without an apostrophe), my code will not detect this book within the database.
Here's the code:-
TitleBox = "Harry"
strSql = "SELECT * FROM BookDetails WHERE BookTitle LIKE '" & TitleBox & "%'"
Is there a coding technique out there which can detect these elusive books that contain those pesky apostrophes?
Thanks
-
Declare @Title
set @Title = 'Harry potter''s adventure'
Select * from dbo.BookDetails where Title LIKE '%' + @Title + '%'
Put this in a stored proc and you're set.
-
Or use the REPLACE function on the field you are comparing and strip the apostrophe out completely when comparing [ LIKE ].
I wasn't born stupid - I had to study.
-
Sorry to be a pain in the derriere, but maybe I should have mentioned that I'm still pretty new to SQL
I think I understand the logic behind this solution, but how do I send over the code from the form i.e. Request.Form("BookTitle") to the stored procedure so that it is used to check against every single row within the BookDetails table?
Thanks for the incredibly quick reply btw.
-
assuming you are using asp and not .net, because you were referencing Request.Form, i think you could use something like this to get your recordset of values.
you still have to create teh stored procedure, and make sure the sp appends the % to the input value for a like comparison.
Dim ConnObj
Dim CmdObj
Dim paramObj
dim rs
dim searchstring
searchstring = Request.Form("BookTitle")
set ConnObj = Server.CreateObject("ADODB.Connection")
ConnObj.ConnectionString= "Provider=SQLOLEDB;Server=192.168.1.1;database=GHBA;network=DBMSSOCN;uid=tester;pwd=password"
cnObj.open
set CmdObj = Server.CreateObject("ADODB.Connection")
set paramObj =Server.CreateObject("ADODB.Parameter")
CmdObj.ActiveConnection = ConnObj
CmdObj.CommandText = "pr_GetBookTitles" 'name of the stored procedure
CmdObj.CommandType = adCmdStoredProc
Set paramObj = CmdObj.CreateParameter("@Return", adInteger, adParamInput,,searchstring)
CmdObj.Parameters.Append paramObj
set rs= CmdObj.Execute
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
This is the procedure to write on the server
CREATE PROCEDURE dbo.SearchBookDetails @Title as varchar(50)
AS
SET NOCOUNT ON
SELECT
Col1, Col2
FROM dbo.BookDetails
WHERE Title LIKE '%' + @Title + '%'
SET NOCOUNT OFF
GO
now to call it
the easy way :
Recordset.open "EXEC dbo.SearchBookDetails " & Request.Form("BookTitle")
THE right way (note this is originally written in vb and there are a few modifications to make it work in ASP that have been commented out) :
Dim MyCmd 'As ADODB.Command
Set MyCmd = Server.CreateObject("ADODB.Command")
MyCmd.CommandText = "dbo.SearchBookDetails "
MyCmd.CommandType = 4 'adCmdStoredProc
Dim MyParam 'As ADODB.Parameter
Set MyParam = Server.CreateObject("ADODB.Parameter")
MyParam.Name = "@NomGarage"
MyParam.Value = TRIM(Request.Form("BookTitle"))
MyParam.Size = 50
MyParam.Direction = 1 'adParamInput
MyParam.Type = 200 'adVarChar
MyCmd.Parameters.Append MyParam
Dim MyRs 'As ADODB.Recordset
Set MyRs = Server.CreateObject("ADODB.Recordset")
MyRs.CursorLocation = 3 'adUseClient
MyCmd.ActiveConnection = MyCn 'your connection object
MyRs.Open MyCmd, , 1, 3
If Not MyRs.EOF Then
'you have results to show
Else
'err mess?
End If
Set MyParam = Nothing
Set MyCmd = Nothing
-
Hey Lowell, there's a catch in asp : the vb constants are not all available : (adInteger) doesn't exists unless you have some include file that I don't remember.
-
dang i knew that too Remi; just didn't include it in my attempt for a sample; i get to do mostly .net winforms and pages now, so my vb is ASP is getting rusty.
Thanks!
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I wish I could go back to .net... stuck in access
. -
The include file is ADOVBS.INC. Get it here http://www.asp101.com/articles/john/adovbs/default.asp. Just remember to rename it to a .ASP file since someone can browse to a .INC file and actually see the contents. Not a big deal if you use it verbatim, but if you stick other constants in that file, having someone see them might be bad...
-
I would not recommend including the entire ADOVBS.inc file in an ASP page though. That is one of the extremely many problems with ASP, an include includes everything and not only the stuff you need. That means the ASP page gets larger than necessary and takes longer to load. :/
Remi, I feel very sorry for you having to work in Access.
On the other hand, I have the fortune of working with C#, but instead I need to work a lot with XML which is equally bad in my mind.
-
The boss has started thinking that it's time to start doing a full rewrite of the application... I'll try making him chose the .net environement... and then I'll chose C# web pages
. when is 2005 coming out ??? -
I think that xml can be good once is a while... It's like cursor, they have their good use that will beat a set solution... but it happens so rarely that we can forget about that
. -
someone who posts here has a tagline that says:
XML is like violence...if it doesn't solve your problem your not using enough of it.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Wow, that's a nice quote.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply