Your question, your audience. Choose who sees your identity—and your question—with question security.
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;
}
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
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
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
--
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
I have divide by zero exceptions/errors bubbling up to c# SqlDataAdapter.Fill(dt);.1/0 worked yesterday from your help.
Use ThrowI have Sql 2008 R2 which does not have Throw.
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
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
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");
}
}
}
}
}
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
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
Open in new window