CODEWITHSUNDEEP
asp.netdrop-down-menuselected
triplestones
triplestones

Reputation: 393

ASP.NET DropDownList - How do I handle missing values?

I have a list of values in a SQL Table which are used to popluate a DropDownList, having a unique Integer as the value of each item and a String as the visible text (via SqlDataSource). There is also a third field in the database which is a flag to indicate whether the list item is active or not (inactive items are not shown in the DropDownList)

Selections made in the dropdown are stored in the database as their integer value (as part of a dataset making up the overall record), not the text value.

Over time, the items in the DropDownList may be removed by marking the items as inactive. However, there is still a need to open old records which may have had a now-inactive item as part of it's data...

My question is, what's the best way to ensure that the missing value included in the dropdown for the old record?

The two methods that spring to mind are to either:

Populate DropDownList with only the currently active items and, when loading a record, catch when the app tries to select a value that doesn't exist, go back to the db to see what it should be (the text value) and manually add it into the dropdown.

or...

Populate DropDownList with all list items (both active and inactive), load the record and then programatically remove all the inactive items (execpt for any that are now selected).

Neither of these seem particularly efficient, so I was wondering whether there is a best practice for this kind of thing?

Upvotes: 0

Views: 2168

Answers (2)

IuR
IuR

Reputation: 176

there are so many optimum ways to do that sort of things, i am defining here a couple of them, use any of following if your Drop down list items count is less than 200 , lets say drop down list is of Products

1)
      i) Load all Products records in drop down list and hide the inactive ones by setting visible=false
      i) When you load a user record than look for its drop down list value if its visible than select it and enjoy, if its not visible than make it visible by setting its property visible=true and select it and also set its index or id in a flag to change its visibility(visible=false) again after your/users required operation performed.

2)
      i) load only active Product records in drop down list
      ii) while loading a user record also load its product details(name, id, inactive_status) using Joins in sql.
      iii) check in that user record if item is inactive then add its record in drop down list as you have all its details now with user details record else just select it.

IMPORTANT NOTE: if you drop down list has items in millions than use ADVANCE SEARCH techniques

Upvotes: 1

Kevin Main
Kevin Main

Reputation: 2344

The first thing I would do is question your business logic - should you be able to make an item inactive if it is being used as a foreign key in an active row elsewhere? If you make it inactive should it not remove all foreign keys as well?

To answer your question though I would go with a variation on the second idea but filtering in the page like that is probably slower than doing directly with SQL so I guess you have something like this at the moment to populate the dropdown

SELECT * FROM Table WHERE Active = 1

You should already have your record and the foreign key value so I would change it to this

SELECT * FROM Table WHERE Active = 1 OR PrimaryKey = [YourForeignKey]

Then you will always have the selected item but should also be fairly efficient.

Upvotes: 0

Related Questions