=?Utf-8?B?U3VoYXMgVmVuZ2lsYXQ=?=
<(E-Mail Removed)> wrote in
news:F65B9639-77B2-4C83-A312-(E-Mail Removed):
> 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
>
>
>
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();
****************************************************************
--
Regards
JTC ^..^
|
Similar Threads
