September 26, 2008 at 1:39 am
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
September 26, 2008 at 4:54 am
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
September 26, 2008 at 5:46 am
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
September 26, 2008 at 7:24 am
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
September 26, 2008 at 7:43 am
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
September 26, 2008 at 9:25 am
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
October 30, 2008 at 7:45 am
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]
@ProductIdNVARCHAR(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
October 30, 2008 at 8:33 am
how about something like this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[bsp_intranet_product_search]
@ProductIdNVARCHAR(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
October 30, 2008 at 9:39 am
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
October 30, 2008 at 9:49 am
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
October 30, 2008 at 9:59 am
is it possible for you to use SQL Profiler? It can capture the actual values of the parameters that are getting passed to your stored procedure in an EXEC call from the application. That might help solve this riddle, because a number of these solutions should have given you better results than what you're seeing.
October 30, 2008 at 10:03 am
OK, I will give that a go and report back.
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
October 30, 2008 at 10:21 am
Chris here are a few results:
exec bsp_intranet_productdesc_search @ProductId=N'FAL',@ProductDescription=N' '
exec bsp_intranet_productdesc_search @ProductId=N'FAS',@ProductDescription=N' '
exec bsp_intranet_productdesc_search @ProductId=N' ',@ProductDescription=N'TRANSMITTER'
exec bsp_intranet_productdesc_search @ProductId=N'FAS175C',@ProductDescription=N' '
If I enter a ProductID but leave the Product Description blank (i.e. no space) then the profiler does not record anything? This is true if I enter a Product Description but leave the ProductID blank.
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
October 30, 2008 at 10:49 am
Hello Phil,
If nothing is being recorded by Profiler, then it sounds like the issue may be in the Application rather than in the SP.
Are you building a SQL string in the application’s code? Can you post the relevant code of the Web form?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
October 30, 2008 at 11:02 am
Hi John / Chris. It appears it was a setting on MS Visual Web Developer 2008. On the grid view when configuring the parameters there is an option to display advanced properties. I changed 'ConvertEmptyStringToNull' from True to False. This now allows me to have a blank value in either form field. The downside is when the page launches is displays the first 100 records!
Any thoughts on that one?
I really appreciate your efforts on this one.
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply