Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 17th, 2006, 07:25 AM
Registered User
 
Join Date: Mar 2006
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored Procedure return value is NULL

Hi,

I'm a newbie who's is getting rather confused with stored procedures.

On my web form I have a gridview which gets its content from an sqldatasource which uses stored procedures to do select/insert/update/delete

All of the stored procedures work - the gridview is filled when I load the page, update/insert/delete also work.

The problem I'm having is that I cannot get any return or output parameters back from the stored procedure - they come back as nulls.
For example if I try to delete a record, and for whatever reason the procedure fails I want to get the error code back into .net so I can hopefully give the user an understandable reason why it didn't work (e.g. that category can't be deleted because it has sub-categories & they must be deleted first or whatever). I'm trying to get hold of the return value in SqlDataSource1_Deleted - but it is null


Code is below. If someone could point out (the probably blatantly obvious) thing I'm doing wrong I'd be most grateful.


Stored Procedure:
CREATE PROCEDURE [dbo].[usp_deleteLearningArea]
(
@lID int,
)
AS
DELETE FROM [learningAreas] WHERE [learningAreaID] = @lID
SET @ERR = @@ERROR
RETURN @ERR
GO


SqlDataSource
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LSCConnectionString %>"
        DeleteCommand="usp_deleteLearningArea" DeleteCommandType="StoredProcedure" InsertCommand="usp_insertLearningArea"
        InsertCommandType="StoredProcedure" SelectCommand="usp_getLearningAreas" SelectCommandType="StoredProcedure"
        UpdateCommand="usp_updateLearningArea" UpdateCommandType="StoredProcedure" OnDeleted="SqlDataSource1_Deleted" OldValuesParameterFormatString="old_{0}">
        <DeleteParameters>
            <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
            <asp:Parameter Name="lID" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="lName" Type="String" />
            <asp:Parameter Name="lID" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="lName" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>

SqlDataSource_Deleted Code
protected void SqlDataSource1_Deleted(object sender, SqlDataSourceStatusEventArgs e)
    {
        string test = "";
        foreach (SqlParameter param in e.Command.Parameters)
            test = test + param.ParameterName + "_" + param.Value.ToString() + " ";

    }

The string I get from that is: "@RETURN_VALUE_ @lID_ @old_lID_25"
So the only parameter that has a value is the original @lID which went into the Stored Procedure.
Reply With Quote
  #2 (permalink)  
Old March 17th, 2006, 11:56 PM
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

In your SP, you need to create an output variable for @ERR.

Reply With Quote
  #3 (permalink)  
Old March 20th, 2006, 12:55 PM
Registered User
 
Join Date: Mar 2006
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Doh!

Sorry, that was a copy & paste mistake. I've had @ERR declared as an output var after the @lID, and using DECLARE @ERR INT in the sp itself.

Neither works.

Oddly this is only a problem for the DELETE stored procedure. The select/update/insert all work fine & return @ERR. The delete sp doesn't. Depending on whether I have @ERR declared in the stored procedure and/or in the delete parameters for the datasource

For example the following sp work fine & return the @ERR. I don't understand why the delete one doesn't. :(


CREATE PROCEDURE [dbo].[usp_getLearningAreas]
AS
DECLARE @ERR INT
SELECT learningAreaName as lName, learningAreaID as lID FROM learningAreas ORDER BY learningAreaName
SET @ERR=@@ERROR
RETURN @ERR
GO

CREATE PROCEDURE [dbo].[usp_insertLearningArea]
(
@lName nvarchar(50)
)
AS
DECLARE @ERR INT
INSERT INTO [learningAreas] ([learningAreaName]) VALUES (@lName)
SET @ERR = @@ERROR
RETURN @ERR
GO


At the moment the delete procedure is merely:

CREATE PROCEDURE [dbo].[usp_deleteLearningArea]
(
@lID int
)
AS
DECLARE @ERR INT
SET @ERR= @lID
RETURN @ERR
GO


I'm getting the "Procedure or function ... has too many arguments specified". But only for the delete sp. What's different about that one?

Reply With Quote
  #4 (permalink)  
Old March 20th, 2006, 03:15 PM
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

I don't know how you are returing the values with out an OUTPUT parameter.. Try:
alter PROCEDURE [dbo].[usp_deleteLearningArea]
(
@lID int,
@ERR int OUTPUT
)
AS

SET @ERR= @lID
RETURN @ERR
GO


Reply With Quote
  #5 (permalink)  
Old March 24th, 2006, 10:34 AM
Registered User
 
Join Date: Mar 2006
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I was returning it by the return value of the stored procedure, which can be be got hold of by using the RETURN_VALUE parameter available through the sqldatasource. It didn't matter which way I did it, that way, or by declaring an output parameter as you suggest, for the delete (and ONLY the delete) it didn't work.


I've discovered what the problem was, but I'm still rather confused as to why it's happening.

The reason I'm getting a null value for the return parameter is that somewhere along the line @ERR is being reset to a STRING INPUT parameter, despite the fact that I'm creating it as an INT32 OUTPUT parameter. It's an int output in the source code, or if I look at the delete query parameters it's definitely an int32 output there.

        <DeleteParameters>
            <asp:Parameter Name="yID" Type="Int32" />
            <asp:Parameter Direction="Output" Name="ERR" Type="Int32" />
        </DeleteParameters

But when I look at the e.command.parameters in the datasource deleting or deleted event, @ERR is a string input parameter, and its value is null.

I can reset @ERR back to being an int32 output parameter in the deleting event:

            SqlParameter outParam = (SqlParameter)e.Command.Parameters["@ERR"];
            outParam.SqlDbType = SqlDbType.Int;
            outParam.Direction = ParameterDirection.Output;
            e.Command.Parameters["@ERR"] = outParam;

Does anyone know why this is happening? The select/update/insert don't do this – @ERR stays an int output parameter with them - only the delete. The update & delete event are fired from the gridview, the insert from a button & textbox. Everything was just dragged onto the page from the toolbox and (as far as I know) I haven't set anything unusual. This is happening on every page with a sqldatasource & gridview (8 so far).

Any suggestions?





Reply With Quote
  #6 (permalink)  
Old March 30th, 2006, 11:17 PM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i'm having a similar problem, only with the SqlDataSource_Inserted method. my return value seems to be null as well. did you ever figure out what was going on with yours?

Reply With Quote
  #7 (permalink)  
Old March 31st, 2006, 04:00 AM
Registered User
 
Join Date: Mar 2006
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The problem I had was that when the code was run Visual Studio decided to change my INT OUTPUT parameter into a STRING INPUT.

I have no idea why it did that, as it was declared as <asp:Parameter Direction="Output" Name="ERR" Type="Int32" /> in the source, but when I got to the deleting/deleted events the e.command.parameters had it as a STRING INPUT. I'm still hoping someone has some suggestions for why that was happening.

The way I fixed it was to get hold of the parameter in the deleting(or inserting in your case) event for the datasource and set the parameter type & direction via code.

            SqlParameter outParam = (SqlParameter)e.Command.Parameters["@ERR"];
            outParam.SqlDbType = SqlDbType.Int;
            outParam.Direction = ParameterDirection.Output;
            e.Command.Parameters["@ERR"] = outParam;

Reply With Quote
  #8 (permalink)  
Old April 26th, 2006, 08:43 PM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi kshort...i had the same problem..perhaps you need to configure your dataset component's update command and refresh its parameters..

Reply With Quote
  #9 (permalink)  
Old April 26th, 2006, 08:46 PM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi kshort..i think you need to configure your dataset object's update/delete/insert command property..just simply refresh their parameters...hope that solves the problem...:)

Reply With Quote
  #10 (permalink)  
Old September 1st, 2006, 08:49 AM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK, this problem is something that took me a long time to figure out, and that's with Microsoft's support line. Luckily, this became classified as "a known issue" so I wasn't charged for anything. There is no complete description as to why it occurs, but it seems to occur when there is an issue passing items to your stored procedure, or an error occurs in your stored procedure. There is an easy fix, but it may not be what you wanted to use.

Use an OUTPUT parameter instead of a return value. I know, I hate doing it too, but it removes the issue. Like I said, the return parameters for sqlDataSources are bugged, so use an output parameter.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
[Resolved] Get return value from stored procedure snufse ASP.NET 2.0 Basics 1 August 8th, 2008 11:05 AM
Stored Procedure return value dmiles C# 3 December 6th, 2007 04:20 AM
Can i return a recordset in a Stored procedure chiefouko VB Databases Basics 6 December 12th, 2006 02:34 AM
Stored Procedure Insert Null Value harpua SQL Server ASP 2 March 5th, 2005 09:19 AM
how i use the Stored Procedure return value and ho yoord ADO.NET 5 June 28th, 2004 08:39 AM



All times are GMT -4. The time now is 09:33 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.