Multiple raiseerrors = SqlException.Errors

[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;
}

 

[Kevin Yu [MSFT]]

Hi Daniel,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that when you execute the same stored
procedure using ExecuteReader and ExecuteNonQuery, you got different
numbers of errors. If there is any misunderstanding, please feel free to
let me know.

I tried you stored procedure and C# code on my computer, however, both
ExcuteReader and ExecuteNonQuery throws a SqlException with all 4 errors.
The error IDs are 2627, 50000, 50000 and 3621.

There is also some mistakes in the stored procedure. When raising error, we
have to use single quotes to represent a string. Please change raiserror to
the following:

RAISERROR('RegionID already exists1!', 16, 1)
RAISERROR('RegionID already exists2!', 16, 1)

If it still doesn't work on your machine, could you try to take a look at
the following KB article? It provides us with a walkthrough on this task.

http://support.microsoft.com/?id=321903

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Hi Kevin,
Thank you for your reply!

You absolutely did understand my issue correct.

I compiled two versions of my code. One with ExecuteReader and one with
ExecuteNonQuery.

1. Exec on my local Windows XP
ExecuteReader = 1 error
ExecuteNonQuery = 4 errors

2. Exec on a Windows 2003 server
ExecuteReader = 4 error
ExecuteNonQuery = 4 errors

What can I do to find out the cause of this? Do you have any ideas?

Regards
/Daniel

 

[Kevin Yu [MSFT]]

Hi Daniel,

I'm using a Windows XP computer to test this issue. However, it returns 4
error in both senarios. Could you find another Windows XP computer to test
it again?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."