Second Life of a Hungarian SharePoint Geek

October 4, 2010

Validation issues with external lists (or have I missed something?)

Filed under: BCS, Bugs, External list, SP 2010 — Tags: , , , — Peter Holpar @ 00:58

Probably most of you have already seen one of those compelling demos of the new external list feature of SharePoint 2010 when it took only a few mouse click for the presenter to display business data stored for example in SQL database integrated into SharePoint as a list.

I should admit I became very enthusiastic over this exciting new possibility when first saw that on the SharePoint Conference last year. One year has elapsed since then and – although I’m still interested in this feature – I had to learn there are some rather annoying issues if you scrape the surface a bit.

One of the most serious issues I found is the question of validation. It’s usually trivial in the case of a traditional business application development, but it will give you a hard time in the case of external list.

In this post I illustrate this issue only with a few examples and the error stack traces belonging to them.

First, let’s see the well known Northwind Customers example. In this case the customers from the Northwind database are displayed in a SharePoint external list.

The image below shows the fields of the list. As you can see, all of the fields are Single line of text (the corresponding type in BDC is System.String), but you have no more information about them than whether they are optional of mandatory fields.

image

In contrast with System.String database fields are usually has length limitation. So let’s see what happens if we type content longer than the corresponding field in database allows that.

image

I would expect some user friendly warning message but instead of that we receive an unhandled exception.

image

Full stack trace is displayed below, having the most important text parts bolded.

Server Error in ‘/’ Application.
——————————————————————————–

String or binary data would be truncated.
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: String or binary data would be truncated.
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): String or binary data would be truncated.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +58
   System.Data.SqlClient.SqlDataReader.get_MetaData() +112
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6281668
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6282737
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +424
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +28
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +211
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +19
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +19
   Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbSystemUtility.ExecuteStaticInternal(DbAccessProviderObjectFactory accessProviderObjectFactory, IConnectionManager connectionManager, IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, INamedPropertyDictionary lobSystemInstanceProperties, Object[]& args, IDbConnection connection, Boolean connectionExplicitlyManaged) +14939845
   Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbSystemUtility.ExecuteStatic(IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, Object[] args, IExecutionContext context) +577

[RuntimeException: The query against the database caused an error.]
   Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbSystemUtility.ExecuteStatic(IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, Object[] args, IExecutionContext context) +14941379
   Microsoft.SharePoint.BusinessData.Runtime.DataClassRuntime.ExecuteInternalWithAuthNFailureRetry(ISystemUtility systemUtility, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IParameterCollection nonReturnParameters, Object[] overrideArgs) +377
   Microsoft.SharePoint.BusinessData.Runtime.DataClassRuntime.ExecuteInternal(IDataClass thisDataClass, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, IParameterCollection nonReturnParameters, Object[]& overrideArgs) +19307891
   Microsoft.SharePoint.BusinessData.Runtime.EntityRuntime.ExecuteInternal(IDataClass thisDataClass, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, IParameterCollection nonReturnParameters, Object[]& overrideArgs, IFilterCollection filters) +122
   Microsoft.SharePoint.BusinessData.Runtime.AbstractEntityInstance.UpdateInternal(IMethodInstance updater, IEntityInstance instanceToUpdate, IDictionary`2 preValues) +316
   Microsoft.SharePoint.BusinessData.Runtime.AbstractEntityInstance.Update(Boolean compareHash) +1684
   Microsoft.SharePoint.SPListDataSource.UpdateEntityInstance(String bdcid, SPListItem itemToUpdate, IDictionary dictFieldValues) +212

[SPException: Failed to update a list item for this external list based on the Entity (External Content Type) ‘Customer’ in EntityNamespace ‘http://sp2010/offline’. Details: The query against the database caused an error.]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.UpdateCallback(Int32 affectedRecords, Exception ex) +23690873
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4052212
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +312
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

You will get similar exceptions if you database table has unique key constraints of foreign key references and the user input contains a value that does not fulfill these constraints.

Now let’s see some examples where we work with Visual Studio and edit the BDC model file.

If you read the page BDC Custom Properties there are some promising properties.

Reference type fields (like String) are optional by default and value type fields (like int, double) are mandatory.

You can change this behavior and set for example a text as mandatory and a number to optional via the RequiredInForms property.

The following XML fragments of the DBC model file illustrate that concept.

  1. <TypeDescriptor TypeName="System.String" Name="Message">
  2.   <Properties>
  3.     <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
  4.   </Properties>
  5. </TypeDescriptor>

and similarly

  1. <TypeDescriptor TypeName="System.Int32" Name="Number">
  2.   <Properties>
  3.     <Property Name="RequiredInForms" Type="System.Boolean">false</Property>
  4.   </Properties>
  5. </TypeDescriptor>

If you prefer, you can use the BDC designer as well and set the property there in the Custom Properties.

image

If you want your value type to be optional, don’t forget to declare that accordingly (e.g. it should be nullable to accept empty values):

public int? Number { get; set; }

I’ve also played with the DecimalDigits property to limit the allowed number of decimal digits for a number and with the Size property to set the maximum length of a text, but without success.

Back to the validation issues.

If you have a field in your model that stores its value as int, then the input field control is smart enough to detect if you type a text into it. In this case you get the following validation error:

image

The value of this field is not a valid number.

But it is not smart enough to limit you only typing integers. So if you enter a non-integer number (like 22.22) or the text “NaN” (!) then you get different exceptions based on the method type. In the case of the edit item form (in the Updater method):

Server Error in ‘/’ Application.
——————————————————————————–

The data source control failed to execute the update command. 0616f1c3-a17b-4218-90af-932d82d54a85
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: Microsoft.SharePoint.WebPartPages.DataFormWebPartException: The data source control failed to execute the update command. 0616f1c3-a17b-4218-90af-932d82d54a85

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:

[DataFormWebPartException: The data source control failed to execute the update command. 0616f1c3-a17b-4218-90af-932d82d54a85]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.UpdateCallback(Int32 affectedRecords, Exception ex) +23690775
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4052212
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +312
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

In the case of the new item form (in the Creator method):

Server Error in ‘/’ Application.
——————————————————————————–

Input string was not in a correct format.
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.FormatException: Input string was not in a correct format.

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:

[FormatException: Input string was not in a correct format.]
   System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +10161267
   System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +207
   System.String.System.IConvertible.ToInt32(IFormatProvider provider) +61
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +307
   Microsoft.SharePoint.BdcClientUtil.ChangeType(Object value, Type destinationType, IFormatProvider culture) +478
   Microsoft.SharePoint.SPListDataSource.FillOneFieldValue(SPField fld, IView view, String bdcfldnm, Object objRawFieldValue, IFieldValueDictionary dictFields) +536

[SPException: Failed to convert value ‘22.22’ to type ‘System.Int32’ for field ‘Number’ in Entity (External Content Type) ‘Entity1’, EntityNamespace ‘BDCValidationTest.BDCModel’. Details: Input string was not in a correct format.]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.InsertCallback(Int32 affectedRecords, Exception ex) +23690584
   System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +4052324
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +378
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

Another example, if you type a value that does not fit into the size of the .NET type you used in you model and entity. In this case I declared the type as byte, but entered the number 256:

Server Error in ‘/’ Application.
——————————————————————————–

Value was either too large or too small for an unsigned byte.
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.OverflowException: Value was either too large or too small for an unsigned byte.

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:

[OverflowException: Value was either too large or too small for an unsigned byte.]
   System.Byte.Parse(String s, NumberStyles style, NumberFormatInfo info) +4386590
   System.String.System.IConvertible.ToByte(IFormatProvider provider) +55
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +10246009
   Microsoft.SharePoint.BdcClientUtil.ChangeType(Object value, Type destinationType, IFormatProvider culture) +478
   Microsoft.SharePoint.SPListDataSource.FillOneFieldValue(SPField fld, IView view, String bdcfldnm, Object objRawFieldValue, IFieldValueDictionary dictFields) +536

[SPException: Failed to convert value ‘256’ to type ‘System.Byte’ for field ‘Byte’ in Entity (External Content Type) ‘Entity1’, EntityNamespace ‘BDCValidationTest.BDCModel’. Details: Value was either too large or too small for an unsigned byte.]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.UpdateCallback(Int32 affectedRecords, Exception ex) +23690873
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4052212
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +312
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

Finally, another strange behavior in SharePoint lists, not limited to the external lists only. If you have to specify the value of a numerical field, you can type a number containing any number of digit grouping symbols in any order (like “3,,,,2” that will be interpreted as 32). The only exception is that placing it into the first position (like “,32”) is not allowed.

After this experience, I’m thinking about how validation issues can be handled in the case of external lists to make them a reliable tool that can be used even for business applications, not only in presentation rooms.

Advertisements

7 Comments »

  1. My team struggled mightily to figure out a sane validation story when using external lists. We never found any supported way to do it, or even a hacky way. Microsoft has no documentation on this subject, like they didn’t foresee at all that people would perhaps want to validate data like in any other real-world application. WTF were they thinking?

    Comment by Matt Blodgett — February 2, 2011 @ 21:41

  2. Using Visual Studio to generate a BDCM file, a developer can define the behavior of the save function(s). While I’ve not attempted much validation, I am confident that Visual Studio would offer the solution you’re looking for. It can easily check values before the save process is initiated, so the only unknown is how the error message would be processed.

    Further, while it has limited applications (taylored more to handle lookup values), BCS validation can be accomplished through ECT Associations in Visual Studio. The association processing automatically does data type validation, while also offering value lookup capabilities – all OOB.

    My point (before I compose another post with questions about BDCM issues) is that you shouldn’t give up hope.

    Comment by Keith Hamilton — May 13, 2011 @ 19:12

    • Hi Keith,

      I don’t give up hope, simply think there is not such support in the BCS architecture that would make it possible out of the box. I wish I were wrong. I would be interested how do you solve issues like SQL unique, foreign or check constraint or even more complex validation requirements, or custom business logic implemented in C# assemblies using ECT assosiations or BDCM model. Could you provide a sample for that? For example, assuming a direct SQL database connection, how do you plan to define the behavior of the save function to validate data? Thanks!

      Peter

      Comment by Peter Holpar — May 14, 2011 @ 20:51

  3. Visual Studio 2010. C#. Sharepoint 2010.

    I’ve created and deployed an External Content Type with Associations. All of the CRUD methods I’ve implemented are working correctly. Here’s my problem – in the View Item, Edit Item and New Item forms, I would like the fields to appear in a specific order, with the associated TypeDescriptors mixed in with the “normal” bound items. I had them ordered correctly before I created the associations. After the associations were implemented through the Visual Studio BDCM Toolbox, the associated items now appear at the end. When I look at the raw XML in the BDCM, I can see that the TypeDescriptors in my ReadItem method are in the order I intend, with the association information contained in the definition for each. I’m convinced that those TypeDescriptor nodes are being skipped though, as they aren’t in that order and the DefaultDisplayName for each is not utilized, while the associated item names are used instead.

    Is there something I’m overlooking here – someplace where I can change the order of fields on my forms?

    Thanks in advance for your help.

    Comment by Keith Hamilton — May 13, 2011 @ 19:15

    • Hi Keith,

      Have you tried to create custom InfoPath forms?

      Peter

      Comment by Peter Holpar — May 14, 2011 @ 20:53

      • Thanks for the response, Peter. I’ve not yet tried InfoPath. We’ve currently got a working web solution and the user wanted validation through associations added. As I mentioned, I have the solution working in my Dev environment, except that I can’t get the ordering issue resolved. I was hoping to deploy a solution without adding another layer of complexity.

        InfoPath may be something we explore in the future, but it’s not currently something we’re utilizing. Do you know if it can handle the ordering of association fields interspersed with other fields?

        Comment by Keith Hamilton — May 16, 2011 @ 13:23

  4. You can make validation by creating custom field types (BCS supports this). Or you can implement your validation in javascript on the form it self, this is supported too. You can read about the custom field types here: http://www.hezser.de/blog/archive/2010/05/17/using-a-custom-field-type-in-a-bcs-model.aspx
    But yes I do agree, validation out of the box is too limited.

    Comment by Simon Pedersen — November 29, 2011 @ 22:59


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: