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 params...
<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>