ADO.NET Update SQL View

[graigcrawford]

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

 

[William \(Bill\) Vaughn]

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

--
____________________________________
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.
__________________________________

 

[graigcrawford]

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.

 

[graigcrawford]

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.

 

[William \(Bill\) Vaughn]

What's the error? Did you trap it with an exception handler?

--
____________________________________
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.
__________________________________