Return value from SQL 2005 SP returns DBNULL - Where am I going wrong?

Question

This is the SP...

USE [EBDB]
GO
/****** Object:  StoredProcedure [dbo].[delete_treatment_category]    Script Date: 01/02/2009 15:18:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURNS 0 FOR SUCESS
        1 FOR NO DELETE AS HAS ITEMS
        2 FOR DELETE ERROR
*/

ALTER PROCEDURE [dbo].[delete_treatment_category]
(
    @id INT
)
AS
    SET NOCOUNT ON

    IF EXISTS
    (
        SELECT id
        FROM dbo.treatment_item
        WHERE category_id = @id
    )
    BEGIN
        RETURN 1
    END 
    ELSE
    BEGIN
        BEGIN TRY
            DELETE FROM dbo.treatment_category
            WHERE id = @id
        END TRY

        BEGIN CATCH
            RETURN 2
        END CATCH 

        RETURN 0                        
    END

And I'm trying to get the return value using the below code (sqlDataSource & Gridview combo in VB .NET

Protected Sub dsTreatmentCats_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles dsTreatmentCats.Deleted
    Select Case CInt(e.Command.Parameters(0).Value)
    Case 0
        'it worked so no action
        lblError.Visible = False
    Case 1
        lblError.Text = "Unable to delete this category because it still has treatments associated with it."
        lblError.Visible = True
    Case 2
        lblError.Text = "Unable to delete this category due to an unexpected error. Please try again later."
        lblError.Visible = True
End Select
End Sub

The problem is that the line CInt(e.Command.Parameters(0).Value) returns a DBNull instead of the return value but only on deletes - this approach works fine with both updates and inserts.

Hopefully I'm just being a bit dense and have missed something obvious - any ideas?

Edit

I'm still having this problem and have tried all of the options below to no avail - I'm surprised no one else has had this problem?

Code for adding parameters:

<asp:SqlDataSource ID="dsTreatmentCats" runat="server" 
        ConnectionString="<%$ ConnectionStrings:EBDB %>" 
        DeleteCommand="delete_treatment_category" DeleteCommandType="StoredProcedure" 
        InsertCommand="add_treatment_category" InsertCommandType="StoredProcedure" 
        SelectCommand="get_treatment_categories" SelectCommandType="StoredProcedure" 
        UpdateCommand="update_treatment_category" 
        UpdateCommandType="StoredProcedure" ProviderName="System.Data.SqlClient">

    <DeleteParameters>
        <asp:Parameter Direction="ReturnValue" Name="RetVal" Type="Int32" />
        <asp:Parameter Name="id" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Direction="ReturnValue" Name="RetVal" Type="Int32" />
        <asp:Parameter Name="id" Type="Int32" />
        <asp:Parameter Name="name" Type="String" />
        <asp:Parameter Name="additional_info" Type="String" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Direction="ReturnValue" Name="RetVal" Type="Int32" />
        <asp:ControlParameter ControlID="txtCat" Name="name" PropertyName="Text" 
            Type="String" />
        <asp:ControlParameter ControlID="txtAddInfo" Name="additional_info" 
            PropertyName="Text" Type="String" />
    </InsertParameters>
</asp:SqlDataSource>

Answer 1

I'm a little late to the game here, but for the sake of people who stumble upon this question...

If you're using ExecuteReader in ADO.Net, the return value will not be populated until you close either the Reader or the underlying connection to the database. (See here)

This will not work:

SqlConnection conn = new SqlConnection(myConnectionString);
 SqlCommand cmd = new SqlCommand(mySqlCommand, conn);

 //     Set up your command and parameters

 cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

 SqlDataReader reader = cmd.ExecuteReader();
 while (reader.Read())
 {
       //     Read your data
 }

 int resultCount = (int)cmd.Parameters["@Return"].Value;
 conn.Close();
 return resultCount;

This will:

SqlConnection conn = new SqlConnection(myConnectionString);
 SqlCommand cmd = new SqlCommand(mySqlCommand, conn);

 //     Set up your command and parameters

 cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

 SqlDataReader reader = cmd.ExecuteReader();
 while (reader.Read())
 {
       //     Read your data
 }

 conn.Close();
 int resultCount = (int)cmd.Parameters["@Return"].Value;
 return resultCount;

Answer 2

Why do you use Name="RETURN_VALUE" for the Delete parameter but Name="RetVal" for Update and Insert? If the latter two work, that is the first place I'd look.

Answer 3

You don't show the code where you are adding the parameters and executing the command. Both may be critical.

I know one way of reproducing this - if your procedure also returns rows (for example, from a DELETE trigger), and you haven't consumed those rows... basically, the out/return parameter values follow the grids in the TDS stream, so if you haven't read the grids yet (when using ExecuteReader) - then you can't get the updated parameters / return value. But if you are using ExecuteNonQuery this shouldn't be a factor.

Answer 4

Run this in the SQL tools to ensure that the stored proc behaves as expected.

DECLARE @rtn int;
EXEC @rtn = dbo.delete_treatment_category /*insert valid id here > 2*/;
SELECT @rtn;

I mention "an id > 2" because you may be reading the wrong parameter. That is, this stored proc has 2 parameters... one for the id and the other for the return value.

IIRC:

cmd.CommandType = CommandType.StoredProcedure 'cmd is SqlCommand

Dim retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
retValParam.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(retValParam)

'add the ID parameter here

'execute

'look at the @RETURN_VALUE parameter here

Answer 5

Yep I did - I'm using the sqlDataSource control which sniffed out the params for me including the return value with the correct direction set. Just for fun I did also create the param from scratch with return val direction too but no joy :(

Answer 6

When you added the Parameter, did you set the Direction to ReturnValue?