On a recent project, we showed our customer how we could provide pop-up search windows, similar to what was in the customer's System i RPG application. The search windows are used to display just a few data elements in a natural sort order to make it easy for the user to locate the required item.

An example of the pop-up is shown in Figure 1 [2]. This uses the DataGridView control that is included in the Visual Studio Toolbox. One of the neat features of the DataGridView is that column sorting is built into the control.

For example, you can click the Code or Description headings to reverse the sort order of the data in the column. Providing multiple search orders in the equivalent RPG applications required at least two logical files, one for each search.

After admiring the new pop-up for a few minutes, our customer remarked that it would be nice to have enhanced search features to make it easier to work with long lists of codes. He suggested the "Begins with" and "Contains" options that are shown in Figure 1 [3]. When you select the "Begins with" option, you can start typing in the Search box.

As shown in Figure 2 [4], as you type, the list of items shrinks, displaying only those items with the starting letters (in this application, the starting letters are compared with the description column, and the search letters are not case sensitive).

Figure 3 [5] shows the "Contains" option. This feature also narrows the list of items that are displayed as you type each letter.

Because the DataGridView is loaded with data when the form is loaded, the selection occurs in memory. As you type search letters, the grid is instantly updated. If you press the backspace key, you immediately see more items added back to the grid.

After you locate the item you want, you can click either the code or the description to select it. You can also click the mouse on an empty space within a cell to position the cursor and then use the arrow up and arrow down keys to navigate through the list. You can press Enter to select the item that currently has the cursor.

The Example Program

Figure 4 [6] is the example code for the form (you can get all the required files to include into a Visual Basic project by downloading this zip file [7]). To make it easy for you to try out the program, I've removed the database connection code and substituted an embedded dataset table that is created entirely within the program. You should be able to load and run the example code without needing to make any connections.

The program starts with class level variables at Section A. The rowFilter is used to contain the selection criteria, built as the user enters or changes the search term. The ds variable contains the dataset that is used in several of the subroutines in the program.

At Section B, the program creates the "in memory" data table. To connect to a database, remove all the code in this section that creates the rows. Use whatever connection and SQL statement you need to fill the dataset variable ds.

It is important to note that the SQL statement is only run once per invocation of the program. By loading the data only once, the performance of the search features is greatly enhanced, compared with dynamically querying the database as the search term changes.

The only downside to using this load technique is that data that is added to the database won't show up until the user exits the pop-up and reloads it. For most code lookup situations, this is not a factor because most code tables tend to be relatively static after they are loaded into the database.

If it is important for your application to have realtime dynamic data, you can reload the data as the search term changes, but you will have a dramatically worse experience with the search performance.

Sections C, D, and E simply move the data from the dataset into the grid. The reason we split these subroutines into such small sections is that we anticipate being able to generate this code in the future. By separating the functions, it will be far easier to develop the code generation feature.

Section F handles a row selection. It retrieves the values from the selected row and prepares them for return to the caller.

In this example application, a MessageBox is displayed so that you can see the values selected. In a real application, you would not display the values in the MessageBox but would simply return them to the caller of the search program.

Section G handles the Cancel button; it simply closes the program and returns. Sections H and I handle mouse click and key press events within the grid.

Where the Action Is

The real action of the program occurs in Sections J, K, and L.

In Section J, the subroutine is called each time the text is changed in the Search text field. For every character you type or every press of the backspace key, this subroutine is called.

When it is called, it takes the current value of the search term and trims it. It then determines which of the search options is currently in effect. Based on the search option, a "filter" string is created.

If you look closely at the code, you see that the filter strings are similar to SQL WHERE clauses. For a "begins with" search using the search value "san," the filter that is created looks like this:

ACDSLN like SAN%.

When you use the "contains" search for "en," the filter looks like this:

ACDSLN like %EN%.

After constructing the filter, the FormatView subroutine at Section E is called. In that subroutine, a view is created based on the dataset table. In this sense of the word, the "view" is like an SQL view over a database table.

When initially created, the view contains all the rows in the table. Immediately after creating the view, the filter, if it is not empty, is applied to the view. Following that, the DataGridView is refilled with only the qualifying rows that are in the view. Because the data is in memory, this process appears to happen instantaneously.

The code in Sections K and L changes the selection option. If you have a search term in effect and you change the option, the search immediately changes to show data for the selection.

Load 'em Up

Given the example, it should be easy for you to adapt it for your own needs. When you want to work with different data, simply change the fields you get from the database and the column names used in the DataGridView. Also remember to change the column that is used in the filter (Section J). Apart from those small adjustments, the rest of the code should work.

Craig Pelkie has worked as a programmer with IBM midrange computers for many years. He has also written and lectured extensively on AS/400 and System i technologies, including client/server programming, Client Access, Java WebSphere, .NET applications for the System i, and web development. You can reach him at cpelkie@systeminetwork.com [8].