Sql, SqlDataAdapter.Fill(); will not catch error raised in Sproc

EE,

It passes sql errors other than "13000 through 2147483647"

Here are the Sprocs.

Regards
Sam

ALTER PROCEDURE [dbo].[SQLUTL_Table_Sel]

		@Filter nvarchar(50)
		--, @cause_error int
		
AS
BEGIN
	LINENO 20
	SET XACT_ABORT, NOCOUNT ON;

	--DECLARE 
	
    BEGIN TRY
		
		SELECT *
		FROM Contacts
		WHERE FName = @Filter
		
		IF @@ROWCOUNT <= 0 -- IS NULL	
		BEGIN							
				RAISERROR (60000, -1,-1, 'ContactID')
		END				
		
		--SELECT @ContactID
		
	END TRY
	
	BEGIN CATCH
		
		EXEC error_handler_sp
		RETURN 333333333
		
	END CATCH
										
END

Select all Open in new window

ALTER PROCEDURE [dbo].[error_handler_sp]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	LINENO 17
	SET XACT_ABORT, NOCOUNT ON;

	DECLARE @errmsg   nvarchar(2048) = error_message(),
           @severity tinyint = error_severity(),
           @state    tinyint  = error_state(),
           @errno    int = error_number(),
           @proc     sysname = error_procedure(),
           @lineno   int = error_line()
           
   --SELECT @errmsg = error_message(), @severity = error_severity(),
   --       @state  = error_state(), @errno = error_number(),
   --       @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%' --so we donot reprocess a errmsg that has already been processed, this may happen if error was in sub query
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg) WITH LOG -- @errmsg is the first param, the errmsg in RAISERROR is only one param, '%s'.
END

Select all Open in new window

May be Throw.

Have you tried calling the sp directly from SSMS with same parameters.

Pawan,

Thank you for the comment.

"Maybe Throw" in the sproc or c#?  

What is peculiar is it works in "Result = SqlCmd.ExecuteScalar();" and not "Adapter.Fill(dt); Result = dt;"  Please look into this.  Why it works in one and not the other.

Edit: Yes they both function the same in SSMS.

Regards
Sam

EE,

Ok after further testing... "Adapter.Fill(dt); Result = dt;" will not bubble up sqlExecptions.  I put a divide by zero in the TRY block.  It errors in SSMS but does not show up in 'catch (SqlException err)'.  It does return a table with zero rows.

Regards
Sam

Yes , Divide by Zero error will not come in SQLExceptions.

You have to handle that error in your stored procedure only. Return lets say -9999 in case of divide by zero and check this value in the UI for further error messaging.

Pawan,

I do believe a divide by zero in Sql will throw an Sql Exception.  Try it in SSMS.

My question restated, "How do I bubble up exceptions from MS SQL to c# with SqlDataAdapter.Fill(); and now I add SqlDataReader .Read();."    Remember .ExecuteScalar();  works fine.

Regards
Sam

Try this code.. SQL Exception working for me in SqlDataAdapter.Fill();

Text Code:

protected void Page_Load(object sender, EventArgs e)
    {
        string con = @"Data Source = localhost; Initial Catalog=SampleDB; Integrated Security = true;";
        SqlConnection sqlcon = new SqlConnection(con);
  
        using (SqlCommand cmd = new SqlCommand("Test_Sp", sqlcon))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@firstNum", "1");
            cmd.Parameters.AddWithValue("@secondNum", "0");

            sqlcon.Open();
            
            try
            {
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                //int i = Convert.ToInt32(cmd.ExecuteScalar());
                //Response.Write(i);
                DataTable dt = new DataTable();
                adp.Fill(dt);
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }

    }


Text Stored Procedure


USE [SampleDB]
GO

/****** Object:  StoredProcedure [dbo].[Test_Sp]    Script Date: 26-Oct-16 11:22:12 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:           <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Test_Sp]
       @firstNum int,
       @secondNum int
AS
BEGIN
       select @firstNum/@secondNum as result
END

GO

--

Select all Open in new window

Hope it helps..!!

Pawan,

Thanks, that helped a lot.  I have divide by zero exceptions/errors bubbling up to c# SqlDataAdapter.Fill(dt);.  Yea!

Now I am unable to get user-defined errors to bubble up.  Actually RAISERROR(50001, -1, -1) works with SSMS but will not Bubble up to c#.  Getting these user-defined errors working was the initial goal.  But when I could not get standard sql error to bubble up I focused on that part first.  Now I am on the user-defined part.

I only made changes to the sproc, to break things :-).  I changed the divide "/" to add "+" so I could quickly switch back and forth.  And added a user-defined message (these messages are in another script, but I put this one in the sproc for simplicity).  Below is the sproc.

Thanks again
Sam

USE [SQLUTL]
GO
/****** Object:  StoredProcedure [dbo].[Test_Sp]    Script Date: 10/26/2016 16:08:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Test_Sp]
       @firstNum int,
       @secondNum int
AS
BEGIN
LINENO 18

EXEC sp_addmessage   
    @msgnum = 50001,   
    @severity = 16,  
    @msgtext =   N'This is a user defined error message',
    @lang = 'us_english';  
    
DECLARE @ContactID_str nvarchar(16);
       select @firstNum + @secondNum as result
       
       RAISERROR (50001, -1,-1) WITH Log; --https://msdn.microsoft.com/en-us/library/ms178592.aspx
       
EXEC sp_dropmessage 50001, 'all'; 
       
END

Select all Open in new window

Pawan,

I have divide by zero exceptions/errors bubbling up to c# SqlDataAdapter.Fill(dt);.

1/0 worked yesterday from your help.

Does this code work with a user-defined error?  See my code.  Comment out the divide by zero and use RAISERROR(...) instead.  I will be tying your code also, but I am sure it works with 1/0, but not with a user-defined error.  I tried something very close to that a few days ago.

Use Throw

I have Sql 2008 R2 which does not have Throw.

Thank you very much for your assistance.

Kind Regards
Sam

I think you should go ahead with Throw..

RAISERROR Statement
--If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.

THROW Statement
--The error_number parameter does not have to be defined in sys.messages.

Try...

DECLARE

     @ERR_MSG AS VARCHAR(4000)      
    ,@ERR_STA AS SMALLINT    
 
BEGIN TRY

    SELECT 1/0 as DivideByZero

END TRY

BEGIN CATCH

    SELECT @ERR_MSG = ERROR_MESSAGE(),
        @ERR_STA = ERROR_STATE()
 
    SET @ERR_MSG= 'Pawan';
 
    THROW 50001, @ERR_MSG, @ERR_STA;

END CATCH
GO

Pawan,

Throw is not available in Sql 2008 R2.  It starts with Sql 2012. https://msdn.microsoft.com/en-us/library/ee677615.aspx

In the above suggestion, the sql code cannot get a user defined message from sys.messages to pass to c#.  

Please see the minor revision I made in your sproc.  That is all I changed.  No changes were made to the c#, as it is working with your fix yesterday.  The divide by zero error must be commented out or changed to something that does not error in order to allow the user raised error, RAISERROR(), and user defined message to work.  

Here is the sproc again. Please toggle 28  --select @firstNum + @secondNum as result --Un-comment me and watch the error come and go???

Kindest Regards
Sam

USE [SQLUTL]
GO
/****** Object:  StoredProcedure [dbo].[Test_Sp]    Script Date: 10/26/2016 16:08:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Test_Sp]
       @firstNum int,
       @secondNum int
AS
BEGIN
LINENO 18

EXEC sp_addmessage   
    @msgnum = 50001,   
    @severity = 16,  
    @msgtext =   N'This is a user defined error messageX',
    @lang = 'us_english';  
    
DECLARE @ContactID_str nvarchar(16);

       --select @firstNum + @secondNum as result --Un-comment me and watch the error come and go???
       
       RAISERROR (50001, -1,-1) WITH Log; --https://msdn.microsoft.com/en-us/library/ms178592.aspx
       
EXEC sp_dropmessage 50001, 'all'; 
       
END

Select all Open in new window

Ohhh...Yes it is with SQL 2012.

So with your above change your problem is fixed ?

Pawan,

No.

Please re read all of my last post.

In addition when select is not commented out the RAISERROR() logs in SqlServerLogs but DOES NOT BUBBLE TO C#.

Thanks again for your help I have been working on this one issue for over 7 days.  I have learned a lot of other things!

Best Regards
Sam

Pawan,

I just wrapped it in a try...catch bloc.  Same bad results.

Regards
Sam

USE [SQLUTL]
GO
/****** Object:  StoredProcedure [dbo].[Test_Sp]    Script Date: 10/26/2016 16:08:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Test_2_Sp]
       @firstNum int,
       @secondNum int
AS
BEGIN
LINENO 18

EXEC sp_addmessage   
    @msgnum = 50001,   
    @severity = 16,  
    @msgtext =   N'This is a user defined error message test_2_sp',
    @lang = 'us_english';  
    
DECLARE @ContactID_str nvarchar(16);
	BEGIN TRY
       select @firstNum + @secondNum as result --Un-comment me and watch the error come and go???
       
       RAISERROR (50001, -1,-1) WITH Log; --https://msdn.microsoft.com/en-us/library/ms178592.aspx
	END TRY
	BEGIN CATCH
		declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
		select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
		--rollback transaction; --from sample
		raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
	
	END CATCH       
EXEC sp_dropmessage 50001, 'all'; 
       
END

Select all Open in new window

Can you please post the bad results?

Pawan,

The sproc correctly returns '1'.  The RAISERROR() error shows in the log.  But the error does not bubble to c#.  (I can not post the error that does not bubble up??)  Log is posted below.

Anticipating, ...there is no error...  A simple example is when a SELECT correctly performs by not returning any rows because of the WHERE criteria.  One may want to fire an exception with RAISERROR() .  i.e. Even though there was no sql error there was a user error raised.

Thanks Again
Sam

Date            10/27/2016 9:46:24 AM
Log            SQL Server (Current - 10/27/2016 8:20:00 AM)

Source            spid56

Message
This is a user defined error message test_2_sp

Date            10/27/2016 9:46:24 AM
Log            SQL Server (Current - 10/27/2016 8:20:00 AM)

Source            spid56

Message
Error: 50001, Severity: 16, State: 1.

Pawan,

Here is your sample back edited to show the exception not bubbling up.  I think all you have to do is change the connection string to your box.

Toggle the below sql line between Commented and Un Commented and you will see the issue.

28 --select @firstNum + @secondNum as result --Un-comment me and watch the error come and go???

Hope this helps.

Best Regards
Sam

C#

namespace SQLUTL.Code.WebForms.Public
{
    public partial class EE_Conn_Exception : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string ConnectionStringName = "SQLUTLtest";
            //string con = @"Data...";
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionStringName].ToString());

            using (SqlCommand cmd = new SqlCommand("Test_2_Sp", sqlcon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@firstnum", "1");
                cmd.Parameters.AddWithValue("@secondnum", "0");
                
                sqlcon.Open();

                try
                {
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);

                    DataTable dt = new DataTable();
                    adp.Fill(dt);
                    Response.Write("Hello");
                }
                catch(Exception ex)
                {
                    Response.Write(ex.Message);
                }
                finally
                {
                    Response.Write("</br>finally");
                }
            }

        }
    }
}

Select all Open in new window

sql

USE [SQLUTL]
GO
/****** Object:  StoredProcedure [dbo].[Test_Sp]    Script Date: 10/26/2016 16:08:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Test_2_Sp]
       @firstNum int,
       @secondNum int
AS
BEGIN
LINENO 18

EXEC sp_addmessage   
    @msgnum = 50001,   
    @severity = 16,  
    @msgtext =   N'This is a user defined error message test_2_sp',
    @lang = 'us_english';  
    
DECLARE @ContactID_str nvarchar(16);
	BEGIN TRY
	
       --select @firstNum + @secondNum as result --Un-comment me and watch the error come and go???
       
       RAISERROR (50001, -1,-1) WITH Log; --https://msdn.microsoft.com/en-us/library/ms178592.aspx
	END TRY
	BEGIN CATCH
		declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
		select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
		
		raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
	
	END CATCH       
EXEC sp_dropmessage 50001, 'all'; 
       
END

Select all Open in new window

Pawan,

Thank you for all your assistance.  I hope you will continue to assist tomorrow.  I talked with EE and they suggested I re-post the (more refined) question.  Close this one.  Provide you with the link to the new question so you may continue to assist..  

https://www.experts-exchange.com/questions/28979394/Bubble-user-defined-Sql-RAISERROR-to-c-exception.html .

My Best Regards
Sam