Ross Mack - GUI Computing
The more programmers you work with, the more you see a diversity of idiosyncrasies. Some like to do things one way and will argue for that until they change colour; others will only accept the opposite. It seems that many are bound to disagree, except in theory on very broad topics. For example, everyone seems to agree that code should be maintainable, but exactly how you achieve that is highly debatable. One of my personal beefs is the use of Data Controls. I just don't like them, and I don't like to use them.
Ever since I first started writing database code in Visual Basic I have found that for anything above the simplistic, they are more trouble than they are worth. Of course they allow you to get a form up and running very quickly, but the writing of validation and error trapping code becomes a little more scrappy (in my opinion). For this reason I think that they are perfect for putting together prototypes and quick demos - most basic things will work happily. The other place I use Data Controls is to provide a recordset for a bound grid (usually TrueGrid for VB3 or TrueDBGrid for VB4). In this case they are read-only and they suit the purpose very well.
Anyway, enough complaints about Data Controls (before I get into resource and speed issues). Let's get to the point. I have seen many people use Data Controls prodigiously to provide data for drop-down ComboBoxes, and alike. This means using a third party custom control for the ListBox. Unfortunately this is very inefficient, and some of those custom controls will themselves present problems - incompatibilities with other controls you are using, slowness and redraw problems are just some hassles I have seen.
Having seen enough of this sort of inefficiency and hassles, I looked at my own code (without data controls) and discovered that I was using almost the same code everywhere that I needed to populate a ListBox or ComboBox. Realising this, I decided to kill two birds with the one stone (or at least injure them) and put the code into an easy-to-use library that allows you to 'bind' data to a data control with simplicity rivaling bound controls - although the binding is done in code.
Binding a simple list of data to the ListBox (or ComboBox) is done with a single call:
BindListSimple ctlListBox, dbDatabase, sTableName, sField
In this call we pass the actual control we want to populate as ctlListBox, an open database object as dbDatabase, the name of the table to get the data from as sTableName, and the name of the field to display in the ListBox as sField. The function then does the grunt work of the population using some standard code. The next thing you know the ListBox or ComboBox is fully populated, with all the data in the specified field of the specified table.
Here is a sample call that will work with the BIBLIO.MDB distributed with VB.
BindListSimple List1, dbBiblio, "Authors", "Author"
Now that's handy, but there are situations that it does not deal with. It only handles the case where you are purely interested in the data to be displayed. Let's say, for example, that you want to display a list of data but actually store its related IDs. An example might be a list of countries and their associated international dialing codes, or just a counter field on a reference table. In this case you need to populate the Combo, not only with the displayed data but also with a list of related IDs. VB allows you to store an array of Long Integers that correspond to list items, in an array property called ItemData. This is actually a facility provided by Windows itself, with all ListBox and ComboBox controls which are exposed by VB. We have the ability to populate this data - using these functions with a slight modification in the call - to include the name of the field to place into the ItemData array property.
BindList ctlListBox, dbDatabase, sTableName, sIDField, sDisplayField
In this case, the second last parameter is the name of the field to place in the ItemData array, and the last parameter is the name the field is to actually display. To simplify matters, and allow you to use any field names or even valid SQL expressions, the functions use aliasing in the SQL expressions they create. Consider the following two examples of the BindList code (again designed to work with Biblio).
BindList List1, dbBiblio, "Authors", "Au_ID", "Author" BindList List1, dbBiblio, "Authors", "Au_ID", "CStr(Au_ID) & '-' & Author"
To retrieve the ID of the selected item, use the following code to access the correct entry in the ItemData array:
lSelItem = List1.ItemData(List1.ListIndex)
Of course, you should check that the list actually has a current selection before you use that code - otherwise you will get an error. If a field name referenced includes spaces or other special characters you will need to enclose the field name in square braces. Here is an example:
BindList List1, dbSomething, "People", "[People ID]", "[Phone Number]"
Obviously the same goes for table names. In fact tables names need not actually be tables. QueryDefs that return recordsets can be used in the same manner, however there is no way to pass parameters for queries. It was this hiccup that lead me to develop one more version of the function which provides the most flexibility - by allowing you to pass a valid SQL where clause as the last parameter, sort of like the parameter of the FindFirst method. It looks like this when in use:
BindList List1, dbBiblio, "Authors", "Au_ID", "Author", "Author Like 'a*'"
This call will populate the ListBox called List1, with all the Author names from the Authors table where the name starts with an 'a'. With the ability to generate this where clause easily as a string at run time, you can very easily dynamically load data into your forms based upon previous user actions, or whatever criteria is necessary.
|The code behind BindList looks like this, the comments should tell the story.|
I have also included some extra functions for working with ListBoxes that could be used outside of the BindList approach. Mostly they are just wrapper functions for selecting and searching ListBoxes or ComboBoxes for data. A brief description for each is linked below.
What I have tried to do here is provide a means of removing the overheads involved with using custom controls for bound List or ComboBoxes. These involve overheads in speed, memory and size of files required to be distributed. While these functions don't do any fancy caching, this should not be required for the small amount of data you would normally want to display in ListBoxes anyway. Apart from that, this code is quite efficient - particularly for searching through the list contents for particular items. It also has the advantage of using standard controls and standard VB code with a couple of simple APIs, there should be no conflicts, redraw problems or similar hassles. I have now used this code in a couple of projects and find it to work quite well, I hope you find the same. If you have any suggestions about how it might be expanded please let me know.
The downloadable zip file, bindlib.zip (11 KB), includes both the library and a simple demonstration program which shows examples of most of the core functions of the library.