Can Views add new record?

Question

Post reply

Can Views add new record?

rghertne

Grasshopper

Points: 11
More actions
February 5, 2008 at 2:23 pm

#71360

Hi
I'm new at SQL Server, having most experience in Access and am developing an Access front end. I'm using a View for my recordsource of a form, and I noticed I cannot add a new record. Do views not allow adding new records?
Robin

Viewing 9 posts - 1 through 8 (of 8 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Yes, but unlike Access, the view must contain one table only and have an identity column.

N 56°04'39.16"
E 12°55'05.25"

Susantha Bathige Ten Centuries Points: 1040 More actions · Answer 2

A view is a virtusal table in SQL Server. A views are updateble. But there are several conditions applied.

1. View must be derived from one base table.

2. Coulmns of the view can not be derived such as a result of an aggregate function or computation columns.

There is no matter about the IDENTITY property or not.

refer BOL (Books on line) for more details.

Susantha

Krishna Reddy-295451 SSC-Addicted Points: 452 More actions · Answer 3

Hi

Check the following link for more information.

http://msdn2.microsoft.com/en-us/library/ms180800.aspx

Thanks,

Krishna

rghertne Grasshopper Points: 11 More actions · Answer 4

rghertne

Grasshopper

Points: 11

February 6, 2008 at 7:17 am

#775570

Thanks everyone! The link is just what I need.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Susantha Bathige (2/6/2008)
There is no matter about the IDENTITY property or not.

If you want to update the view with SSMS there is.

It is not a matter if you want to update through code as you wrote.

CREATE TABLE tA (i INT, j INT IDENTITY(1, 1))

GO

CREATE VIEW vA

AS

SELECT i FROM tA

GO

CREATE VIEW vA2

AS

SELECT i, j FROM tA

GO

INSERTvA

SELECT1 UNION ALL

SELECT2 UNION ALL

SELECT3

GO

UPDATEvA

SETi = 4

WHEREi = 1

GO

UPDATEvA2

SETi = 9

WHEREi = 4

GO

SELECT*

FROMtA

GO

Now try to update view vA with SSMS and edit back one of the 9 values to 1.

You will get an error.

Now try to update view vA2 with SSMS and edit back one of the 9 values to 1.

You will not get an error.

N 56°04'39.16"
E 12°55'05.25"

rghertne Grasshopper Points: 11 More actions · Answer 6

Hmm... I think I get it.

I figured out what my problem was, including the identity column you mentioned and including it in my INSERT statement. However, what I really need to do is be able to update values in my Access form using the View as its recordset. My View is based off of two tables. Here's the SQL its derived from:

SELECT tblComms.ID, tblComms.InitID, tblComms.Title, tblComms.ProjectID,

tblComms.StartDate, tblComms.Type, tblComms.Status, tblComms.TargetDate,

tblComms.SubUnit, tblComms.ProdEst, tblComms.ProdCost,

tblCC.Estimate AS CCEst, tblCC.FinalCost AS CCCost, tblComms.PrintCost,

tblComms.PrintEst FROM tblComms LEFT JOIN tblCC ON tblComms.ID=tblCC.ID;

Is there a way to change this so that it will be updateable? Do I need to add the identity column from tblCC?

R

J.Pridmore SSC Veteran Points: 208 More actions · Answer 7

Hi there,

Contrary to many of the posts above, it is possible to update multiple tables from one view.

Check out "instead of" triggers that you place on the view.

These allow you to fine tune what happens when you try and insert a record into a view.

Have a look at http://msdn2.microsoft.com/en-us/library/ms175089.aspx

Hope this helps!

dbajunior SSCrazy Points: 2307 More actions · Answer 8

Just a comment....

A lot of good info here...particularly the urls...thanks to everyone involved