0

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?

Improve this question
0

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

2 Comments

The reason that I'm using the active/inactive flag is that (just for example) last week my dropdown includes "Cat", "Dog" and "Fish" and records could be saved with any of these options, but from this week I don't sell "Fish" anymore, so I mark it as inactive so that it's not appearing in the dropdown but I still need to be able to reference it for my old data (hence keeping the foreign key relationship). In my page, the DropDownLists are databound before the record is loaded, so I think this may be where I'm going wrong?
Just a thought, presumably any records which have a foreign key to Fish will be old data (i.e legacy) so you will not really want to make any changes to it? So why not just bring back the selected value only? Either way if you want to make it efficient I would start by binding the dropdown after the record is loaded.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.