The return value from SQL 2005 SP returns DBNULL - Where am I going wrong?
This is 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 am trying to get the return value using the below code (sqlDataSource and 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 CInt string (e.Command.Parameters (0) .Value) returns DBNull instead of the return value, but only on deletion - this approach works great with both updates and inserts.
Hopefully I'm just a little tight and missing something obvious - any ideas?
Edit
I still have this problem and tried all the options below but didn't figure it out - am I surprised anyone else 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>
source to share
I am a little late to the game here, but for the sake of people stumbling across this question ...
If you are using ExecuteReader in ADO.Net, the return value will not be populated until you close the Reader or the underlying database connection. ( See here )
It won't 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;
It will be:
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;
source to share
Run this in 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 "id> 2" because you might 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
source to share
You are not showing the code where you add parameters and execute the command. Both can be critical.
I know of one way to reproduce this: if your procedure also returns rows (e.g. from a DELETE trigger) and you haven't consumed those rows ... basically, the out / return parameter values follow the grids into the TDS stream, so if you haven't already read the grids (when using ExecuteReader) - then you won't be able to get the updated parameters / return value. But if you do ExecuteNonQuery
, it shouldn't be a factor.
source to share