View Single Post
  #1 (permalink)  
Old March 17th, 2006, 08:25 AM
kshort kshort is offline
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