CODEWITHSUNDEEP
viewvb.net-2010tableadapter
Yaroslav
Yaroslav

Reputation: 6544

update sql view using tableadapter in vbnet 2010

I have a table adapter that gets filled with a simple: SELECT * FROM [view]

I try to update the table adapter or add a new row and nothing happens. I have lot of table adapters but all get filled with one single table so updates are not a problem. But now using a view to get the data I can not do the updates/inserts.

Any idea?

Upvotes: 1

Views: 2851

Answers (1)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

If view is created on multiple tables or have aggregate function or having order by clause or union clause then it means the view is not updatable view. It will not support Insert/Delete/Update on this view.

You have join between two table in view, so it is not updatable view. A view is updatabale only if it's created only on single table.

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:

  • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
  • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.

The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses. TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. For more information, see Modifying Data Through a View.

If the previous restrictions prevent you from modifying data directly through a view, consider the following options:

INSTEAD OF Triggers

INSTEAD OF triggers can be created on a view to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger lets the user specify the set of actions that must happen to process the data modification statement. Therefore, if an INSTEAD OF trigger exists for a view on a specific data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement.

Partitioned Views

If the view is a partitioned view, the view is updatable, subject to certain restrictions. When it is needed, the Database Engine distinguishes local partitioned views as the views in which all participating tables and the view are on the same instance of SQL Server, and distributed partitioned views as the views in which at least one of the tables in the view resides on a different or remote server.

Refer this MSDN link for more details

Upvotes: 1

Related Questions