Solved

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

Posted on 2016-10-20
19
149 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
19 Comments
 

Author Comment

by:SamCash
ID: 41853159
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 28

Expert Comment

by:Pawan Kumar
ID: 41853376
May be Throw.

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

Author Comment

by:SamCash
ID: 41853804
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:SamCash
ID: 41854265
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 28

Expert Comment

by:Pawan Kumar
ID: 41855389
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
ID: 41859600
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 28

Expert Comment

by:Pawan Kumar
ID: 41859772
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
ID: 41861478
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 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41861661
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
 

Author Comment

by:SamCash
ID: 41862185
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 28

Expert Comment

by:Pawan Kumar
ID: 41862204
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
ID: 41862376
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 28

Expert Comment

by:Pawan Kumar
ID: 41862418
Ohhh...Yes it is with SQL 2012.

So with your above change your problem is fixed ?
0
 

Author Comment

by:SamCash
ID: 41862445
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
ID: 41862500
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 28

Expert Comment

by:Pawan Kumar
ID: 41862570
Can you please post the bad results?
0
 

Author Comment

by:SamCash
ID: 41862689
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
ID: 41862726
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
ID: 41862860
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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