Web search on SQL database fails on backslash

We have a web site and if you search for a product code with 2 slashes e.g. 123/456/7 in it fails. The search seems to be controlled from a stored procedure (see below) can anyone help?


 
-- Search Products (name,Code)
insert into @SearchResults (ResultID,ResultName,ResultType,ResultLink,ResultDescription,ResultImage,ResultMixed,Sort,ResultDescription2)
select DISTINCT OurCompany_Product.ProductID, OurCompany_Product.[Name],'Products','Product.aspx?ProductID='+cast(OurCompany_Product.ProductID as varchar),
cast(OurCompany_Product.Description as varchar(5000)),OurCompany_Product.ImageUrl,Code,1,cast(OurCompany_Product.Summary as varchar(5000))
from OurCompany_Product
left outer join OurCompany_Product_ProductCategory on OurCompany_Product.ProductID=OurCompany_Product_ProductCategory.ProductID
INNER JOIN   OurCompany_ProductCategory ON OurCompany_Product_ProductCategory.ProductCategoryID = OurCompany_ProductCategory.ProductCategoryID
where (OurCompany_Product.[Name] like '%'+@SearchText +'%' or Code like '%'+@SearchText +'%') AND OurCompany_Product_ProductCategory.ProductCategoryID <> @GenAccessID AND OurCompany_Product.Live = 1
AND Code NOT IN (SELECT ResultMixed FROM @SearchResults) AND OurCompany_Product_ProductCategory.ProductCategoryID <> @SparePartID;

Open in new window

HKFueyAsked:
Who is Participating?
 
HKFueyAuthor Commented:
Problem was with the content management software, thanks for the help
0
 
Brendt HessSenior DBACommented:
Can you output the SQL statement above (as substituted) as a string and run it manually?  There should be no reason for a double-slash to cause an issue in this SQL code.  

What I would like to see is SQL code like:

select DISTINCT 
	OurCompany_Product.ProductID, 
	OurCompany_Product.[Name],
	'Products',
	'Product.aspx?ProductID='+cast(OurCompany_Product.ProductID as varchar),
	cast(OurCompany_Product.Description as varchar(5000)),
	OurCompany_Product.ImageUrl,
	Code,
	1,
	cast(OurCompany_Product.Summary as varchar(5000))
from OurCompany_Product
left outer join OurCompany_Product_ProductCategory 
	on OurCompany_Product.ProductID=OurCompany_Product_ProductCategory.ProductID
INNER JOIN OurCompany_ProductCategory 
	ON OurCompany_Product_ProductCategory.ProductCategoryID = OurCompany_ProductCategory.ProductCategoryID
where (
	OurCompany_Product.[Name] like '%123/456/7%' 
	or Code like '%123/456/7%'
	) 
	AND OurCompany_Product_ProductCategory.ProductCategoryID <> <Value here> -- @GenAccessID 
	AND OurCompany_Product.Live = 1
	AND Code NOT IN (
		SELECT ResultMixed 
		FROM @SearchResults
		) 
	AND OurCompany_Product_ProductCategory.ProductCategoryID <> <Value here> -- @SparePartID;

Open in new window


Then run that SQL Statement (comment out the subselect section on @SearchResults.  If it works - then something else is the problem.  If it does not work - then work through the query, step by step, to see why.
0
 
mrGreenCommented:
Hi, do you get an error or just no results?

Regards
0
 
HKFueyAuthor Commented:
Hi Guys,
Just testing now but beginning to think bhess1 is right and the SP is OK and the issue is with the C Sharp code that is calling it.
HK
0
 
HKFueyAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.