Post reply

Can Views add new record?

  • 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

  • February 5, 2008 at 2:38 pm

    #775344

    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"

  • February 6, 2008 at 12:45 am

    #775450

    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

  • February 6, 2008 at 1:05 am

    #775452

    Hi

    Check the following link for more information.

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

    Thanks,

    Krishna

  • February 6, 2008 at 7:17 am

    #775570

    Thanks everyone! The link is just what I need.

  • February 6, 2008 at 7:37 am

    #775585

    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

    INSERT vA

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3

    GO

    UPDATE vA

    SET i = 4

    WHERE i = 1

    GO

    UPDATE vA2

    SET i = 9

    WHERE i = 4

    GO

    SELECT *

    FROM tA

    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"

  • February 6, 2008 at 8:12 am

    #775614

    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

  • February 7, 2008 at 2:54 am

    #775916

    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!

  • February 7, 2008 at 9:51 am

    #776082

    Just a comment....

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

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

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