none
How do I get Custom Error Messages

    Question

  • I'm using a stored procedure and it checks several things before committing the record, I have each set to generate a different number if an error is raised. How can I get the error in my website and display a message based on the returned number?

    IE 0 Successful

    1 Already Exists

    2 Wrong Format

    Thanks for the help

    Thursday, July 25, 2013 1:37 AM

Answers

All replies

  • I believe this information should be available in the exception object some extra properties. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 25, 2013 2:25 AM
    Moderator
  • check this,

    http://www.c-sharpcorner.com/UploadFile/63f5c2/user-define-error-message-in-sql-server-20123/


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 25, 2013 2:48 AM
  • We can add an OUTPUT parameter in the stored procedure. Then the parameter will return error number. Finally catch error number in the website.

    Refer the below link,

    http://www.c-sharpcorner.com/UploadFile/rohatash/get-out-parameter-from-a-stored-procedure-in-Asp-Net/


    Regards, RSingh




    Thursday, July 25, 2013 3:47 AM
  • Each stored procedure returns an integer code. Make the last statement in your procedure

    RETURN theerrorcode;

    And evaluate that code in your application.


    Tom G.

    • Marked as answer by maddmike Saturday, July 27, 2013 1:29 AM
    Thursday, July 25, 2013 12:49 PM
  • I've been messing with this all day, I added the following into my cs and all I get is "Procedure or Function has too many arguments specified"

            cmd.Parameters.Add("@ReturnValue", SqlDbType.SmallInt);
            cmd.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
            lblError.Text = (string)cmd.Parameters["@ReturnValue"].Value;

    I verified that the return value is declared and is of type SmallInt, not sure what else it could be.
    If I comment out those 3 lines the procedure works but I need to get the data back.
    • Edited by maddmike Friday, July 26, 2013 3:46 AM Added Info
    Friday, July 26, 2013 3:42 AM
  • What is the definition of your SP? Are you using @ReturnValue as OUTPUT parameter for the stored procedure?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 26, 2013 3:39 PM
    Moderator
  • This was set up in 06 by a company we contracted with, the majority of the system is poorly written and hidden in dll files. As our needs have progressed I've been editing and recreating pages where I can. The stored procedure is as follows.

    ALTER PROCEDURE [dbo].[TestUpdate]
    (@LoadID	IVSID,
    @PalletCode	IVSShort)
    AS
    BEGIN TRANSACTION
    	Declare  @ReturnValue smallint,
    		@PalletID	IVSID,
    		@IsHold	IVSBool,
    		@Inspected	IVSBool
    
    	Set @ReturnValue = 0
    	Set @PalletID = -1
    	Set @IsHold = 0
    	
    	--Check pallet and run
    	Select 	@PalletID = PalletID
    	From 	IVS_T_Pallet 	
    	Where  	Code = @PalletCode
    	If(@PalletID<=0)
    	Begin	
    		Set @ReturnValue = -2 --pallet code not exist.
    		Goto ExitLable
    	End	
    
    	Select	@IsHold = Hold 
    	From	IVS_T_PalletOriginal
    	Where 	PalletID = @PalletID
    	If(@IsHold>0)
    	Begin
    		Set @ReturnValue = -3 --pallet is hold
    		Goto ExitLable
    	End	
    
    	Select	@Inspected = IsNull(Inspected , 0)
    	From	IVS_T_PalletDetail
    	Where 	PalletID = @PalletID
    	If(@Inspected<1)
    	Begin
    		Set @ReturnValue = -6 -- Pallet has not been inspected
    		Goto ExitLable
    	End	
    
    
    	If((Select    count(*)
    	    From 	   IVS_T_PalletDetail A, IVS_T_RunHeader B
                      Where   A.RunID = B.RunID And 
    			(A.Removed is null Or  A.Removed<=0) And
     		   B.ProductID not in (Select ProductID From IVS_T_LoadProduct
    				Where LoadID = @LoadID) And
    	  	   A.PalletID = @PalletID)>0)
    	Begin
    		Set @ReturnValue = -4 --Some products in this pallet don't exist in this load
    		Goto ExitLable
    	End	
    
    	create table #RequiredProductQty
    	(
    	        ProductID	numeric,
    	        Qty		 int)
    	create table #SumRequiredProductQty
    	(
    	        ProductID	numeric,
    	        Qty		 int)
    	Insert into #RequiredProductQty(Qty, ProductID)
    	Select  Sum(IsNull(A.CaseQty, 0)), C.ProductID
    	From 	IVS_T_PalletDetail A, IVS_T_RunHeader B, IVS_T_Products C
    	Where 	A.RunID = B.RunID And 	
    		A.LoadID = @LoadID And
    		B.ProductID = C.ProductID And
    		(Removed is null or  A.Removed   <=0)
    	Group By B.ProductID, C.ProductID
    	
    	Insert into #RequiredProductQty(Qty, ProductID)
    	Select  Sum(IsNull(A.CaseQty, 0)), C.ProductID
    	From 	IVS_T_PalletDetail A, IVS_T_RunHeader B, IVS_T_Products C
    	Where 	A.RunID = B.RunID And 	
    		A.PalletID = @PalletID And
    		B.ProductID = C.ProductID And
    		(Removed is null or  A.Removed   <=0) And
    		(A.LoadID is null or A.LoadID<=0)
    	Group By B.ProductID, C.ProductID
    
    	Insert into #SumRequiredProductQty(ProductID, Qty)
    	Select ProductID, sum(Qty)
    	From #RequiredProductQty
    	Group By ProductID
    
    	If((Select	count(*) 
    	   From #SumRequiredProductQty A
    	   Where A.Qty>(Select Sum(Qty) From IVS_T_LoadProduct 
    			Where LoadID = @LoadID And
    				ProductID = A.ProductID
    			Group By ProductID))>0)
    	Begin
    		Set @ReturnValue = -5 --Allocated qty > Order quantity
    		Goto ExitLable	
    	End
    
    	-- End of check
    	Update	IVS_T_PalletDetail
    	Set 	LoadID = @LoadID
    	Where  	PalletID = @PalletID
    	IF (@@ERROR<>0)
    		GOTO HANDLEERR
    ExitLable:
    	Select @ReturnValue as ErrorCode
    COMMIT TRAN
    RETURN(0)
    HANDLEERR:
    	Set @ReturnValue =-1 -- has errors
    	ROLLBACK TRANSACTION
    	RAISERROR (15002,-1,-1,'IVS_SP_UPD_LOAD_ADD_PALLET')
    	RETURN(1)
    

    I understand most everything in their tsql but cannot figure out the error handling, When I execute this in Management Studio its output is as follows, correct or not the returnvalue is always 0.

    Friday, July 26, 2013 10:47 PM
  • Are you able to fix this stored procedure? Its code is so old style. In any case, you're not returning this return value as Return or use it as output parameter in this SP right now. The quick simplest way for now would be to define @ReturnValue as OUTPUT parameter in this procedure. Then your .NET code will be able to access it.

    The best way is to re-write this procedure using TRY-CATCH error handling instead.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Marked as answer by maddmike Saturday, July 27, 2013 1:29 AM
    • Edited by Naomi NModerator Sunday, July 28, 2013 3:39 AM Typo fix
    Saturday, July 27, 2013 12:43 AM
    Moderator
  • I finally got it working by changing RETURN(0) to RETURN @ReturnValue, Now I am getting the corresponding numbers. Thanks for all the help.
    Saturday, July 27, 2013 1:28 AM