SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search by ProductId or Product Description


Search by ProductId or Product Description

Author
Message
2Tall
2Tall
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5370 Visits: 1218
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

John H Marsh
John H Marsh
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4983 Visits: 1470
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
2Tall
2Tall
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5370 Visits: 1218
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

2Tall
2Tall
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5370 Visits: 1218
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

John H Marsh
John H Marsh
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4983 Visits: 1470
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
2Tall
2Tall
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5370 Visits: 1218
Hi John. The data type is NVARCHAR (100). Maybe I will resort back to one search!! Smile

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

2Tall
2Tall
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5370 Visits: 1218
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

Attachments
2.bmp (21 views, 792.00 KB)
1.bmp (19 views, 182.00 KB)
Chris Harshman
Chris Harshman
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16314 Visits: 5174
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


2Tall
2Tall
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5370 Visits: 1218
Hi Chris, I will give that a go. I thought it was going to be simple Smile
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

2Tall
2Tall
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5370 Visits: 1218
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search