Getting a DataTable from SQL Server using a JOIN where I can updat

[Guest]

I currently have a SQL join which combines 2 tables. I then update only one
of the tables with new information. While this works easily in ADO, I am
having trouble figuring out how to do this in ADO.NET. I first tried to do
an update using a SQL JOIN DataSet, but that gave me an error, I then tried
to create 2 different DataTables (1 from each table) and adding a
DataRelation, but I need to produce a DataTable that combines the two with
the columns in a specific order. I want to pass this DataTable to a method
that will then display the columns in that order without the method having to
know that I have 2 DataTables. Any ideas?

 

[David Browne]

I currently have a SQL join which combines 2 tables. I then update only
one
of the tables with new information. While this works easily in ADO, I am
having trouble figuring out how to do this in ADO.NET. I first tried to
do
an update using a SQL JOIN DataSet, but that gave me an error, I then
tried
to create 2 different DataTables (1 from each table) and adding a
DataRelation, but I need to produce a DataTable that combines the two with
the columns in a specific order. I want to pass this DataTable to a
method
that will then display the columns in that order without the method having
to
know that I have 2 DataTables. Any ideas?

You just need to adjust your DataAdapter.UpdateCommand to target the correct
table.

David

 

[William \(Bill\) Vaughn]

ADO.NET is not nearly as "smart" as ADO classic but it's a lot more
flexible.
The problem you're facing is that the update logic is in the DataAdapter and
the DataAdapter.Update method is designed to point at a single table. One
approach is to setup the action commands (UpdateCommand, InsertCommand,
DeleteCommand) to point to the single table you wish to update--even though
the SelectCommand returns columns from a JOINed product. The trick is to
create these action commands yourself--ADO.NET's CommandBuilder won't know
how. Many of us build these manually anyway to deal with more sophisticated
collision management.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________