• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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

0
HKFuey
Asked:
HKFuey
  • 3
2 Solutions
 
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:
Problem was with the content management software, thanks for the help
0
 
HKFueyAuthor Commented:
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now