Database Error Handling - please help

[Suhas Vengilat]

Hi All,

I have a couple of stored procedures written in SQL 2000. Given below one sp.
*************** *************** *************** ********
ALTER PROCEDURE [dbo].[usp_PC_AcTypeMI nsert]
(
@AcTypeName varchar(50),
@GLCode varchar(50),
@ErrorCode int output

)
AS
SET NOCOUNT OFF;
BEGIN TRAN

INSERT INTO [PC_AcTypeM]
([AcTypeName],
[GLCode])
VALUES (@AcTypeName, @GLCode);
SELECT @ErrorCode = @@ERROR
IF @ErrorCode <0 GOTO ERR_HANDLER
SELECT @ErrorCode = 0
COMMIT TRAN
ERR_HANDLER:
ROLLBACK TRAN
*************** *************** *************** *********
Please note that I have an output parameter for the SP, @ErrorCode to return
the ErrorCode if any error occurs.

I have a unique key constraint on the column named "AcTypeName ". Now, when
execute this SP from my C# code, using Enterprise library, trying to insert a
new AccountTypeName which is already there in the table, it gives an error.
But the problem is that the execution control goes to the exception handler,
where I retrieve the @ErrorCode value. Is it right ? Is it possible to
retrieve this error in the main execution block without going to the
Exception Handling block ?

Attached a piece of C# code for executing this SP.

*************** *************** *************** ***********
public void InsertAccountTy pe(PC_AcTypeM Actype, out int
intAccTypeError Code)
{
SqlDatabase db = new SqlDatabase(str Con);
string sqlCommand = "usp_PC_AcTypeM Insert";
DbCommand dbCommand = db.GetStoredPro cCommand(sqlCom mand);
try
{
db.AddInParamet er(dbCommand, "@AcTypeNam e", DbType.String,
Actype.AcTypeNa me);
db.AddInParamet er(dbCommand, "@GLCode", DbType.String, Actype.GLCode);
db.AddOutParame ter(dbCommand, "@ErrorCode ", DbType.Int32, 0);
db.ExecuteNonQu ery(dbCommand);
intAccTypeError Code = int.Parse(db.Ge tParameterValue (dbCommand,
"@ErrorCode").T oString());
}
catch (Exception ex)
{
intAccTypeError Code = int.Parse(db.Ge tParameterValue (dbCommand,
"@ErrorCode").T oString());
}
}
*************** *************** *************** **************

Can anyone throw some idea on this and help me to get to the right way to
achieve this ?

Thanks & Regards
Suhas

[Marc Gravell]

It is probably still getting the error from the server and responding
to that. Reading @@ERROR doesn't stop the error coming back over the
wire, and all over a certain severity are treated as exceptions.

Simply stop your SQL from erroring! (check things first, perhaps using
UPDLOCK, etc).

Marc

[JTC ^..^]

=?Utf-8?B?U3VoYXMgVmV uZ2lsYXQ=?=
<Su***********@ discussions.mic rosoft.comwrote in
news:F6******** *************** ***********@mic rosoft.com:

Hi All,

I have a couple of stored procedures written in SQL 2000. Given below
one sp. *************** *************** *************** ********
ALTER PROCEDURE [dbo].[usp_PC_AcTypeMI nsert]
(
@AcTypeName varchar(50),
@GLCode varchar(50),
@ErrorCode int output

)
AS
SET NOCOUNT OFF;
BEGIN TRAN

INSERT INTO [PC_AcTypeM]
([AcTypeName],
[GLCode])
VALUES (@AcTypeName, @GLCode);
SELECT @ErrorCode = @@ERROR
IF @ErrorCode <0 GOTO ERR_HANDLER
SELECT @ErrorCode = 0
COMMIT TRAN
ERR_HANDLER:
ROLLBACK TRAN
*************** *************** *************** *********
Please note that I have an output parameter for the SP, @ErrorCode to
return the ErrorCode if any error occurs.

I have a unique key constraint on the column named "AcTypeName ". Now,
when execute this SP from my C# code, using Enterprise library, trying
to insert a new AccountTypeName which is already there in the table,
it gives an error. But the problem is that the execution control goes
to the exception handler, where I retrieve the @ErrorCode value. Is it
right ? Is it possible to retrieve this error in the main execution
block without going to the Exception Handling block ?

Attached a piece of C# code for executing this SP.

*************** *************** *************** ***********
public void InsertAccountTy pe(PC_AcTypeM Actype, out int
intAccTypeError Code)
{
SqlDatabase db = new SqlDatabase(str Con);
string sqlCommand = "usp_PC_AcTypeM Insert";
DbCommand dbCommand = db.GetStoredPro cCommand(sqlCom mand);
try
{
db.AddInParamet er(dbCommand, "@AcTypeNam e", DbType.String,
Actype.AcTypeNa me);
db.AddInParamet er(dbCommand, "@GLCode", DbType.String,
Actype.GLCode);
db.AddOutParame ter(dbCommand, "@ErrorCode ", DbType.Int32,
0); db.ExecuteNonQu ery(dbCommand);
intAccTypeError Code =
int.Parse(db.Ge tParameterValue (dbCommand,
"@ErrorCode").T oString());
}
catch (Exception ex)
{
intAccTypeError Code =
int.Parse(db.Ge tParameterValue (dbCommand,
"@ErrorCode").T oString());
}
}
*************** *************** *************** **************

Can anyone throw some idea on this and help me to get to the right way
to achieve this ?

Thanks & Regards
Suhas

You must handle the exception, Secondly, you can get the error number by
catching the SqlException, using catch(SqlExcept ion sqlEx)
{...sqlEx.Numbe r...} and assign it to the variable.... Something like
this. Of course I haven't used the output parameter.

*************** *************** *************** *************** ****

intAccTypeError Code = 0;

try
{
cmd.Parameters. Add("@AcTypeNam e", SqlDbType.VarCh ar, 20, "AcTypeName ");
cmd.Parameters. Add("@GLCode", SqlDbType.VarCh ar, 20, "GLCode");

cmd.Parameters["@AcTypeNam e"].Value = Actype;
cmd.Parameters["@GLCode"].Value = GLCode;

cnn.Open();

cmd.ExecuteNonQ uery();
}
catch(SqlExcept ion sqlEx)
{
intAccTypeError Code = sqlEx.Number;
}
catch (Exception ex)
{
MessageBox.Show (ex.Message);
}
finally
{
cnn.Close();
}

// Do something with the error code outside the catch block
this.txtErrorCo de.Text = intAccTypeError Code.ToString() ;

*************** *************** *************** *************** ****
--
Regards
JTC ^..^