code_junkie

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

Nov 13th, 2011
138
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.22 KB | None | 0 0
  1. USE [EBDB]
  2. GO
  3. /****** Object: StoredProcedure [dbo].[delete_treatment_category] Script Date: 01/02/2009 15:18:12 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. /*
  9. RETURNS 0 FOR SUCESS
  10. 1 FOR NO DELETE AS HAS ITEMS
  11. 2 FOR DELETE ERROR
  12. */
  13.  
  14. ALTER PROCEDURE [dbo].[delete_treatment_category]
  15. (
  16. @id INT
  17. )
  18. AS
  19. SET NOCOUNT ON
  20.  
  21. IF EXISTS(
  22. SELECT id
  23. FROM dbo.treatment_item
  24. WHERE category_id = @id
  25. )
  26. BEGIN
  27. RETURN 1
  28. END
  29. ELSE
  30. BEGIN
  31. BEGIN TRY
  32. DELETE FROM dbo.treatment_category
  33. WHERE id = @id
  34. END TRY
  35.  
  36. BEGIN CATCH
  37. RETURN 2
  38. END CATCH
  39.  
  40. RETURN 0
  41. END
  42.  
  43. Protected Sub dsTreatmentCats_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles dsTreatmentCats.Deleted
  44. Select Case CInt(e.Command.Parameters(0).Value)
  45. Case 0
  46. 'it worked so no action
  47. lblError.Visible = False
  48. Case 1
  49. lblError.Text = "Unable to delete this category because it still has treatments associated with it."
  50. lblError.Visible = True
  51. Case 2
  52. lblError.Text = "Unable to delete this category due to an unexpected error. Please try again later."
  53. lblError.Visible = True
  54. End Select
  55. End Sub
  56.  
  57. <asp:SqlDataSource ID="dsTreatmentCats" runat="server"
  58. ConnectionString="<%$ ConnectionStrings:EBDB %>"
  59. DeleteCommand="delete_treatment_category" DeleteCommandType="StoredProcedure"
  60. InsertCommand="add_treatment_category" InsertCommandType="StoredProcedure"
  61. SelectCommand="get_treatment_categories" SelectCommandType="StoredProcedure"
  62. UpdateCommand="update_treatment_category"
  63. UpdateCommandType="StoredProcedure" ProviderName="System.Data.SqlClient">
  64. <DeleteParameters>
  65. <asp:Parameter Direction="ReturnValue" Name="RetVal" Type="Int32" />
  66. <asp:Parameter Name="id" Type="Int32" />
  67. </DeleteParameters>
  68. <UpdateParameters>
  69. <asp:Parameter Direction="ReturnValue" Name="RetVal" Type="Int32" />
  70. <asp:Parameter Name="id" Type="Int32" />
  71. <asp:Parameter Name="name" Type="String" />
  72. <asp:Parameter Name="additional_info" Type="String" />
  73. </UpdateParameters>
  74. <InsertParameters>
  75. <asp:Parameter Direction="ReturnValue" Name="RetVal" Type="Int32" />
  76. <asp:ControlParameter ControlID="txtCat" Name="name" PropertyName="Text"
  77. Type="String" />
  78. <asp:ControlParameter ControlID="txtAddInfo" Name="additional_info"
  79. PropertyName="Text" Type="String" />
  80. </InsertParameters>
  81. </asp:SqlDataSource>
  82.  
  83. SqlConnection conn = new SqlConnection(myConnectionString);
  84. SqlCommand cmd = new SqlCommand(mySqlCommand, conn);
  85.  
  86. // Set up your command and parameters
  87.  
  88. cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
  89.  
  90. SqlDataReader reader = cmd.ExecuteReader();
  91. while (reader.Read())
  92. {
  93. // Read your data
  94. }
  95.  
  96. int resultCount = (int)cmd.Parameters["@Return"].Value;
  97. conn.Close();
  98. return resultCount;
  99.  
  100. SqlConnection conn = new SqlConnection(myConnectionString);
  101. SqlCommand cmd = new SqlCommand(mySqlCommand, conn);
  102.  
  103. // Set up your command and parameters
  104.  
  105. cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
  106.  
  107. SqlDataReader reader = cmd.ExecuteReader();
  108. while (reader.Read())
  109. {
  110. // Read your data
  111. }
  112.  
  113. conn.Close();
  114. int resultCount = (int)cmd.Parameters["@Return"].Value;
  115. return resultCount;
  116.  
  117. DECLARE @rtn int;
  118. EXEC @rtn = dbo.delete_treatment_category /*insert valid id here > 2*/;
  119. SELECT @rtn;
  120.  
  121. cmd.CommandType = CommandType.StoredProcedure 'cmd is SqlCommand
  122.  
  123. Dim retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
  124. retValParam.Direction = ParameterDirection.ReturnValue
  125. cmd.Parameters.Add(retValParam)
  126.  
  127. 'add the ID parameter here
  128.  
  129. 'execute
  130.  
  131. 'look at the @RETURN_VALUE parameter here
Add Comment
Please, Sign In to add comment