I have 2 tables with unique IDs that can be mapped to each other (one table comes from a database outside of my application but has information about the same entities I'm tracking). I want to provide a UI for linking "my" records with the "other" ones, so I can store the "other" IDs in my table for the matching records. In a nutshell, the key UI element is a dropdownlist showing the available (ie, not-yet-linked) records from the "other" table.
I have a gridview for displaying "my" records in each row, plus a column showing some info from the linked record in the "other" table, if any. The data are about buildings and properties, so it might look like this:
Building1 Dallas TX Building1_Dallas_TX
Building2 Memphis TN Bldg2_Memphis_TN
Building3 Denver CO
Building4 Seattle WA
Building5 Boston MA Building5_Boston_MA
where the first 3 columns show some information from "my" table, and the last one shows the matching record from the "other" table, where the records have already been linked, else a blank where no link has been established. My actual gridview has several more columns from "my" table, but this is enough to illustrate the need.
When Edit (not shown) is clicked for a row, I want to be able to edit all 4 columns. The first 3 are textboxes; the last column should be a dropdownlist showing all of the unlinked records from the "other" table, plus an "Unassigned" option (as the first item in the list) so records can be "unlinked" or simply left unassigned, plus (for those rows that already have a link established) the linked item (it's "used" but it needs to be in there so they can continue to use it).
I can populate the dropdownlist correctly in code-behind in the gridview RowEditing event (except for the Unassigned item which I add as a ListItem in .aspx with the AppendDataBoundItems option set to true), and I can set the correct SelectedValue on the ddl's DataBound event, but on postback when I click to Update the row, the ddl has no items in it anymore (except for the Unassigned item) so the link is always cleared. If I set a link manually by directly editing my table, everything looks fine going into Edit mode -- the ddl has the correct items in it and it pre-selects the correct item, but all is lost on the Update postback. I have tried re-binding the ddl at various stages of the page cycle but clearly I don't quite understand that cycle well enough yet because I cannot figure out how to get the user-selected item to update my table.
I've also tried populating the ddl with a SqlDataSource SelectCommand, but have had no luck including the ID of currently-linked records in the other database (even though it's available in "my" table and I set it as a GridView DataKey and add it as a ControlParameter in the SelectParameters for the SqlDataSource. Without the currently-linked record in the ddl item list, I can't keep existing links.
I'll hold off sending a code sample for now -- this message is already too long! -- in hopes that the solution is obvious from what I've described... I don't think my aim is weird, but I'm open to being persuaded otherwise.
Many thanks in advance!
Chris