Select from one datasource update another

Question

I am using Visual Web Developer to create a form updating my database. Currently, I fill a gridview based on two drop down lists querying against a view in my SQL 2008 database. This works fine, but I cannot update a view directly, so what I need to do is update the main table that supports the view. So here is the question(s). Can I have a select statement that says

SELECT * FROM [vw_GridviewSource] WHERE (([Annotation Date] = @Annotation_Date) AND ([Name] = @Name))

And have an update to another table?

Also can I somehow only allow the user to update certain fields and not others? I have an instance where the "Annotation Number" is actually generated by my client and should not be changed, but I do want them to be able to update notes, business unit etc.

Answer 1

I have difficulty in understanding your question[s].

Also can I somehow only allow the user to update certain fields and not others?

Short answer yes. Following example grant UPDATE on Name column and deny UPDATE on OrganizationNode column for User007.

GRANT SELECT ON dbo.Department TO User007;
GRANT UPDATE (Name) ON dbo.Department TO User007;
DENY UPDATE (OrganizationNode) ON dbo.Department TO User007;
GO

Now you can connect using this user and execute following statements:

PRINT 'SELECT >';
SELECT  *
FROM    dbo.Department;

PRINT 'UPDATE #1 >';
UPDATE  dbo.Department
SET     Name = NULL;

PRINT 'UPDATE #2 >';
UPDATE  dbo.Department
SET     OrganizationNode = NULL;
PRINT 'End of Test';
GO

Results:

SELECT >
DepartmentID Name                                               OrganizationNode
------------ -------------------------------------------------- ------------------------------------------------------------

UPDATE #1 >
UPDATE #2 >
Msg 230, Level 14, State 1, Line 13
The UPDATE permission was denied on the column 'OrganizationNode' of the object 'Department", database 'Test', schema 'dbo'.