Solved

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

Posted on 2016-10-20
19
44 Views
Last Modified: 2016-10-27
EE,

'SqlDataAdapter.Fill()' will not catch error raised in Sproc.  The error is logged in 'SQL Server Logs, so I know it is firing.  The SqlCmd.ExecuteScalar(); in the else block passes it's errors correctly.  I haven't found anything Googling. What am I missing?

Regards
Sam

public class SQLUTL
    {
        internal static object Sel(Type type, string ConnectionStringName, string SProc, string[,] Parameters,
                                    string TVParameterName = "", DataTable TVParameter = null, string RequestingPage = "Page not provided")
        {
            object Result;
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionStringName].ToString());
            SqlCommand SqlCmd = new SqlCommand(SProc, Conn);
            SqlDataAdapter Adapter = new SqlDataAdapter();
            SqlCmd.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < Parameters.GetLength(0); i++)
            {
                SqlParameter P = new SqlParameter();
                P.ParameterName = Parameters[i, 0];
                if (Parameters[i, 1] == "")
                {
                    P.Value = DBNull.Value;
                }
                else
                {
                    P.Value = Parameters[i, 1];
                }
                SqlCmd.Parameters.Add(P);
            }
            try
            {
                Conn.Open();
                
                if (type == typeof(DataTable))
                {
                    DataTable dt = new DataTable();
                    Adapter.SelectCommand = SqlCmd;
                    Adapter.Fill(dt);  //Does not error
                    Result = dt;
                }
                else
                {
                    Result = SqlCmd.ExecuteScalar(); //works correctly
                }
            }
            catch (SqlException err)
            {
                Debugger.Break();
                string e = RequestingPage; e = SProc; string[,] p = Parameters; // so i can see these without scrolling
                throw err;
            }
            finally
            {
                // do clean up close connections etc;
            }
            return Result;
        }

Open in new window

0
Comment
Question by:SamCash
  • 12
  • 7
19 Comments
 

Author Comment

by:SamCash
Comment Utility
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

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

Open in new window

0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
May be Throw.

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

Author Comment

by:SamCash
Comment Utility
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
0
 

Author Comment

by:SamCash
Comment Utility
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
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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.
0
 

Author Comment

by:SamCash
Comment Utility
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
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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

--

Open in new window


Code
Hope it helps..!!
0
 

Author Comment

by:SamCash
Comment Utility
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

Open in new window

0
 
LVL 16

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
I think you should use THROW statement in SQL Server 2012 instead of RAISERROR !! Anyways try below.

C# 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);
                DataTable dt = new DataTable();
                adp.Fill(dt);
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }

    }

Error:

 

Stored Procedure Code:

CREATE PROCEDURE [dbo].[Test_Sp]
       @firstNum int,
       @secondNum int
AS
BEGIN
       BEGIN TRY
  
         DECLARE @result INT

         SET @result = @firstNum/@secondNum 

END TRY
BEGIN CATCH

       DECLARE
          @ErMessage NVARCHAR(2048),
          @ErSeverity INT,
          @ErState INT

        SELECT
          @ErMessage = ERROR_MESSAGE(),
          @ErSeverity = ERROR_SEVERITY(),
          @ErState = ERROR_STATE()
                print @ErMessage
                print @ErSeverity
                print @ErState
        RAISERROR (@ErMessage,
                           @ErSeverity,
                           @ErState )

END CATCH


END

Open in new window


Code..
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:SamCash
Comment Utility
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
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
0
 

Author Comment

by:SamCash
Comment Utility
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

Open in new window

0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Ohhh...Yes it is with SQL 2012.

So with your above change your problem is fixed ?
0
 

Author Comment

by:SamCash
Comment Utility
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
0
 

Author Comment

by:SamCash
Comment Utility
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

Open in new window

0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Can you please post the bad results?
0
 

Author Comment

by:SamCash
Comment Utility
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.
0
 

Author Comment

by:SamCash
Comment Utility
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");
                }
            }

        }
    }
}

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

Open in new window

0
 

Author Comment

by:SamCash
Comment Utility
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now