G
Guest
Hi all,
I posted a question a couple of days ago, but did not receive any answers.
I´ll try to explain my issue a bit deeper.
I will also attach some example code. One stored procedure and some C# code.
I have a stored procedure that can raise multiple raiseerror. It is
important that I can catch every raiseerror that is made by the sp. It is
also important that ExecuteReader is used.
However........
-If ExecuteReader is used, the SQLException.Errors.count is only 1.
-If ExecuteNonQuery is used, the SQLException.Errors.count is 5.
How can I get all 5 errors when using ExecuteReader??????
Best Regards,
/Daniel
______________________________________________________
CREATE PROCEDURE dbo.spInsertRegionTest
(
@RegionID int,
@RegionDescription varchar(100)
)
AS
DECLARE @ErrorNumber int
SET NOCOUNT ON
BEGIN TRANSACTION
-- Insert new record
INSERT INTO Region
(
RegionID,
RegionDescription
)
VALUES
(
@RegionID,
@RegionDescription
)
-- Save ErrorNumber before value get reset
SELECT @ErrorNumber = @@ERROR
IF @ErrorNumber <> 0
BEGIN
IF @ErrorNumber = 2627
BEGIN
RAISERROR("RegionID already exists1!", 16, 1)
RAISERROR("RegionID already exists2!", 16, 1)
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN
END
END
Select @RegionID
COMMIT TRANSACTION
GRANT EXECUTE ON dbo.spInsertRegionTest TO PUBLIC
GO
____________________________________________________________________________________
SqlCommand objCommand = null;
try
{
objCommand = new SqlCommand();
objCommand.Connection = new SqlConnection("server=dawi;User
ID=sa;Password=lololo;database=northwind;");
objCommand.Connection.Open();
objCommand.CommandType = CommandType.Text;
objCommand.CommandText = "EXECUTE spInsertRegionTest 1000, 'Test'";
objCommand.ExecuteReader();
}
catch (SqlException objException)
{
MessageBox.Show("Errors Count:" + objException.Errors.Count);
foreach (SqlError objSqlError in objException.Errors)
{
MessageBox.Show(objSqlError.Number + " - " + objSqlError.Message);
}
}
catch (Exception objException)
{
MessageBox.Show(objException.Message);
}
finally
{
objCommand = null;
}
I posted a question a couple of days ago, but did not receive any answers.
I´ll try to explain my issue a bit deeper.
I will also attach some example code. One stored procedure and some C# code.
I have a stored procedure that can raise multiple raiseerror. It is
important that I can catch every raiseerror that is made by the sp. It is
also important that ExecuteReader is used.
However........
-If ExecuteReader is used, the SQLException.Errors.count is only 1.
-If ExecuteNonQuery is used, the SQLException.Errors.count is 5.
How can I get all 5 errors when using ExecuteReader??????
Best Regards,
/Daniel
______________________________________________________
CREATE PROCEDURE dbo.spInsertRegionTest
(
@RegionID int,
@RegionDescription varchar(100)
)
AS
DECLARE @ErrorNumber int
SET NOCOUNT ON
BEGIN TRANSACTION
-- Insert new record
INSERT INTO Region
(
RegionID,
RegionDescription
)
VALUES
(
@RegionID,
@RegionDescription
)
-- Save ErrorNumber before value get reset
SELECT @ErrorNumber = @@ERROR
IF @ErrorNumber <> 0
BEGIN
IF @ErrorNumber = 2627
BEGIN
RAISERROR("RegionID already exists1!", 16, 1)
RAISERROR("RegionID already exists2!", 16, 1)
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN
END
END
Select @RegionID
COMMIT TRANSACTION
GRANT EXECUTE ON dbo.spInsertRegionTest TO PUBLIC
GO
____________________________________________________________________________________
SqlCommand objCommand = null;
try
{
objCommand = new SqlCommand();
objCommand.Connection = new SqlConnection("server=dawi;User
ID=sa;Password=lololo;database=northwind;");
objCommand.Connection.Open();
objCommand.CommandType = CommandType.Text;
objCommand.CommandText = "EXECUTE spInsertRegionTest 1000, 'Test'";
objCommand.ExecuteReader();
}
catch (SqlException objException)
{
MessageBox.Show("Errors Count:" + objException.Errors.Count);
foreach (SqlError objSqlError in objException.Errors)
{
MessageBox.Show(objSqlError.Number + " - " + objSqlError.Message);
}
}
catch (Exception objException)
{
MessageBox.Show(objException.Message);
}
finally
{
objCommand = null;
}