Question
To manage validation in a multi-user web project, my stored method is producing custom errors. However, when my C# code detects the issue, it always gets the error number 50000 instead of 50001, thus I cannot handle the errors differently. This is functioning as intended in SQL Server, with error number 50001 being returned.
How do I recognize error 50001? Please take note that I am running every stored procedure using Dapper.
SQL
THROW 50001, 'Client already has an Active Visit! THIS IS DEV!!!!',1;
Msg 50001, Level 16, State 1, Line 1
Client already has an Active Visit! THIS IS DEV!!!!
C#
catch (SqlException ex)
{
var errorHandler = new ErrorHandler();
var msg = errorHandler.ErrorMessage(ex);
if (ex.Number == 50001)
{
return BadRequest(msg);
}
else
{
return StatusCode(500, msg);
}
}
catch (Exception ex)
{
var errorHandler = new ErrorHandler();
return StatusCode(500, errorHandler.ErrorMessage(ex));
}
EXAMPLE
Accepted Answer
It seems that error 50001 is not being found in SQL Server error 50000, which is reserved for generic user defined message.sys.messages
You might try utilizing the saved procedure to add the error.sp_addmessage
.
Once it is finished, you may refer to it as follows:
RAISERROR(50001, 1, 1)
If you are reporting the SQL error in a SQL TRY/CATCH, which will always return a 50000 error code, that is another reason it can be error 50000.
I recently tested this code:
using (var cmd = new SqlCommand("THROW 50001,'error',1;", conn))
{
cmd.ExecuteNonQuery();
}
It seems that there is an issue with your SQL since that does return ex.Number 50001. As I said before, a TRY/CATCH might be to blame as it won't keep the original error code number.