Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Search by ProductId or Product Description Expand / Collapse
Author
Message
Posted Friday, September 26, 2008 1:39 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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
Posted Friday, September 26, 2008 4:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
Posted Friday, September 26, 2008 5:46 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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
Posted Friday, September 26, 2008 7:24 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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
Posted Friday, September 26, 2008 7:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
Posted Friday, September 26, 2008 9:25 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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
Posted Thursday, October 30, 2008 7:45 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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
Posted Thursday, October 30, 2008 8:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:09 AM
Points: 1,907, Visits: 2,058
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
Posted Thursday, October 30, 2008 9:39 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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
Posted Thursday, October 30, 2008 9:49 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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 »

Add to briefcase 12»»

Permissions Expand / Collapse