Click here to monitor SSC
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
Right there with Babe
Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)

Group: General Forum Members
Points: 796 Visits: 1203
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1455
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
Right there with Babe
Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)

Group: General Forum Members
Points: 796 Visits: 1203
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
Right there with Babe
Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)

Group: General Forum Members
Points: 796 Visits: 1203
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1455
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
Right there with Babe
Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)

Group: General Forum Members
Points: 796 Visits: 1203
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
Right there with Babe
Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)

Group: General Forum Members
Points: 796 Visits: 1203
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 (11 views, 792.00 KB)
1.bmp (11 views, 182.00 KB)
Chris Harshman
Chris Harshman
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3292 Visits: 3550
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
Right there with Babe
Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)

Group: General Forum Members
Points: 796 Visits: 1203
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
Right there with Babe
Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)

Group: General Forum Members
Points: 796 Visits: 1203
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