Database Error Handling - please help

G

Guest

Hi All,

I have a couple of stored procedures written in SQL 2000. Given below one sp.
*****************************************************
ALTER PROCEDURE [dbo].[usp_PC_AcTypeMInsert]
(
@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 InsertAccountType(PC_AcTypeM Actype, out int
intAccTypeErrorCode)
{
SqlDatabase db = new SqlDatabase(strCon);
string sqlCommand = "usp_PC_AcTypeMInsert";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
try
{
db.AddInParameter(dbCommand, "@AcTypeName", DbType.String,
Actype.AcTypeName);
db.AddInParameter(dbCommand, "@GLCode", DbType.String, Actype.GLCode);
db.AddOutParameter(dbCommand, "@ErrorCode", DbType.Int32, 0);
db.ExecuteNonQuery(dbCommand);
intAccTypeErrorCode = int.Parse(db.GetParameterValue(dbCommand,
"@ErrorCode").ToString());
}
catch (Exception ex)
{
intAccTypeErrorCode = int.Parse(db.GetParameterValue(dbCommand,
"@ErrorCode").ToString());
}
}
***********************************************************

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

Thanks & Regards
Suhas
 
M

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
 
J

JTC ^..^

=?Utf-8?B?U3VoYXMgVmVuZ2lsYXQ=?=
Hi All,

I have a couple of stored procedures written in SQL 2000. Given below
one sp. *****************************************************
ALTER PROCEDURE [dbo].[usp_PC_AcTypeMInsert]
(
@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 InsertAccountType(PC_AcTypeM Actype, out int
intAccTypeErrorCode)
{
SqlDatabase db = new SqlDatabase(strCon);
string sqlCommand = "usp_PC_AcTypeMInsert";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
try
{
db.AddInParameter(dbCommand, "@AcTypeName", DbType.String,
Actype.AcTypeName);
db.AddInParameter(dbCommand, "@GLCode", DbType.String,
Actype.GLCode);
db.AddOutParameter(dbCommand, "@ErrorCode", DbType.Int32,
0); db.ExecuteNonQuery(dbCommand);
intAccTypeErrorCode =
int.Parse(db.GetParameterValue(dbCommand,
"@ErrorCode").ToString());
}
catch (Exception ex)
{
intAccTypeErrorCode =
int.Parse(db.GetParameterValue(dbCommand,
"@ErrorCode").ToString());
}
}
***********************************************************

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

Thanks & Regards
Suhas
Click to expand...

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

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

intAccTypeErrorCode = 0;

try
{
cmd.Parameters.Add("@AcTypeName", SqlDbType.VarChar, 20, "AcTypeName");
cmd.Parameters.Add("@GLCode", SqlDbType.VarChar, 20, "GLCode");

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

cnn.Open();

cmd.ExecuteNonQuery();
}
catch(SqlException sqlEx)
{
intAccTypeErrorCode = sqlEx.Number;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cnn.Close();
}

// Do something with the error code outside the catch block
this.txtErrorCode.Text = intAccTypeErrorCode.ToString();

****************************************************************
 
Top