Remove From My Forums

How to display errors from stored procedures?

Question
0

Sign in to vote

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

0

Sign in to vote
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

0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote
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

0

Sign in to vote

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

Thursday, December 11, 2008 1:41 PM

0

Sign in to vote
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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote
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
0

Sign in to vote

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
0

Sign in to vote
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
0

Sign in to vote
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
0

Sign in to vote

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

Answered by:

How to display errors from stored procedures?

Question

Answers

All replies

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.

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.