How to handle a large drop-down list?

[Paul W]

Hi - I'm developing a 'front-desk' app. to be used over the internet.
(SQL-Server back-end).

One of the pages will require the user to select an entry from a large
(~4000) list of 'member names'.

What are the options to reduce the bandwidth load of this? Is there any way
that I can cache the list on the client (and allow them to periodically
'refresh' their local copy?). Or, does anyone have some code for a 'manual'
auto-select option where the user can enter the start of the name (eg.
"Wri") into a textbox and the list will be populated with all matching
names?

The list will be called upon many times a day and is relatively static (~1-2
changes a day).

Thanks for any and all suggestions,

Paul.

 

[Marina]

First off, please, under any circumstances, populate a dropdown list with
4000 items. This will be completely useless to the end user.

One way you can cache something like this, is to put the items in a list in
a javascript file. So store it in an array, whatever. Include the js file in
your .aspx, and pull the list from there. The beauty of js files, is that
every time you open a new browser instance and browse to the page, the
browser checks for a newer version of the js file. Of course, there is no
way to force the client to get a newer file other then having them do what I
described.

This would mean, that you probably will need to manually maintain this js
file, or have a process that knows how to reconstruct it based on what is in
SQL server periodically. Maybe whatever lets you add members, would then
make sure this file is updated as well.

The filtering you want, is not something going to happen on its own. You
will need to write javascript that will be able to search through the list
of people and filter it, and change the contents of the dropdown. Depending
on how you are storing this data on the client, this can potentially cause a
noticeable delay.

I would recommend having a dialog lookup, which allows a user to filter/do a
search on member, select a member. Selecting a member would close the dialog
and populate the field value on the main page with the selection.

 

[c.verma]

If the items in SQL server keep on adding or chaning, for eg 4000 can
become 5000 and so on, then I would suggest to save the data in cache
and reterive it from cache.
When the page loads, you can get this data from SQL server and
reterive it in a DataSet. Then you can save this dataset in cache. You
can set the time of the cache for 10 minutes or 20 minutes. Whenever
the cache is empty, the page will try to get data from SQL server and
will referesh the dataset with new data. Hope that makes sense..here is
the eg:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
If Not (IsPostBack) Then
BuildTable()
End If
End Sub

Private Sub BuildTable()
Dim myCacheData As New DataSet()
myCacheData = GetCacheData()
myDropDownList.DataSource = myCacheData.Tables(0)
myDropDownList.DataTextField = "Column1 '
myDropDownList.DataValueField = "Column2
myDropDownList.DataBind()
End Sub

Private Function GetCacheData() As DataSet
Dim blnResults As Boolean

Dim ds As DataSet = CType(Cache.Get("CodeList "), DataSet)
If ds Is Nothing Then
ds= New DataSet()
'Fill the dataset. Below is the ex from my code..
'blnResults = myServices.GetCode(ds)' In this line
basically fill ur ds with 4000 records from SQl server
If blnResults = True Then
Cache.Insert("CodeList", ds, Nothing,
DateTime.Now.AddMinutes(10), TimeSpan.Zero)
End If
End If
GetCacheData = ds
End Function

 

[MWells]

Also consider;

+ Making your list hierarchical and using a treeview. More manageable, and
with some work you can make the data load on expansion of a node. More
round trips, but a lot less data per trip. Of course, your data has to be
hierarchically organizable, e.g. by member classification, when they joined,
by location, or some other criteria that is intuitive and useful.

+ Using filters. Marina's partial-text match is a good example; but for
'member names' an A-Z bar could also provide advantages.