How to get stored procedure to ignore an @@error.

I'm trying to pass back to the calling program, the value of @.@.error from a
stored procedure. The stored procedure seems to work properly, but the
calling program diverts program flow to the "catch" portion of the try/catch
section since it knows an error occured. I'd like for it to just continue on
as if an error didn't occur, and if it wants to check for an error, it could
test the @.err output parameter.
I pushed the button twice to generate a duplicate key error on the 2nd push.
Here's the stored procedure I'm using:
----
-- Insert a single record into Categories3
----
CREATE PROC pr_Categories3_Insert
@.CategoryID int,
@.CategoryName nvarchar(30),
@.Description ntext = NULL,
@.Picture image = NULL,
@.err INT OUT
AS
SET NOCOUNT ON
SET @.err = 0
INSERT Categories2(CategoryID, CategoryName, Description, Picture)
VALUES (@.CategoryID, @.CategoryName, @.Description, @.Picture)
SELECT @.err = @.@.error
RETURN
GO
----
--
And here's the calling program that sets up the parameter list and calls it
using the data access blocks.
private void button3_Click(object sender, System.EventArgs e)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null;
try
{
try
{
connection = GetConnection(txtConnectionString.Text);
}
catch
{
MessageBox.Show("The connection with the database can′t be established",
"Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
using (SqlTransaction trans = connection.BeginTransaction())
{
// Establish command parameters
SqlParameter param_CategoryID =
new SqlParameter("@.CategoryID", SqlDbType.Int);
param_CategoryID.Value = 1111;
SqlParameter param_CategoryName =
new SqlParameter("@.CategoryName", SqlDbType.Char, 10);
param_CategoryName.Value = "Test 1";
SqlParameter param_Description =
new SqlParameter("@.Description", SqlDbType.Char, 50);
param_Description.Value = "ABCD";
SqlParameter param_Picture =
new SqlParameter("@.Picture", SqlDbType.Image);
param_Picture.Value = null;
// @.err
SqlParameter param_err =
new SqlParameter("@.err", SqlDbType.Int);
param_err.Value = null;
param_err.Direction = ParameterDirection.Output;
try
{
// Call ExecuteNonQuery static method of SqlHelper class
// We pass in SqlTransaction object, command type, stored procedure
name, and a comma delimited list of SqlParameters
int @.RC = 0;
// Perform the address Insert operation
@.RC = SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure,
"prWS_Categories2_Insert",
param_CategoryID,
param_CategoryName,
param_Description,
param_Picture,
param_err);
int @.err = (int) param_err.Value;
if (@.err != 0)
{
trans.Rollback();
txtResults.Text = "Transfer Error, ID = " + param_CategoryID.Value + ";
@.RC=" + @.RC + "; paramErr = " + param_err.Value;
}
else
{
trans.Commit();
txtResults.Text = "Transfer Completed, ID = " + param_CategoryID.Value + ";
@.RC=" + @.RC + "; paramErr = " + param_err.Value;
}
}
catch (Exception ex)
{
// throw exception
trans.Rollback();
txtResults.Text = "Transfer Error";
throw ex;
}
}
}
catch(Exception ex)
{
string errMessage = "";
for( Exception tempException = ex; tempException != null ; tempException =
tempException.InnerException )
{
errMessage += tempException.Message + Environment.NewLine +
Environment.NewLine;
}
MessageBox.Show( string.Format( "There are some problems while trying to
use the Data Access Application block, please check the following error
messages: {0}"
+ Environment.NewLine + "This test requires some modifications to the
Northwind database. Please make sure the database has been initialized using
the SetUpDataBase.bat database script, or from the Install Quickstart optio
n
on the Start menu.", errMessage ),
"Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
}
finally
{
if(connection != null)
connection.Dispose();
}
}
}"Vern" <Vern@.discussions.microsoft.com> wrote in message
news:462AF1BE-1B3E-40D2-BCD8-1A4FA853772A@.microsoft.com...
> I'm trying to pass back to the calling program, the value of @.@.error from
a
> stored procedure. The stored procedure seems to work properly, but the
> calling program diverts program flow to the "catch" portion of the
try/catch
> section since it knows an error occured. I'd like for it to just continue
on
> as if an error didn't occur, and if it wants to check for an error, it
could
> test the @.err output parameter.
> I pushed the button twice to generate a duplicate key error on the 2nd
push.
> Here's the stored procedure I'm using:
> ----
--
> -- Insert a single record into Categories3
> ----
--
> CREATE PROC pr_Categories3_Insert
> @.CategoryID int,
> @.CategoryName nvarchar(30),
> @.Description ntext = NULL,
> @.Picture image = NULL,
> @.err INT OUT
> AS
> SET NOCOUNT ON
> SET @.err = 0
> INSERT Categories2(CategoryID, CategoryName, Description, Picture)
> VALUES (@.CategoryID, @.CategoryName, @.Description, @.Picture)
> SELECT @.err = @.@.error
> RETURN
> GO
> ----
--
> And here's the calling program that sets up the parameter list and calls
it
> using the data access blocks.
> private void button3_Click(object sender, System.EventArgs e)
> {
> // SqlConnection that will be used to execute the sql commands
> SqlConnection connection = null;
> try
> {
> try
> {
> connection = GetConnection(txtConnectionString.Text);
> }
> catch
> {
> MessageBox.Show("The connection with the database cant be established",
> "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
> return;
> }
> using (SqlTransaction trans = connection.BeginTransaction())
> {
> // Establish command parameters
> SqlParameter param_CategoryID =
> new SqlParameter("@.CategoryID", SqlDbType.Int);
> param_CategoryID.Value = 1111;
> SqlParameter param_CategoryName =
> new SqlParameter("@.CategoryName", SqlDbType.Char, 10);
> param_CategoryName.Value = "Test 1";
> SqlParameter param_Description =
> new SqlParameter("@.Description", SqlDbType.Char, 50);
> param_Description.Value = "ABCD";
> SqlParameter param_Picture =
> new SqlParameter("@.Picture", SqlDbType.Image);
> param_Picture.Value = null;
> // @.err
> SqlParameter param_err =
> new SqlParameter("@.err", SqlDbType.Int);
> param_err.Value = null;
> param_err.Direction = ParameterDirection.Output;
>
> try
> {
> // Call ExecuteNonQuery static method of SqlHelper class
> // We pass in SqlTransaction object, command type, stored procedure
> name, and a comma delimited list of SqlParameters
> int @.RC = 0;
> // Perform the address Insert operation
> @.RC = SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure,
> "prWS_Categories2_Insert",
> param_CategoryID,
> param_CategoryName,
> param_Description,
> param_Picture,
> param_err);
> int @.err = (int) param_err.Value;
> if (@.err != 0)
> {
> trans.Rollback();
> txtResults.Text = "Transfer Error, ID = " + param_CategoryID.Value + ";
> @.RC=" + @.RC + "; paramErr = " + param_err.Value;
> }
> else
> {
> trans.Commit();
> txtResults.Text = "Transfer Completed, ID = " + param_CategoryID.Value +
";
> @.RC=" + @.RC + "; paramErr = " + param_err.Value;
> }
> }
> catch (Exception ex)
> {
> // throw exception
> trans.Rollback();
> txtResults.Text = "Transfer Error";
> throw ex;
> }
> }
> }
> catch(Exception ex)
> {
> string errMessage = "";
> for( Exception tempException = ex; tempException != null ; tempException
=
> tempException.InnerException )
> {
> errMessage += tempException.Message + Environment.NewLine +
> Environment.NewLine;
> }
> MessageBox.Show( string.Format( "There are some problems while trying
to
> use the Data Access Application block, please check the following error
> messages: {0}"
> + Environment.NewLine + "This test requires some modifications to the
> Northwind database. Please make sure the database has been initialized
using
> the SetUpDataBase.bat database script, or from the Install Quickstart
option
> on the Start menu.", errMessage ),
> "Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
> }
> finally
> {
> if(connection != null)
> connection.Dispose();
> }
> }
> }
>
Hello -
If the error is "Violation of unique key...", I don't think you can stop SQL
Server from throwing it.
To sidestep the problem, you could test beforehand in the stored procedure
whether a conflicting row exists - the test and update should really be
wrapped in a transaction.
Another sidestep would be to create a new method in C# dedicated to calling
the stored procedure, and this method would have a try..catch that would
deal with the exception and prevent it reaching button3_Click().
I think this issue will be easier to deal with in SQL Server 2005 - it has
try..catch built in to T-SQL.
Regards,
Simon|||I did see where 2005 has the try/catch block, and was wishing it was
available now.
I was going to test to see if the key exists first, and write the new record
if it doesn't, but when 100's of people are entering data at the same time,
there is still a chance that two people could be inserting the same record a
t
the same time. The first persons attempt will get through, and the second
person will get an error exception.
I like the idea of creating a module that calls the stored procedure so it
can have it's own try/catch block.
Thanks,
Vern

> Hello -
> If the error is "Violation of unique key...", I don't think you can stop S
QL
> Server from throwing it.
> To sidestep the problem, you could test beforehand in the stored procedure
> whether a conflicting row exists - the test and update should really be
> wrapped in a transaction.
> Another sidestep would be to create a new method in C# dedicated to callin
g
> the stored procedure, and this method would have a try..catch that would
> deal with the exception and prevent it reaching button3_Click().
> I think this issue will be easier to deal with in SQL Server 2005 - it has
> try..catch built in to T-SQL.
> Regards,
> Simon
>
>