Raiserror in procedure (SQL Server 2005) is not caught in update process in client side(c#)

Question

I am using stored procedure with RAISERROR. The error raised by the SP is not caught by the try catch statement in c#.Net. I'm using SqlDataSource for SQL connection and SqlDataSource_Updating event to update the data.

SQL Code:

DECLARE @count int
@count=0
   if(@count = 0)
   begin
   RAISERROR('Updation failed. record already exists', 16, 1) 
  end

c# Code:

protected void sqlDataSource_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
  try
  {
    // Assigns the update command parameter
    e.Command.Parameters.Add(new SqlParameter("@fag", Flg));
    e.Command.Parameters.Add(new SqlParameter("@Date", DateTime.Now));

  }
  //catch (SqlException ex)
  //{


  //}
  catch (Exception ex)
  {
    // If so, checks the Exception message
    if (ex.Message == "Updation failed. record Not exists")
    {
        // Display the details of the exception to the user
        lblMessage.Text = " record already exists";
    }

    // Writes the error in log file
    WriteErrorLog(ex.Message , "sqlDataSource_Updating");
  }
}

SQLDataSource:

                SelectCommand="SELECT [ID], [Name], [Flg] FROM [Master] ORDER BY Name "  

                InsertCommand="exec [Master_Insert] @ID, @Name, @Flg, @createdDate, @createdBy, @updatedDate, @updatedBy"

                UpdateCommand="exec [Master_Update] @ID, @Name, @Flg, @updatedDate, @updatedBy"

                 DeleteCommand="DELETE FROM Master WHERE ID = @ID" 
                            oninserted="sqlDataSource_Inserted" 
                            oninserting="sqlDataSource_Inserting" 
                            onupdated="sqlDataSource_Updated" 
                            onupdating="sqlDataSource_Updating"> 
                </asp:sqldatasource>

Regards Geetha

Answer 1

Thank You for all your replies.

By using a e.Exception in sqlDataSource_Updated my problem got solved.

protected void sqlDataSource_Updated(object sender, SqlDataSourceStatusEventArgs e)

{

       if (e.Exception != null)
            {

                // If so, checks the Exception message

                if (e.Exception.Message == "record already exists")

                {

                }
         }
}

Answer 2

One thing I noticed right away is that the error message that your stored procedure is raising "Updation failed. record already exists" does not match the text string, "Updation failed. record Not exists", that you are checking in your try/catch block.

Answer 3

The Updating event is used to customize the SqlCommand being executed, but it does not execute the command. The try/catch block must wrap the actual place where the command is executed, not the customization callback.