Updating a view
-
ADO.NET offers a great functionality which makes it very easy to implement databinding (two directional - read and write). Let`s assume that we have a DataSet with some simple DataTable tied with TableAdapter. Here is what we have to do: 1) Create DataTable with the standard GetData method from the Adapter 2) Assign this datatable as binding source for our datagridview on UI 3) When user makes some changes in the datagridview, it is enough to call 'Update' method on the Adapter to transfer all changes into database. This is very easy and fast to implement. However it does not work when our DataTable contains data from more than 1 physical tables from database (any joins in select clause). Why is it impossible? The update method on tableAdapter does not generate at all. How to solve this? Thank you very much for any help!
-
ADO.NET offers a great functionality which makes it very easy to implement databinding (two directional - read and write). Let`s assume that we have a DataSet with some simple DataTable tied with TableAdapter. Here is what we have to do: 1) Create DataTable with the standard GetData method from the Adapter 2) Assign this datatable as binding source for our datagridview on UI 3) When user makes some changes in the datagridview, it is enough to call 'Update' method on the Adapter to transfer all changes into database. This is very easy and fast to implement. However it does not work when our DataTable contains data from more than 1 physical tables from database (any joins in select clause). Why is it impossible? The update method on tableAdapter does not generate at all. How to solve this? Thank you very much for any help!
Haven't really used TableAdapter so I'm not able to say why it's not genereting all the methods (my guess is that the generator thinks that the view isn't updatable). On the server side there are strict rules when the view can be updated:
-
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.
So you may have to use
VIEW_METADATAoption on the view orINSTEAD OFtriggers. Hope this helps, MikaThe need to optimize rises from a bad design. My articles[^]
-
-
ADO.NET offers a great functionality which makes it very easy to implement databinding (two directional - read and write). Let`s assume that we have a DataSet with some simple DataTable tied with TableAdapter. Here is what we have to do: 1) Create DataTable with the standard GetData method from the Adapter 2) Assign this datatable as binding source for our datagridview on UI 3) When user makes some changes in the datagridview, it is enough to call 'Update' method on the Adapter to transfer all changes into database. This is very easy and fast to implement. However it does not work when our DataTable contains data from more than 1 physical tables from database (any joins in select clause). Why is it impossible? The update method on tableAdapter does not generate at all. How to solve this? Thank you very much for any help!
I guess the table adapter will have some
UpdateCommandproperty that you need to manually set. Hope that helps. Regard, Syed Mehroz AlamMy Blog My Articles Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein