Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 26th, 2006, 12:39 PM
Authorized User
Points: 239, Level: 4
Points: 239, Level: 4 Points: 239, Level: 4 Points: 239, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default Store Procedure Problem

Hello and thank you for taking a moment to read this message. Please forgive me if this question is a bit out of scope. I am writng an application that queries a database. The user can do a search on any of four fields(Order Number, Product Name,ShippedDate, or Ship Name). If the user leaves all fields blank and hits a submit button, I would like the application to return all records from the database. If the user enters appropriate search criteria, I would like the application to return only those records that meet the criteria searched. I am passing a blank string in my aspx code for those fields left blank by the user. (Ex.

If txtOrderNumber.Text Is DBNull.Value

 Then txtOrderNumber.Text = " "

End If

\\* later on in the code assign it to the parameter

Dim objCommand As New Data.SqlClient.SqlCommand("usp_Find_Orders", objConn)

objCommand.CommandType = Data.CommandType.StoredProcedure


objCommand.Parameters.AddWithValue("@OrderID", txtOrderNumber.Text)

)

My stored procedure is where it becomes confusing. I have seen code that tests for a blank string and sets the parameter equal to '%%' . I think this is what I want to do here but am not 100% sure. I believe my problem is mostly that I don't know how my WHERE clause should look. If anybody has suggestions, I would be most appreciative. Stored Procedure is below. Thank You- Jason

CREATE PROCEDURE [dbo].[usp_Find_Orders]
(


@OrderID varchar(50),
@ProductName varchar (100),
@ShippedDate varchar (100),
@ShipName varchar (100)
)

AS
 If @OrderID=" "
     SET @OrderID="%%"

If @ProductName=" "
     SET @ProductName="%%"

If @ShippedDate=" "
     SET @ShippedDate="%%"

If @ShipName=" "
     SET @ShipName="%%"

SELECT [Order Details].Quantity, Orders.OrderID, Orders.OrderDate, Products.ProductName, Orders.ShippedDate, Orders.ShipVia, Orders.ShipName,
                      Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode
FROM [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
                      Products ON [Order Details].ProductID = Products.ProductID

Where
Orders.OrderID Like @OrderID
AND
ProductName Like '%' + @ProductName + '%'
AND
ShippedDate Like '%' + @ShippedDate +'%'
AND
ShipName Like '%'+ @ShipName +'%'
GO



  #2 (permalink)  
Old March 27th, 2006, 01:43 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

First "s should be 's
Second, any date columns should be datatime

Ex:
CREATE PROCEDURE [dbo].[usp_Find_Orders]
(


@OrderID varchar(50),
@ProductName varchar (100),
@ShippedDate varchar (100),
@ShipName varchar (100)
)

AS

declare @OrderID_flg int
declare @ProductName_flg int
declare @ShippedDate_flg int
declare @ShipName_flg int


SET @OrderID_flg = 1
SET @ProductName_flg = 1
SET @ShippedDate_flg = 1
SET @ShipName_flg = 1



 If @OrderID=''
     SET @OrderID_flg= 0

If @ProductName=''
     SET @ProductName_flg= 0

If @ShippedDate=''
     SET @ShippedDate_flg= 0

If @ShipName=''
     SET @ShipName_flg= 0

SELECT [Order Details].Quantity, Orders.OrderID, Orders.OrderDate, Products.ProductName, Orders.ShippedDate, Orders.ShipVia, Orders.ShipName,
                      Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode
FROM [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
                      Products ON [Order Details].ProductID = Products.ProductID

Where
   ( ( @OrderID_flg = 1 AND Orders.OrderID = @OrderID ) OR ( @OrderID_flg = 0 ) ) AND
   ...Repeat for each variable


GO

Jim

  #3 (permalink)  
Old March 27th, 2006, 10:08 PM
Authorized User
Points: 239, Level: 4
Points: 239, Level: 4 Points: 239, Level: 4 Points: 239, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much. That helped a lot.- Jason

  #4 (permalink)  
Old March 28th, 2006, 12:41 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Great.. glad to help...
:)

Jim



Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with clause Top in store procedure Javierera SQL Server 2005 3 October 24th, 2008 10:17 AM
Store procedure help... RinoDM SQL Server 2000 7 August 11th, 2008 07:09 PM
Store procedure help ??? RinoDM SQL Server 2000 8 May 1st, 2008 03:03 PM
store procedure if else problem krshekhar SQL Language 0 February 20th, 2008 05:31 AM
Store Procedure sureshyuga SQL Server 2000 0 May 18th, 2007 01:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.