DataSet & DataGrid with joined tables

[Marc S. Gibian]

I am working with a very typical pair of tables in a SQL Server
database, one is a detail table (details) , the other a mapping table
(mapping). I have successfully populated my dataset using:

"SELECT mappingID, detailID, detailText FROM details, mapping WHERE
details.detailID = mapping.detailID"

I've successfully bound this dataset to a web form datagrid. I am now
attempting to implement an add function. I click on my add button,
which attempts to add an empty row to the mapping table and then offer
a dropdownlist to the user of the available details.

My code is not quite working and I am unable to find an examples of
adding or modifying a dataset when handling data from a join such as
the one above. I am currently getting an error that indicates that I'm
updating the dataset itself, but it is generating incorrect SQL to
insert that data into the underlying database. The problem statement
is:

DataAdapter.Update(DataSet, "TableNameOfMap");

The resulting error indicates that SQL is being generated to insert
into my detail table when my only modification to my dataset was an
add to the map table. I had thought the arguments to Update would do
the job, but apparently not. I am sure I am close, but I just can't
come up with the search to find the clue to solve this problem.

Thanks in advance for your help,
Marc

 

[William \(Bill\) Vaughn]

I drone on and on about this in my book.
The problem is, the DataAdapter is designed to work with a single table--not
the product of a JOIN. It's up to you to code the UpdateCommand etc. so you
can decide how the Update method works but generally, we use a separate
DataAdapter for each table--even if the control is bound to the product of a
JOIN.

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