Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
|
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
 
Old May 21st, 2007, 10:51 AM
Authorized User
 
Join Date: May 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem sending values into SQL Server Proc

Hi all,
I am new to Visual Studio 2005 and am having a problem with calling a Sql Server Stored Procedure. For now, I have created a page with a search button, two text boxes that will hold my input values into the Stored proc, and a datagrid that will hold my output. When I try to send in just one of the text box values, I get nothing back. If I send in both, I get the correct return. I have tested this proc in sql server and it works fine with one or both values as input (using exec command). I have even changed the default value for the StreetName text box to be a valid street name (searching for street name and employee id from my database) to see what I get and I get the correct return data (as if I sent in both values). Why can't I just send in one value and get the return for it? My proc select statement is very simple:

ALTER PROCEDURE [Sam_Search_Pinewood]
    -- Add the parameters for the stored procedure here
@StreetName As varchar(30) = null,
@AssignedEIN As varchar(4) = null


AS
Begin

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

 Select CaseNumber
from fir_fieldinvreport
where (@StreetName is NULL or @StreetName = StreetName)
and (@AssignedEIN is NULL or @AssignedEIN = AssignedEIN)

end

Thanks!
 
Old May 21st, 2007, 02:15 PM
Registered User
 
Join Date: May 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Since the stored proc has two variables defined, it needs two passed. In the case of only one input box being entered, are you passing an empty string in the other missing variable? Try passing an empty string in teh case of a blank input box and changing your WHERE statement to the following:

WHERE (CASE WHEN @StreetName <> '' THEN @StreetName ELSE StreetName END = StreetName) AND (CASE WHEN @AssignedEIN <> '' THEN @AssignedEIN ELSE AssignedEIN END = AssignedEIN)

I hope this helps.


-Orcabelle
 
Old May 23rd, 2007, 01:19 PM
Authorized User
 
Join Date: May 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Orcabelle. Thanks for the advice. I tried that and it worked!!





Similar Threads
Thread Thread Starter Forum Replies Last Post
sending mail from sql server pady123 SQL Server 2005 0 October 17th, 2008 01:58 AM
sending NULLs into a SQL Server PROC lisabb ASP.NET 2.0 Basics 1 June 21st, 2007 03:36 PM
MS Access .mdb calling SQL server 2000 stored proc fazzou Access 6 September 8th, 2006 11:32 AM
Problem in sending arabic values in email using CD Basma Classic ASP Components 1 October 11th, 2004 05:34 PM
store proc use in report services of sql server shivani16 ADO.NET 3 June 22nd, 2004 11:17 AM





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