|
This article demonstrates the use of the AutoCompleteCustomSource and AutoCompleteMode in Visual Studio 2005 to suggest a list of possible values for a textbox control from a table in a database. This functionality is similar to the AutoComplete function of URLs in a browser.
The code is very straight forward and I feel it is almost a waste dedicating a whole article to this topic. However, I have found this functionality quite useful for displaying a list of account names (which I permanently keep in memory) for an accounting package. You can also do the same thing within a ToolStripTextBox (on your application's toolbar).
As a result of pure laziness I have used the same project used in a previous article
Using the DataGridViewComboBoxColumn to display a dropdown list in C# (Visual Studio 2005)
I have commented the code used for the autocomplete functionality in a block named "AutoComplete code". If you don't understand any of the rest of the code, please refer to the above article where the rest of the code is explained.
Downloads
Visual Studio 2005 (Beta) project (Excludes the database) - 23KB
MS Access database - 8KB
Download and unzip the project file into your Visual Studio projects directory. Then unzip the MS Access file into the bin\debug directory of your new project. Open the project in Visual Studio and run it.
MS Access Database
I will be using an MS Access database as datasource. For simplicity's sake, I place the Access database in the BIN\DEBUG directory of my project - My Documents\Visual Studio\Projects\DataGridView-Sample\DataGridView-Sample\bin\Debug, where "DataGridView-Sample" is the name of my Visual Studio C# project.
You may download the database from the Downloads section above or you can recreate it with the following 2 tables.
If you create the database yourself, make sure you have some values in both tables. In the Titles tables, add "Mr", "Ms", "Mrs" and "Dr". In the People table, Add a couple of names and use any of the values in Titles in the PTitle column. We're not doing any validation on loading the data, so yes... the application will crash if one of the values in PTitle are not found in the Titles table.
| People | |
|---|---|
| PersonID | AutoNumber |
| PTitle | Text(5) |
| PName | Text(50) |
| Titles | |
| Title | Text(5) |
Creating the application
As I mentioned before, I am pretty lazy and it is 2:44AM on a Sunday morning as I'm writing this. So... I'm not going to go through everything required to re-create the application. If you have trouble understanding any of the code, please refer to the previous article
Using the DataGridViewComboBoxColumn to display a dropdown list in C# (Visual Studio 2005)
Basically that article describes how to display data from an MS Access table in a DataGridView and how to display values from another table as a Dropdown list in one of the columns. The reason I used that project is because it already has everything to get data from a database into a dataset, ds1. So all that was left to do was to add a textbox control and use the values from the dataset to populate the AutoComplete values for the textbox.
So, let's run through the code. I'm assuming that you have either downloaded the code for this article and you're simply going to look at what I've done, or you have downloaded the code for the previous article and you're going to extend it with the new code for AutoComplete.
Add a textbox control named txtAutoComplete to your form and set the following properties:
AutoCompleteMode = SuggestAppend
This will "Suggest" values in a dropdown below the textbox AND "Append" the remaining characters of the first matching string found as you type.
AutoCompleteSource = CustomSource
This says that you will specify an AutoCompleteCustomSource to use for matching values.
Next go to the code view of the Form.
Click on the + sign next to "Using Directives" at the top of your code and add the following lines to the bottom of the list of using directives:
using System.Collections;
using System.Collections.Specialized;
These are required to work with the AutoCompleteStringCollection, which will hold all possible values for the textbox as contained in the dataset, ds1.
Next search for "this.dataGridView1.Columns[0].Visible = false;" in the Form1_Load event and add the following block of code below it.
As you can see, the code is very straight forward. We declare a StringCollection variable to hold all lookup values. Then we loop through the dataset and add the names from the database into the collection and finally we assign the collection to the AutoCompleteCustomSource property of our textbox.
That's all there is to it! Now run the application and click on the textbox. Start typing the first few characters of one of the names in the Peoples table in the textbox (Dirk, if you downloaded the database). As you can see, the textbox displays a dropdown of all names that match what you have typed. Pretty boring, but I still think it makes life a little easier for my users.
About the author
Dirk Wessels is a software developer and owner of www.getquotes-it.com. You may redistribute this article, but please keep this “About the author” section within the article together with an active link to www.getquotes-it.com