Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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
- <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>
- 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;
- 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;
- DECLARE @rtn int;
- EXEC @rtn = dbo.delete_treatment_category /*insert valid id here > 2*/;
- SELECT @rtn;
- 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
Add Comment
Please, Sign In to add comment