StrataFrame Forum

Can a BO use a view, that joins two tables, for updates

http://forum.strataframe.net/Topic9236.aspx

By Peter Denton - 5/31/2007

G'day

I'm using a business object in a grid to update an SQL Server 2005 table, but I've based the BO on a view which joins two tables because I want one column from the other table for grouping and sorting the grid. I get an exception when I edit a record and save:

"View or function 'dbo.vw_MyJoinView' is not updatable because the modification affects multiple base tables."

I'm looking for a bit of guidance here, is what I'm attempting here possible (or sensible), or do I give up and find some other means of achieving what I'm after?

Peter 

By Trent L. Taylor - 6/1/2007

If you are going to use a view, then you have to make an updateable view.  You will also need to open the BO Mapper and manually override the primary key field and specify a primary key field.  It gets more complicated (whether using SF or not) when you start to create updateable views because there are just more things that need to be set.  But what you are attempting is possible, as to whether or not it is the best approach just depends on your end goal.  Another thing you can do is just create a regular query (SELECT statement) that pulls in all of the fields that you need.  THen on the BO you can set the fields that do not need to be updates or inserted in the FieldsToExcludeFromInsert or the FieldsToExcludeFromUpdate properties.  This way you achieve the same functionality without going through a view and complicating your INSERTs and UPDATEs.
By Peter Denton - 6/1/2007

Thanks Trent,

I'd figured out the override of the primary key, but your suggestion of using the FieldsToExcludeFromInsert / FieldsToExcludeFromUpdate properties sounds much easier. I'll give it a go.

Peter

By Trent L. Taylor - 6/1/2007

Good deal Smile