locked
How to display errors from stored procedures? RRS feed

  • Question

  • User-578366845 posted

    This scenario is for Dynamic Data with EF plus stored procedures.

    Stored procedures apply some logic and error handling and use RAISEERROR statement to return error messages to the ASP.NET with Dynamic Data. Unfortunately, by default Dynamic Data is not showing error messages coming from stored procedures.

    The question is if it is possible to modify Dynamic Data layer and display errors from the stored procedures in the same visual manner like they come from the client side attribute based validation similar to

    [Range(x,x, ErrorMessage=”…”)] or
    [RegularExpression(…, ErrorMessage=”…”)]

    Regards,
    Yitzhak

     

    Sunday, December 7, 2008 1:25 AM

Answers

  • User1641955678 posted

    FYI, I just wrote a blog post on this issue, with a complete sample attached.

    thanks,
    David

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 11, 2008 6:04 PM

All replies

  • User-330204900 posted

    Hi Yitzhak, the first question is does EF support this i.e. bubbling up the errors through EF to the next layer.

    What I would try is create a standard ASP.Net page or a command line app and test this by doing some Linq to EF in a try catch loop and see if your custom error is returned if it is then you can proceed.

    Have you tried somthing like this with any sucess?

    Sunday, December 7, 2008 6:36 AM
  • User-578366845 posted

    I came across David Ebbo's blog posting which sheds some light on the subject:

    http://blogs.msdn.com/davidebb/archive/2008/05/25/dynamic-data-ajax-and-javascript-errors.aspx

    But it covers just the development phase, not the deployed Dynamic Data application.
    The Dynamic Data community needs some kind of sound approach to present validation errors from stored procedures.

    Regards,
    Yitzhak

    Sunday, December 7, 2008 10:19 AM
  • User-1005219520 posted

    Setting EnablePartialRendering =false is only for development/debugging -  But I don't see why you couldn't use it to trap a better error message and then use a custom error page..

    Monday, December 8, 2008 1:08 AM
  • User-578366845 posted
    On a practical note, when we create a custom validation method it looks similar to this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

     

    partial void On<ColumnName>Changing(string value)
    {
       // some validation logic
       …
       if(…)
       {
          throw new ValidationException("Error message text");
       }
    }<o:p></o:p>

     

    After  that  the error message is displayed and behaves along the lines with the built-in out-of-the-box Dynamic Data attribute based validation.

    Now back to the scenario when we have a stored procedure raising an error through the RAISEERROR statement.
    How to redirect the application flow (error bubbling) to the same execution path as a built-in
    Dynamic Data attribute based validation?

    <o:p></o:p>Regards,
    Yitzhak<o:p></o:p>
    <o:p> </o:p>

     

    Monday, December 8, 2008 9:24 AM
  • User-1005219520 posted

    I'll check with the Dev folks - but I think the fundamental problem is the DD validation requires you to detect problems on the client side before the data model sends the changes to the DB.  Once you get past the On<ColumnName>Changing phase the data model sends the changes to the DB and it's too late for validation to catch the error. - That's not to say you can't change the order of events and call your Stored Proc in the OnValidate (L2S) or the SavingChanges event handler (EF) to catch/display the error in validation. Many errors cannot be caught on the client side and require DB side validation.

    Monday, December 8, 2008 2:21 PM
  • User-1005219520 posted

    Hi Yitzhak,

    I think you should be able to run your stored proc from Onvalidate/OnSavingChanges. Let me know if that works - in the mean time we'll investigate.

    Monday, December 8, 2008 3:25 PM
  • User-578366845 posted

    Hi Rick, 

    It is an interesting idea to call stored procedures in the Onvalidate/OnSavingChanges event handlers.

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>But wouldn’t such scenario produce 2 calls to the database?<o:p></o:p>

    First, from the custom code in the Onvalidate/OnSavingChanges event handler and second, immediately after that by the native Dynamic Data application execution path.

    P.S. Did you have a chance to consult with the Dynamic Data development team on the subject?

    <o:p></o:p>Regards,
    Yitzhak<o:p></o:p>
    <o:p> </o:p>

     

    Wednesday, December 10, 2008 8:47 AM
  • User-1005219520 posted

    Hi Yitzhak,

    Yes, you would get two calls to the DB. We discovered the problem is in the ADO Entity Framework - specifically that SaveChanges is not virtual. We've made a request to the EF team to fix the problem. We hope the problem will be fixed in the next release.

    Wednesday, December 10, 2008 1:19 PM
  • User1641955678 posted

    Here is a workaround which I think will work in the mean time.  First, define a control derived from DynamicValidator, and do something like this:

    public class MyDynamicValidator : DynamicValidator {
        protected override void ValidateException(Exception exception) {
            // If it's not already an exception that DynamicValidator looks at
            if (!(exception is IDynamicValidatorException) && !(exception is ValidationException)) {
                // Find the most inner exception
                while (exception.InnerException != null) {
                    exception = exception.InnerException;
                }

                // Wrap it in a ValidationException so the base code doesn't ignore it
                exception = new ValidationException(null, exception);
            }

            base.ValidateException(exception);
        }
    }

    Then make sure your control is used instead of DynamicValidator.  You can do this easily in web.config, as follows:

        <pages>
          <tagMapping>
            <add tagType="System.Web.DynamicData.DynamicValidator" mappedTagType="MyDynamicValidator"/>
          </tagMapping>
        </pages>
    

    In theory that should do the trick.  Let me know how that works for you.

    thanks,
    David

     

    Wednesday, December 10, 2008 8:02 PM
  • User-578366845 posted

    Hi David,

    I followed your instructions and so far it didn’t work.

    Here are my steps:

    1.       I created MyDynamicValidator.cs file and placed it in the App_Code folder.
    The file contains your code verbatim from your post above.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.DynamicData;
    using System.ComponentModel.DataAnnotations;
    
    public class MyDynamicValidator : DynamicValidator
    {
        protected override void ValidateException(Exception exception)
        {
            // If it's not already an exception that DynamicValidator looks at
            if (!(exception is IDynamicValidatorException) && !(exception is ValidationException))
            {
                // Find the most inner exception
                while (exception.InnerException != null)
                {
                    exception = exception.InnerException;
                }
    
                // Wrap it in a ValidationException so the base code doesn't ignore it
                exception = new ValidationException(null, exception);
            }
    
            base.ValidateException(exception);
        }
    }
    
     

    2.  I modified the web.config file as shown below:

     

    <pages>
       …
       <tagMapping>
          <clear />
          <add tagType="System.Web.DynamicData.DynamicValidator" mappedTagType="MyDynamicValidator"/>
       </tagMapping>
     </pages>
    
      

    I placed a break point inside body of the MyDynamicValidator class. The Dynamic Data application flow never came to the MyDynamicValidator class.
    It looks like that it was ignored. The Internet browser showed an error message in the message box:

    Line: 4723
    Error: Sys.WebForms.PageRequestManagerServerErrorException: Object reference not set to an instance of an object.

    Is it possible that something else is missing? Please advise.

    Regards,
    Yitzhak

     

    Thursday, December 11, 2008 11:30 AM
  • User1641955678 posted

    Hi Yitzhak,

    Could you set EnablePartialRendering to false in the master page?  You should then get the full stack of where the null ref exception is happening, which should help us find the right next step.

    thanks,
    David

    Thursday, December 11, 2008 1:00 PM
  • User-578366845 posted

    I am calling the Dynamic Data without stored procedures, i.e as is, directly.
    The original idea was and still is to redirect the app. flow through the Dynamic Data native flow for the client side validation error path.

    Here is the error info:

    Server Error in '/LM_BuildingMaintenance' Application.

    The DELETE statement conflicted with the REFERENCE constraint "FK_Fee_FeeCategory". The conflict occurred in database "LM_Building", table "dbo.Fee", column 'FeeCategoryID'.
    The statement has been terminated.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Fee_FeeCategory". The conflict occurred in database "LM_Building", table "dbo.Fee", column 'FeeCategoryID'.
    The statement has been terminated.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:

    [SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_Fee_FeeCategory". The conflict occurred in database "LM_Building", table "dbo.Fee", column 'FeeCategoryID'.
    The statement has been terminated.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
       System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
       System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
       System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
       System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues) +554
       System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) +253
    
    [UpdateException: An error occurred while updating the entries. See the InnerException for details.]
       System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) +442
       System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) +117
       System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave) +453
       System.Data.Objects.ObjectContext.SaveChanges() +9
       System.Web.UI.WebControls.EntityDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +624
       System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +89
       System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +714
       System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +869
       System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
       System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
       System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123
       System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
       System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
       System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
       System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
    


    Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053 <!-- [SqlException]: The DELETE statement conflicted with the REFERENCE constraint "FK_Fee_FeeCategory". The conflict occurred in database "LM_Building", table "dbo.Fee", column 'FeeCategoryID'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues) at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) [UpdateException]: An error occurred while updating the entries. See the InnerException for details. at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave) at System.Data.Objects.ObjectContext.SaveChanges() at System.Web.UI.WebControls.EntityDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) at System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) at System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) at System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) at System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) at System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) at System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) [HttpUnhandledException]: Exception of type 'System.Web.HttpUnhandledException' was thrown. at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.dynamicdata_pagetemplates_list_aspx.ProcessRequest(HttpContext context) in c:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\lm_buildingmaintenance\06c40a23\85e87558\App_Web_lvtcstx5.8.cs:line 0 at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) -->

    Thursday, December 11, 2008 1:41 PM
  • User1641955678 posted

    I'm a bit confused here.  If you get a null ref exception in the javascript error, then you should still get a null ref exception when you turn off partial rendering.  It should just be displayed with a full stack trace.

    Misc things to try:

    • Add a constructor to MyDynamicValidator and see if it gets there
    • Change the config registration to a bad class name (e.g. mappedTagType="BadDynamicValidator") and make sure that it complains.

    David

    Thursday, December 11, 2008 2:19 PM
  • User-1005219520 posted

    Why not trap the exception in a global error handler, parse the error message (so you have a good idea of what went wrong) - then redirect to the previous page and post an intelligent error message. (This row cannot be deleted until dependant rows are deleted) - That was what I meant when I originally said Setting EnablePartialRendering in not only for debug/dev. Brad tells me even with  EnablePartialRendering set to true, you can do a redirect and get the error message.

    Thursday, December 11, 2008 2:50 PM
  • User-578366845 posted

    Hi David, 

    Some progress on my side.

    The addition of the empty constructor to the MyDynamicValidator class did the trick.
    The code inside ValidateException() is executing now.

    So far I see 2 open issues:

    1.       The error message is displayed in the message box in the browser, not through the same execution path as a built-in Dynamic Data attribute based validation. This was the original intent.

    2.       The actual error message in the message box in the browser still doesn’t show  the exception.InnerException.It seems that the code manipulating with exceptions inside ValidateException() should be tweaked.

    Below is the message I am getting in the message box of the Internet Explorer:

    ---------------------------

    Error

    ---------------------------

    A Runtime Error has occurred.

    Do you wish to Debug?

    Line: 4723

    Error: Sys.WebForms.PageRequestManagerServerErrorException: An error occurred while updating the entries. See the InnerException for details.

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

    Regards,
    Yitzhak

     

    Thursday, December 11, 2008 2:54 PM
  • User1641955678 posted

    Hi Yitzhak,

    You really should turn off partial rendering while trying to get to the bottom of this issue, as it masks the real errors.  Have you tried debugging into ValidateException() to see whether it gets to the line that creates the ValidationException?  This works fine for me.  The scenario I tested was:

    • DD app using Entity Framework
    • Create a Northwind model
    • Try to delete a category, which causes a DB error because of contraint violations

    With code above, I get the error to show up in the page validation summary.

    David

    Thursday, December 11, 2008 4:24 PM
  • User1641955678 posted

    Hi Yitzhak,

    I think I know why this is not quite working for you.  There is another small issue that has come up before in the forum and is easy to fix: in List.aspx and Details.aspx, you'll find a CausesValidation="false".  Delete it (or set it to true), and I think it'll all work for you.

    David

    Thursday, December 11, 2008 4:52 PM
  • User-1005219520 posted
    If you don't delete  CausesValidation="false"   From  List.aspx (and ListDetails if you're using it)  - you get a less than helpful message A Runtime Error has occurred.
    Do you wish to Debug?   Line: 4723
    Error: Sys.WebForms.PageRequestManagerServerErrorException: An error occurred while updating the entries. See the InnerException for details.

    After deleting  , on AdventureWorks light I tried to delete a product description (that is referenced in the product table and has a constraint). I get the following nice Validation error message:

    List of validation errors
    • The DELETE statement conflicted with the REFERENCE constraint "FK_ProductModelProductDescription_ProductDescription_ProductDescriptionID". The conflict occurred in database "AdventureWorksLT2008", table "SalesLT.ProductModelProductDescription", column 'ProductDescriptionID'. The statement has been terminated.
    Yitzhak - ping me offline if you're still having problems.
    Thursday, December 11, 2008 6:00 PM
  • User1641955678 posted

    FYI, I just wrote a blog post on this issue, with a complete sample attached.

    thanks,
    David

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 11, 2008 6:04 PM
  • User-578366845 posted

    Just to finalize this very long  post.

    The solution provided by David Ebbo is exactly what the doctor ordered.

    I am very thankful to all the participants who contributed to this post on the forum.
    It provided an important insight how to handle exceptions coming out of the database. 

    Regards,
    Yitzhak

    Friday, December 12, 2008 12:00 PM