p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 2.0 Basics (http://p2p.wrox.com/forumdisplay.php?f=136)
-   -   Stored Procedure return value is NULL (http://p2p.wrox.com/showthread.php?t=39970)

kshort March 17th, 2006 08:25 AM

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.

jbenson001 March 18th, 2006 12:56 AM

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


kshort March 20th, 2006 01:55 PM

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?


jbenson001 March 20th, 2006 04:15 PM

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



kshort March 24th, 2006 11:34 AM

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?






elblanke March 31st, 2006 12:17 AM

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?


kshort March 31st, 2006 05:00 AM

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;


eugim April 26th, 2006 08:43 PM

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


eugim April 26th, 2006 08:46 PM

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...:)


Tarcash September 1st, 2006 08:49 AM

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.


All times are GMT -4. The time now is 09:38 PM.

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