TextBox AutoComplete from MDB

I have a .Net 4 Windows Forms app that uses a Microsoft Access Database with one table which has three columns CityCode, Name and Country.

What I want to do is have an autocomplete which shows the “Name” and “Country” but when selected the “CityCode” Value is shown in the textbox. In addition if the user types A City Code eg LAX as they type L it would list all the cities whose code or Name starts with L.

Can this be done?

Currently I have the following for access the database (but it seems to be a bit slow!)

textBoxCity.AutoCompleteCustomSource = CityList(); 

public static AutoCompleteStringCollection CityList()
    {
        string connectionStringLD = string.Empty;

            connectionStringLD = @"Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\CityList.mdb";


        string SQL = "SELECT CityCode from CityTable";

        OdbcConnection conn = new OdbcConnection(connectionStringLD);

        OdbcCommand cmd = new OdbcCommand(SQL);
        cmd.Connection = conn;

        conn.Open();

        OdbcDataReader reader = cmd.ExecuteReader();
        AutoCompleteStringCollection theCityList = new AutoCompleteStringCollection();

        while (reader.Read())   
        {

            theCityList.Add(reade开发者_Python百科r.GetValue(0).ToString());

        }

        return theCityList;
    }

You can use Like '%' Query in your Sql Statement which will return the city name based on your input.

You can Refer this example Sql Parameter with C# Using Like wildCards

I'm not sure what you're getting at with

What I want to do is have an autocomplete which shows the “Name” and “Country” but when selected the “CityCode” Value is shown in the textbox.

but I can answer the autocomplete part of your question.

To do this you need to get your data into a DataTable; you can read it from the database into the table however you want, but the Right Way to do it is to use OleDbConnection, OleDbDataAdapter, and OleDbCommandBuilder - msdn has examples.

Now that it's in a DataTable, bind it to a ComboBox:

var query = 
    from row in mytable.AsEnumerable()
    select new { citycode = row.Field<string>("CityCode") } // put whatever you want in the anonymous type

mycombobox.DisplayMember = "citycode"
mycombobox.ValueMember = "citycode" // this one can be a different member name
mycombobox.DataSource = query.toList(); // the datasource should be set last

And now you can set the combo box behavior to be an autocomplete:

• combobox1.AutoCompleteMode can be set to Append (to simply autocomplete), Suggest (to bring up a dropdown box), or SuggestAppend (both)
• Set combobox1.AutoCompleteSource to ListItems to have it get the autocomplete entries from the data binding.

If you prefer to allow users to type whatever they want and only suggest your datatable values as options, then you should only set the AutoCompleteCustomSource and not worry about actual data binding with DataSource.

This is kind of all a lot of trouble; since cities aren't exactly going to be opening international airports several times a second you might prefer just dumping all the airport codes into a List, which you can also data bind to.