Search by ProductId or Product Description

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Search by ProductId or Product Description

18 posts, Page 1 of 2

1 2 »»

Search by ProductId or Product Description

Rate Topic

Display Mode

Topic Options

Author

Message

2Tall

Posted Friday, September 26, 2008 1:39 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:43 AM
Points: 619, Visits: 1,065

Hi. I would like users to be able to search by either ProductId or by the Product Description.

I have added two text boxes + 2 buttons to a web form (Text1 & Text2 + Search1 & Search2) + a GridView. The GridView source is a stored proc which leads me to my question.

If a user enters ProductId in Text1 then hits 'Search1' nothing is returned. If user enters Product Description in Text2 then hits Search2 nothing is returned. If values are entered both Text1 & Text2 then data is returned to the GridView so I am doing something wrong!

here is my stored proc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[sp_intranet_product_search]
	@ProductId NVARCHAR(100),
	@Description NVARCHAR(300)
AS
SELECT DISTINCT TOP 100 ProductId,ProductDescription,CRef,ISNULL(CONVERT(VARCHAR,SUM(QuantityOutstanding)),'0')AS Quantity,'£' + CONVERT (VARCHAR(12),StandardPrice,1)AS StandardPrice
FROM Products 
LEFT JOIN dbo.In ON dbo.In.Product= dbo.Products.Product
WHERE ([ProductId] LIKE '%' + @ProductId + '%' OR [ProductDescription] LIKE '%' + @Description + '%')
GROUP BY Products.ProductId, Products.ProductDescription,Products.CRef, Products.StandardPrice

So basically I want users to have the ability to search by either description or code (only needing to populate one text field) in order to return results to the GridView.

Many Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Post #576573

John H Marsh

Posted Friday, September 26, 2008 4:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 1,325, Visits: 1,391

Hello,

In theory your SP should work. It depends what is being passed in when no parameter value is supplied e.g. if it is a Null or a string of spaces then the Like will not work.

You could try using RTrim(IsNull(@ProductId, '')) in the Where clause instead. (Same for @Description too).

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Post #576668

2Tall

Posted Friday, September 26, 2008 5:46 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:43 AM
Points: 619, Visits: 1,065

Thanks John, I will give that a try.
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Post #576701

2Tall

Posted Friday, September 26, 2008 7:24 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:43 AM
Points: 619, Visits: 1,065

Hi John. I tried the following:

WHERE ([ProductId] LIKE '%' + RTrim(IsNull(@ProductId, '')) + '%' OR [CRef] LIKE '%' + RTrim(IsNull(@CRef, '')) + '%')

Same issue. I have to enter values in both boxes fro results to be returned to the grid?

Kind Regards,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Post #576815

John H Marsh

Posted Friday, September 26, 2008 7:43 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 1,325, Visits: 1,391

Hello Phil,

What data-types are the CRef column and @CRef variable?

My example would only work for character data-types e.g. it won’t work if zeros are being passed in.

Regards,

John

www.sql.lu
SQL Server Luxembourg User Group

Post #576841

2Tall

Posted Friday, September 26, 2008 9:25 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:43 AM
Points: 619, Visits: 1,065

Hi John. The data type is NVARCHAR (100). Maybe I will resort back to one search!! :)

Many Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Post #576952

2Tall

Posted Thursday, October 30, 2008 7:45 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:43 AM
Points: 619, Visits: 1,065

OK still trying to get this working. My stored proc is as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[bsp_intranet_product_search]
	@ProductId	NVARCHAR(100),
	@ProductDescription NVARCHAR(300)
AS
SELECT DISTINCT TOP 100 ProductId,ProductDescription,CrossReference,ISNULL(CONVERT(VARCHAR,SUM(QuantityOutstanding)),'0')AS Quantity,'£' + CONVERT (VARCHAR(12),StandardPrice,1)AS StandardPrice
FROM Products 
LEFT JOIN dbo.Inventory ON dbo.Inventory.Product= dbo.Products.Product
--WHERE ([ProductId] LIKE '%' + @ProductId + '%')
--WHERE ([ProductId] LIKE '%' + RTrim(IsNull(@ProductId, '')) + '%' OR [ProductDescription] LIKE '%' + RTrim(IsNull(@ProductDescription, '')) + '%')
WHERE ([ProductId] LIKE '%' + RTrim(IsNull(@ProductId, '')) OR [ProductDescription] LIKE '%' + RTrim(IsNull(@ProductDescription, '')))
GROUP BY Products.ProductId, Products.ProductDescription,Products.CrossReference, Products.StandardPrice

I want users to be able to search by ProductID or by Product Description.

If I enter say FAS as part of a ProductID but leave the Product Description field blank then the query returns nothing (same results for reverse, if I enter TOOL as part of a Product Description search string query returns blank). If I put a single space ' ' in the second search field the query runs and returns results i.e. ProductID FAS Product Description ' '

Attachments:
1.bmp shows the search fields
2.bmp show the parameters for the web page controls

Hope that makes sense.
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Post Attachments

2.bmp (5 views, 792.01 KB)

1.bmp (5 views, 182.30 KB)

Post #594307

Chris Harshman

Posted Thursday, October 30, 2008 8:33 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 1,573, Visits: 1,732

how about something like this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[bsp_intranet_product_search]
    @ProductId	NVARCHAR(100),
    @ProductDescription NVARCHAR(300)
AS
SELECT DISTINCT TOP 100 ProductId, ProductDescription, CrossReference,
    ISNULL(CONVERT(VARCHAR,SUM(QuantityOutstanding)),'0') AS Quantity,
    '£' + CONVERT (VARCHAR(12),StandardPrice,1) AS StandardPrice
  FROM Products
    LEFT OUTER JOIN dbo.Inventory ON dbo.Inventory.Product= dbo.Products.Product
  WHERE (IsNull(RTrim(@ProductId), '') = '' OR [ProductId] LIKE '%' + RTrim(@ProductId) + '%')
    AND (IsNull(RTrim(@ProductDescription), '') = '' OR [ProductDescription] LIKE '%' + RTrim(@ProductDescription) + '%')
  GROUP BY Products.ProductId, Products.ProductDescription, Products.CrossReference, Products.StandardPrice

Post #594381

2Tall

Posted Thursday, October 30, 2008 9:39 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:43 AM
Points: 619, Visits: 1,065

Hi Chris, I will give that a go. I thought it was going to be simple :)
Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Post #594473

2Tall

Posted Thursday, October 30, 2008 9:49 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:43 AM
Points: 619, Visits: 1,065

Hi Chris that is better than it was but I am unable to leave one of the fields blank. If I do the query does not return any values, however if I put a single space then the query executes OK.
Many Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Post #594482

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions