handling lookup tables with deleted records and databound controls

Question

I have a table Resource with a field Type. Type is a lookup into the table ResourceType.

So for instance ResourceType might have: 1: Books 2: Candy 3: Both

And Resource might have 1: Tom's Grocery, 2 2: Freds News, 3

It would display as: Tom's Grocery Candy

Now lets say I am using a databound combobox for the resource type and the third record is deleted from ResourceType, we of course get an error when Fred's News is displayed. I could simply put a marker in (perhaps an asterisk), indicating that it has been deleted, rather than actually delete it. It shows up as *Both in the textbox portion of the combo and I am content.

However, I would not want it to show up as an option in the dropdown. Is this too much to ask from databound fields? Must I write my own code to load the drop down?

Answer 1

Add a bit Deleted column to the lookup table. When you delete a type, set Deleted = 1. When you pull back ResourceTypes, only pull out ResourceTypes where Deleted = 0 and then bind to the dropdown.

Edit: How are you getting the dataset that you're binding to the dropdownlist? Are you using drag and drop datasets? I really haven't worked with datasets like that in years, but I'm pretty sure you can change the Get sql to what you need it to be.

Answer 2

OK, I already have most of that built into the solution - but I must be binding wrong. If I only pull back records where deleted = 0, I get exceptions - I'm guessing because the textbox can't be filled from the record set.

I guess I'm not sure how to bind the textbox display to one dataset - the one with all the records - and the dropdown to a filtered dataset.

Answer 3

Simply adding a deleted column was not enough - there had to be a way to see a deleted record in the text portion of a combo box while at the same time filtering out deleted records in the drop down.

In the end, I wrote a custom user control to handle this.