|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ADO.NET Update SQL ViewI have created a view in SQL server 2000 that joins two one-to-one
tables. This view is updateable from SQL enerprise manager as all of the keys are present to perform updates. In VB6, I could simply create an ADO Recordset that queries this view, make the recordset optimistic, and updates would be performed automatically when the .Update method was invoked. However, in ADO.NET this appears to be impossible. I have created a stored procedure that queries this view and bound the resulting dataset to a grid. MY issue is I cannot make this dataset updateable. Obviously, the command builder will not generate the Update statement because there is a table join. I have tried creating a stored procedure fot the update but am receiving an error - which gives me no indication as to what the actual problem is. I cannot create two adaptors because I need to bind he dataset to a single grid. What is the solution? It seems as though ADO.NET is a huge step backward from ADO. Any help would be greatly appreciated One of the most common mistakes we all have to deal with on a daily basis is
this: thinking that ADO.NET should behave like ADO classic. ;) In ADO classic, the UPDATE SQL action commands are created using a runtime engine that analyzes the SELECT query and after several editions (about 5) it figured out how to update Views and other JOINed products. In ADO.NET there is no runtime engine to do this. You can choose to use the CommandBuilder (as the design-time tools do) to generate the UpdateCommand but it's pitiful when compared to the logic in ADO classic. The team at Microsoft expects you to build your own SQL UpdateCommand and other action commands for this type of problem. If you really want to update the View, you can write your own UPDATE and past it into the UpdateCommand.CommandText and setup the Parameters collection. Not trivial, but also doable. I discuss this in my book on ADO.NET. hth -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ <graigcrawf***@yahoo.com> wrote in message news:1136349115.778293.165840@g49g2000cwa.googlegroups.com... >I have created a view in SQL server 2000 that joins two one-to-one > tables. This view is updateable from SQL enerprise manager as all of > the keys are present to perform updates. In VB6, I could simply create > an ADO Recordset that queries this view, make the recordset optimistic, > and updates would be performed automatically when the .Update method > was invoked. However, in ADO.NET this appears to be impossible. I > have created a stored procedure that queries this view and bound the > resulting dataset to a grid. MY issue is I cannot make this dataset > updateable. Obviously, the command builder will not generate the > Update statement because there is a table join. I have tried creating > a stored procedure fot the update but am receiving an error - which > gives me no indication as to what the actual problem is. I cannot > create two adaptors because I need to bind he dataset to a single grid. > What is the solution? It seems as though ADO.NET is a huge step > backward from ADO. Any help would be greatly appreciated > Bill,
Thanks for the response. Unfortunately I have tried writing a Stored procedure for my update - and I receive a very helpful system.data.dll error which tells me absolutely nothing about the source of the problem. I setup the Update command of my adaptor so that the stroed procedure parameters map to each field in my dataset. The update command works fine when I only update one of the two tables used in the view - but when I try to update a field from each table - here comes the system.data.dll error. Is there anything I can try because I am ready to scrap .NET and return to VB6 simply for the data access benefits. What's the error? Did you trap it with an exception handler?
-- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ <graigcrawf***@yahoo.com> wrote in message news:1136355568.130592.220730@g49g2000cwa.googlegroups.com... > Bill, > > Thanks for the response. Unfortunately I have tried writing a Stored > procedure for my update - and I receive a very helpful system.data.dll > error which tells me absolutely nothing about the source of the > problem. I setup the Update command of my adaptor so that the stroed > procedure parameters map to each field in my dataset. The update > command works fine when I only update one of the two tables used in the > view - but when I try to update a field from each table - here comes > the system.data.dll error. Is there anything I can try because I am > ready to scrap .NET and return to VB6 simply for the data access > benefits. > Bill,
Thanks for the response. Unfortunately I have tried writing a Stored procedure for my update - and I receive a very helpful system.data.dll error which tells me absolutely nothing about the source of the problem. I setup the Update command of my adaptor so that the stroed procedure parameters map to each field in my dataset. The update command works fine when I only update one of the two tables used in the view - but when I try to update a field from each table - here comes the system.data.dll error. Is there anything I can try because I am ready to scrap .NET and return to VB6 simply for the data access benefits.  |
|||||||||||||||||||||||
Other interesting topics