Implement AutoComplete TextBox using business class in Windows Application

Hi PRA,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespaces

C#

using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Data.SqlClient

AutoComplete Class

C#

class AutoCompleteClass
{
    public DataTable ListPeople()
    {
        string conString = @"Data Source =.; Initial Catalog = Northwind; UID=sa;PWD=pass@123";
        string query = "SELECT DISTINCT ContactName FROM Customers";
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Connection = con;
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);

                        return dt;
                    }
                }
            }
        }
    }
}

VB.Net

Public Class AutoCompleteClass
    Public Function ListPeople() As DataTable
        Dim conString As String = "Data Source=.; Initial Catalog=Northwind; UID=sa;PWD=pass@123"
        Dim query As String = "SELECT DISTINCT ContactName FROM Customers"
        Using con As SqlConnection = New SqlConnection(conString)
            Using cmd As SqlCommand = New SqlCommand(query)
                cmd.Connection = con
                Using sda As SqlDataAdapter = New SqlDataAdapter()
                    sda.SelectCommand = cmd
                    Using dt As DataTable = New DataTable()
                        sda.Fill(dt)

                        Return dt
                    End Using
                End Using
            End Using
        End Using
    End Function
End Class

Code

C#

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        AutoCompleteClass ac = new AutoCompleteClass();
        DataTable dt = ac.ListPeople();
        AutoCompleteStringCollection autoCompleteStringCollection = new AutoCompleteStringCollection();
        foreach (DataRow dr in dt.Rows)
        {
            autoCompleteStringCollection.Add(dr["ContactName"].ToString());
        }

        txtName.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
        txtName.AutoCompleteSource = AutoCompleteSource.CustomSource;
        txtName.AutoCompleteCustomSource = autoCompleteStringCollection;
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
        MessageBox.Show("Customer Name : " + txtName.Text.Trim());
    }
}

VB.Net

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim ac As AutoCompleteClass = New AutoCompleteClass()
        Dim dt As DataTable = ac.ListPeople()
        Dim autoCompleteStringCollection As AutoCompleteStringCollection = New AutoCompleteStringCollection()
        For Each dr As DataRow In dt.Rows
            autoCompleteStringCollection.Add(dr("ContactName").ToString())
        Next

        txtName.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        txtName.AutoCompleteSource = AutoCompleteSource.CustomSource
        txtName.AutoCompleteCustomSource = autoCompleteStringCollection
    End Sub

    Private Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click
        MessageBox.Show("Customer Name : " & txtName.Text.Trim())
    End Sub
End Class

Screenshot