Tuesday, March 3, 2009

The data source control failed to execute the update command

I created a SharePoint list with one of the fields being a lookup to a field in another SharePoint list. The user wanted this DDL to be filtered. No problem. I can do that. So I created my filtered lookup (good instruction but not supported well). When I tried to update an item or insert a new item using the filtered lookup I received the error: The data source control failed to execute the update command. I found that the Microsoft Office Online website actually gave some instruction about this error:

From Insert a Data View as a Form at Microsoft Office Online:
"However, if your data source is an SQL data source such as an SQL database or a SharePoint list or library, the field types may be specified in the data source itself. In such a case, if you use the form to enter text in a field that requires numbers and then click Save, an error message appears in the browser explaining that the data source control failed to execute the update command. This means that you are entering values in the form that the data source field cannot accept. If you receive such an error message, click Back in the browser, and then either click Cancel on the form to discard your changes, or enter values in the form fields that the data source can accept."

Basically, I had to go back into my SharePoint list and delete that lookup column and re-create the column as a single line text type column. This did solve the problem. Unfortunately, this column, in its current state, only saves the text of the option value in the filtered lookup. Be warned: this column no longer saves the link to view the information of the lookup item. This was no great loss for my users and this solution allowed me to store the necessary information I needed in that column. By the way, no changes were needed to the filtered lookup on the NewForm.aspx or EditForm.aspx pages after this change to the SharePoint list.