View in 2005 says cell is read only

Question

Post reply

View in 2005 says cell is read only

Danster

SSCommitted

Points: 1632
More actions
April 30, 2007 at 10:07 pm

#123679

Hi,
As a certified DBA in 2000 I've done hundreds of views in Enterprise
Manager. I've just started to get my teeth into 2005.
As one of my first forays, I thought I'd whip up a typical view. No
problems, but when I execute, the results pane say 'cell is read only'.
I'm used to directly editing within the results pane in Enterprise Manager
(yeah, I know...I'm careful) but in SSMS the view doesn't allow me to edit
data. This is essential for the kind of stuff I do. What gives? This
particular view is just three tables joined, plain old select statement with
a column or two from each table. No criteria.
This question was originally posted on the ms news server but so far have not had any decent response after a few months. Surely this is a common question? Basically I'm just after the same editing functionality in Management Studio which I had in EM. Note, I want to edit a VIEW from the GUI, in the results pane, not edit a table, nor edit the results from a query window.
cheers
(This was also posted in another forum on this site - just wanted to cover my bases).

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

DickBaker Old Hand Points: 334 More actions · Answer 1

your view should surface all the relevant PK's so the view can hit the appropriate base table.

but you might be interested in this experiment [same for TSQL and in SSMS UI]

create

table T1

( IDA int not null primary key

, descrip1 varchar(99)

)

create

table T2

( IDB int identity(1,1) not null primary key

, descrip2 varchar(99)

)

create

table T3

( IDA int not null foreign key references T1(IDA)

, IDB int not null foreign key references T2(IDB)

, descrip3 varchar(99)

)

go

create

view v1 as

select T1.*, T2.*, T3.descrip3

from T1

join T3 on T3.IDA=T1.IDA

join T2 on T2.IDB=T3.IDB

go

insert

into T1 values (1, 'this is NOT identity table')

insert into T2(descrip2) values ('this IS not identity table')

insert into T3 values(1,scope_identity(), 'some join table')

go

-- #1 fails Msg 4405 ... is not updatable because .. affects multiple base tables

update V1 set

descrip1='XXX'

, descrip2='YYY'

, descrip3='ZZZ'

where IDA=1

and IDB=(select min(IDB) from T2)

go

-- #2 but individually it all works OK

update V1 set

descrip1='XXX'

where IDA=1

and IDB=(select min(IDB) from T2)

go

update

V1 set

descrip2='YYY'

where IDA=1

and IDB=(select min(IDB) from T2)

go

update

V1 set

descrip3='ZZZ'

where IDA=1

and IDB=(select min(IDB) from T2)

go

select

* from V1

Danster SSCommitted Points: 1632 More actions · Answer 2

Thanks Dick,

Although you say that you could update individually, that's not really what I'm asking.

If I create a view, I need to be able to edit the results, not through query code, but through the results pane in the GUI.

Anyone? This is my biggest problem so far moving from 2000 to 2005.

cheers

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 3

Dick wrote

your view should surface all the relevant PK's so the view can hit the appropriate base table.

but you might be interested in this experiment [same for TSQL and in SSMS UI]

I think Dick got the point. Run the code and try afterwood in SSMS GUI to edit the results....

Danster SSCommitted Points: 1632 More actions · Answer 4

I shouldn't need the IDB=(select min(IDB) from T2) clause

Like in EM, I should be able to graphically link the tables as appropriate, run the view. A few records will display in the results pane which should be editable.

Using the example from Dick, my view would read

select T1.*, T2.*, T3.descrip3

from T1

join T3 on T3.IDA=T1.IDA

join T2 on T2.IDB=T3.IDB

I just run it, and edit any old field I need. That's the functionality I need in SSMS which Microsoft in their wisdom have destroyed. Surely this is common? Am I the only one out there who edits data like this?

cheers

rdemotsis SSC Enthusiast Points: 102 More actions · Answer 5

Editing data directly to the table using the SSMS has always been frowned upon from most of the DBAs I know. Being able to control the transaction commit or rollback is the biggest advantage in using TSQL instead.

Danster SSCommitted Points: 1632 More actions · Answer 6

Sure direct editing may be frowned upon. Side issue.

Still looking for a solution!

rdemotsis SSC Enthusiast Points: 102 More actions · Answer 7

While in the view designer, open the properties window and change the update specification to yes. Once you save your view, you should be able to open it and directly edit it.

For further info regarding the entire process to do this go to http://msdn2.microsoft.com/en-us/library/f5scy1hs(VS.71).aspx or search for updateable view in SQL BOL

Danster SSCommitted Points: 1632 More actions · Answer 8

Hi,

I thought this might've been the solution! But alas, changes to the view designer's properties didn't help.

And the link refers to VisualFoxPro, not SSMS.

rdemotsis SSC Enthusiast Points: 102 More actions · Answer 9

I had success in the sample I created joining three tables from the Adventure Works DB. Employee, EmployeeAddress, and EmployeePayRate and successfully updated the data in the view.

BTW - I also caught that reference to Visual Fox Pro in the top left corner as well, interesting isn't it?

Good luck in your efforts.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 10

Views have to meet some specific criteria to be updatable. For a quick test, try updating a record wih a SQL statement rather than using the SSMS grid. If you cannot, look in the books online about updatable views and make sure your view meets all of the criteria.