Thursday, November 13, 2008

Using Dataviews to edit and update SQL data

If you hooked up your dataviews with SQL data and enable editing you most probably have come across following error message:
The data source control failed to execute the update command

The reason for this can be manyfold, either you might have a data type issue where you have non numerical data being pushed into a numerical field or in my case as simple as a database constraint getting in your way.
Dealing with the select, update, insert and delete commands manually can be a bit of a pain. So I really like the ability to pick my table and let SharePoint Designer worry about the rest. Just a shame that SharePoint designer does not understand one simple rule. You can not update a column which is the identity column of a table. SQL server reserves the right to auto increment that column. How to fix it? two ways:

The simple way:
Start with a blank Data View and make sure not to add the ID column to the list.

What if you want to show the id though?
The hard way:
same as above but you then you add the ID not by using the Edit Columns... task but simply by adding a new column to the dataview itself (click into first column of the DataView, right click and choose Insert...Column to the left) and injecting the ID manually. Easiset way to do that is to copy one of the other fields and then changing its databinding to point to the ID field.


Nyssa and Mike said...

Have you noticed that if you add a new field to a SQL data list page the UPDATE command is not updated automatically, you have to manually go in and change the UPDATE statement in the code.

ALSO the UPDATE statemment consists of a WHERE statement with loads of AND ... AND ... AND .. statements.
Just edit this to update the table using the UniqueID field..

alx said...

When you specify to manually create the statements you will also have to manually update them when you change the fields. When you tick the generate queries automatically it tends to do it for you. But yeah, the syntax is pretty ugly that it generates. (and sometimes does not even work in edit mode)