March 17, 2009 at 7:18 am
Hi. Using Visual Web developer I have designed a Web Page to return product information.
I have written a stored procedure that returns values to a grid on a web page based on the serach criteria (Product ID).
Code for stored procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[sp_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
However the end users are used to the CrossReference Product Code.
When a user enters a value in the Product Search I want the stored procedure to return values for either the ProductID or CrossReference fields if there is a match.
I amended the code as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[sp_product_search]
@ProductId NVARCHAR(100),
@CrossReference 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(@CrossReference), '') = '' OR [CrossReference] LIKE '%' + RTrim(CrossReference) + '%')
AND (IsNull(RTrim(@ProductDescription), '') = '' OR [ProductDescription] LIKE '%' + RTrim(@ProductDescription) + '%')
GROUP BY Products.ProductId, Products.ProductDescription, Products.CrossReference, Products.StandardPrice
On the web form I linked the Parameter to the control. However it does not function as required.
Previously when the page loaded the top 100 records displayed (bit of a pain but the only way I could get it to work). The user could serach bu either ProductId or ProductDescription. However now the form only returns data for the ProductID.
I have attached a couple of screen dumps that may help.
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
March 17, 2009 at 7:30 am
I think this is what you want:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[sp_product_search]
@ProductId NVARCHAR(100),
@CrossReference 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) + '%'
) OR
(
IsNull(RTrim(@CrossReference), '') = '' OR
[CrossReference] LIKE '%' + RTrim(@CrossReference) + '%'
)
)AND
(
IsNull(RTrim(@ProductDescription), '') = '' OR
[ProductDescription] LIKE '%' + RTrim(@ProductDescription) + '%'
)
GROUP BY
Products.ProductId,
Products.ProductDescription,
Products.CrossReference,
Products.StandardPrice
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 17, 2009 at 8:40 am
Hello once again Jack. That did not work, I get the feeling Visual Web Developer is not the best tool to use! My main issue is not being a coder I am using the inbuilt controls that give me erm.......less control 🙂
Using your code when the page launches the grid is blank. If I enter a value into the ProductID control then hit search then data is returned to the grid. If I enter a value into the Description Control then hit search the grid remains 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply