Friday, August 13, 2010

Handle Fatal Error in SQL using DOT NET Try Catch block

Stored Procedure with fatal error

tempPK has one column with datatype = int
==============================================
CREATE PROCEDURE [dbo].[spError]
AS
BEGIN

Begin Tran
--Select 1/0

--execute('insert into tempPK select ''s''')
execute('insert into tempPK select ''fg''')
execute('insert into tempPK select 4')
Commit Tran
print 'Success'
END

======================================================
Dot Net Calling Method
try
{
SqlConnection sCon = new SqlConnection();
sCon.ConnectionString = "Database=xyz;Server=10.62;User Id=sa;Password=#12#";
sCon.Open();
SqlCommand sCom = new SqlCommand();
sCom.Connection = sCon;
sCom.CommandText = "Exec spError";
sCom.ExecuteNonQuery();
}
catch (Exception ex)
{
if(ex.Message.Substring(ex.Message.Length-7) == "Success")
MessageBox.Show("No Fatal Error");
else
MessageBox.Show("Fatal Error");
}
======================================================

No comments:

Post a Comment