Description
This article shows a simple method to load names from a MS-Access database table into the AutoComplete source for a TextBox. One of the reasons for this, a operator doing data entries perhaps for placing customer orders. You could start off with a simple list or someone can create a list of names perhaps from US census database. Any names that are not in the list can be added to the database table using the code shown in the attached project.

 

To start off there are two tables in the database, one for first name and one for gender. The table with names has the following columns/fields, identifier as our primary key (you should always have a primary key for any table), FirstName for name and gender (Female/Male) where in today's world there could be more possibilities, I will let you figure this out on your own.

 

In the demonstration project I query only for female names, if we wanted male and female we would simple remove the where condition.        

 

In the form load event we setup the TextBox to use a custom source which is populated by reading values from our names table in the MS-Access database. A DataGridView is populated so you can see the underlying names that are in the custom source for the AutoCompleteCustomSource for the TextBox (this I believe is helpful for learning purposes).

 

Once the form is displayed you can type in a name that as you type if there is a match or several matches they are shown in a drop down for the TextBox. If you type in a name that does not exists in the list (we use the Contains method to determine if the name exists in the current list) we add the name to the list using the Add method and set the first character to upper case as it is generally a rule of thumb to format a name this way. This is triggered by pressing ENTER key while in the TextBox. There are countless method to add for adding via mouse operations but I wanted to keep this demonstration fairly clean and easy to understand.

 

In the form closing event a procedure is called which determines if from the list one or more names are not currently in the database table, if they are not these names are added to a List(Of String). Once all the names are checked if the List(Of String) has names they are added to the names database table.

 

There is a button which shows how someone might remove a name from the auto complete list and the underlying database table although I would think this would not be something commonly done yet it shows you how this can be done if needed.

 

2/15/2014
Added basic example is C# based off the VB.NET version

3/14/2014 Added more options in the VB.NET projoject.

3/22/2014 Added another example to the VB.NET project.

Core code
 
Visual Basic
Edit|Remove
Module DataAccess 
    Private Builder As New OleDb.OleDbConnectionStringBuilder With 
        { 
            .Provider = "Microsoft.ACE.OLEDB.12.0", 
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb") 
        } 
    ''' <summary> 
    ''' Used to remove the current item selected in the txtFirstName text box. 
    ''' </summary> 
    ''' <param name="Name"></param> 
    ''' <remarks></remarks> 
    Public Sub RemmoveFemale(ByVal Name As String) 
        Using cn As New OleDb.OleDbConnection With 
                { 
                    .ConnectionString = Builder.ConnectionString 
                } 
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn} 
                cmd.CommandText = 
                    <SQL> 
                        DELETE FROM FirstNames WHERE FirstName = @FirstName 
                    </SQL>.Value 
 
                cmd.Parameters.Add(New OleDb.OleDbParameter With 
                                   { 
                                       .DbType = DbType.String, 
                                       .ParameterName = "@FirstName", 
                                       .Value = Name 
                                   } 
                               ) 
 
                cn.Open() 
                Dim Affected As Int32 = cmd.ExecuteNonQuery 
 
            End Using 
        End Using 
    End Sub 
    ''' <summary> 
    ''' Called in Form1 on FormClosing event to update the database table if 
    ''' needed. 
    ''' </summary> 
    ''' <param name="sender"></param> 
    ''' <remarks></remarks> 
    Public Sub UpdateFemaleNames(ByVal sender As AutoCompleteStringCollection) 
        Dim NewNames As New List(Of String) 
 
        Using cn As New OleDb.OleDbConnection With 
                { 
                    .ConnectionString = Builder.ConnectionString 
                } 
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn} 
                cmd.CommandText = 
                    <SQL> 
                        SELECT FirstName 
                        FROM FirstNames 
                        WHERE FirstName = @FirstName 
                    </SQL>.Value 
 
                cmd.Parameters.Add( 
                    New OleDb.OleDbParameter With 
                    { 
                        .DbType = DbType.String, 
                        .ParameterName = "@FirstName" 
                    } 
                ) 
 
                cn.Open() 
 
                For x As Int32 = 0 To sender.Count - 1 
                    cmd.Parameters("@FirstName").Value = sender.Item(x) 
                    Dim Result As String = CStr(cmd.ExecuteScalar) 
                    If String.IsNullOrWhiteSpace(Result) Then 
                        NewNames.Add(sender.Item(x)) 
                    End If 
                Next 
 
                If NewNames.Count > 0 Then 
                    cmd.CommandText = 
                        <SQL> 
                                INSERT INTO FirstNames  
                                (FirstName,Gender)  
                                VALUES (@FirstName,@Gender) 
                            </SQL>.Value 
 
                    cmd.Parameters.Add( 
                        New OleDb.OleDbParameter With 
                        { 
                            .DbType = DbType.String, 
                            .ParameterName = "@Gender", 
                            .Value = "Female" 
                        } 
                    ) 
 
                    For Each Item In NewNames 
                        cmd.Parameters("@FirstName").Value = Item 
                        cmd.ExecuteReader() 
                    Next 
 
                End If 
 
            End Using 
        End Using 
    End Sub 
    ''' <summary> 
    ''' Used in Form1 DataGridView1 for learning purposes only 
    ''' </summary> 
    ''' <returns></returns> 
    ''' <remarks></remarks> 
    Public Function AllFemaleNames() As DataTable 
        Dim dt As New DataTable 
        Using cn As New OleDb.OleDbConnection With 
                { 
                    .ConnectionString = Builder.ConnectionString 
                } 
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn} 
                cmd.CommandText = 
                    <SQL> 
                        SELECT Identifier, FirstName 
                        FROM FirstNames 
                        WHERE Gender = 'Female' 
                        ORDER BY FirstName; 
                    </SQL>.Value 
 
                cn.Open() 
 
                dt.Load(cmd.ExecuteReader) 
 
            End Using 
        End Using 
 
        Return dt 
    End Function 
    ''' <summary> 
    ''' Load only female first names into the auto complete source 
    ''' </summary> 
    ''' <returns></returns> 
    ''' <remarks></remarks> 
    Public Function LoadFemaleNames() As AutoCompleteStringCollection 
        Dim TheNameList As New AutoCompleteStringCollection 
 
 
        Using cn As New OleDb.OleDbConnection With 
                { 
                    .ConnectionString = Builder.ConnectionString 
                } 
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn} 
                cmd.CommandText = 
                    <SQL> 
                        SELECT FirstName 
                        FROM FirstNames 
                        WHERE Gender = 'Female' 
                        ORDER BY FirstName; 
                    </SQL>.Value 
 
                cn.Open() 
                Dim Reader As OleDb.OleDbDataReader = cmd.ExecuteReader 
 
                If Reader.HasRows Then 
                    While Reader.Read 
                        TheNameList.Add(Reader.GetString(0)) 
                    End While 
 
                    Reader.Close() 
 
                End If 
 
            End Using 
        End Using 
 
        Return TheNameList 
 
    End Function 
 
End Module 
 
 
Screenshots
Names table
 
 
 Showing removal of an item
 C# Code for loading data 
C#
Edit|Remove
using System; 
using System.Data; 
using System.Data.OleDb; 
using System.Windows.Forms; 
 
public class DataAccess 
{ 
    private OleDbConnectionStringBuilder Builder = 
        new OleDbConnectionStringBuilder 
            { 
                Provider = "Microsoft.ACE.OLEDB.12.0", 
                DataSource = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb") 
            }; 
 
    public DataTable AllFemaleNames() 
    { 
        DataTable dt = new DataTable(); 
        using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString }) 
        { 
            using (OleDbCommand cmd = new OleDbCommand { Connection = cn }) 
            { 
                cmd.CommandText = 
                    @" 
                    SELECT Identifier, FirstName 
                    FROM FirstNames 
                    WHERE Gender = 'Female' 
                    ORDER BY FirstName"; 
                cn.Open(); 
                dt.Load(cmd.ExecuteReader()); 
            } 
        } 
        return dt; 
    } 
 
    public AutoCompleteStringCollection LoadFemaleNames() 
    { 
        AutoCompleteStringCollection TheNameList = new AutoCompleteStringCollection(); 
 
        using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString }) 
        { 
            using (OleDbCommand cmd = new OleDbCommand { Connection = cn }) 
            { 
                cmd.CommandText = 
                @" 
                    SELECT FirstName 
                    FROM FirstNames 
                    WHERE Gender = 'Female' 
                    ORDER BY FirstName; 
                    "; 
 
                cn.Open(); 
                OleDbDataReader Reader = (OleDbDataReader)cmd.ExecuteReader(); 
 
                if (Reader.HasRows) 
                { 
                    while (Reader.Read()) 
                    { 
                        TheNameList.Add(Reader.GetString(0)); 
                    } 
                    Reader.Close(); 
                } 
            } 
        } 
        return TheNameList; 
    } 
}
 Form code for C project to load data and setup auto-complete.
....
C#
Edit|Remove
using System; 
using System.Windows.Forms; 
 
namespace AutoComplete_C_Version 
{ 
    public partial class Form1 : Form 
    { 
        public Form1() 
        { 
            InitializeComponent(); 
        } 
 
        private void Form1_Load(object sender, EventArgs e) 
        { 
            DataAccess da = new DataAccess(); 
            txtFirstName.AutoCompleteMode = AutoCompleteMode.SuggestAppend; 
            txtFirstName.AutoCompleteSource = AutoCompleteSource.CustomSource; 
            txtFirstName.AutoCompleteCustomSource = da.LoadFemaleNames(); 
            ComboBox1.DisplayMember = "FirstName"; 
            ComboBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend; 
            ComboBox1.AutoCompleteSource = AutoCompleteSource.CustomSource; 
            ComboBox1.DataSource = da.LoadFemaleNames(); 
            ComboBox1.AutoCompleteCustomSource = da.LoadFemaleNames(); 
            dataGridView1.DataSource = da.AllFemaleNames(); 
        } 
        private void cmdClose_Click(object sender, EventArgs e) 
        { 
            this.Close(); 
        } 
    } 
}