VB.NET Programming - How to Auto-Complete textbox from access 2013 database.

Visual Basic .NET Programming - How to Auto-Complete Textbox from Access 2013 Database.

🎓 Master Visual Basic .NET and Access Database By Building the Point Of Sale System (POS).

📲 Enroll Now: https://bit.ly/2WcbRhX

[Screenshot-1]

[Screenshot-2]

[Screenshot-3]

[Screenshot-4]

[Screenshot-5]

(Part 1)

Part 1 : VB.Net Source Code

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Private con As New OleDb.OleDbConnection
    Private command As OleDb.OleDbCommand
    Private adapter As OleDbDataAdapter
    Private reader As OleDbDataReader
    Private dataSt As DataSet
    Private sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ibk As String = "(demo by ibasskung.)"
        Me.Text = "Auto-Complete TextBox from Access 2013 Database." & " - " & ibk
        Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath + "\Customers.accdb"
        With con
            If .State = ConnectionState.Closed Then
                .ConnectionString = strCon
                .Open()
            End If
        End With
        cboOptions.DropDownStyle = ComboBoxStyle.DropDownList
        Dim itms As String() = {"Any Part", "Match Prefix", "Match Suffix", "Whole Word"}
        cboOptions.Items.AddRange(itms)
        cboOptions.SelectedIndex = 0 '-- Any Part
        AutoComplete()
    End Sub

    Private Sub AutoComplete()
        sql = "SELECT CustomerName FROM Customers"
        command = New OleDbCommand(sql, con)
        reader = command.ExecuteReader()
        Dim autoComp As New AutoCompleteStringCollection()
        While reader.Read()
            autoComp.Add(reader("CustomerName"))
        End While
        reader.Close()
        txtSearch.AutoCompleteMode = AutoCompleteMode.Suggest
        txtSearch.AutoCompleteSource = AutoCompleteSource.CustomSource
        txtSearch.AutoCompleteCustomSource = autoComp
    End Sub

    Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        With con
            If .State = ConnectionState.Open Then
                .Close()
            End If
        End With
    End Sub

End Class

'End Part 1

Part 2/3 : YouTube Video

Part 3/3 : YouTube Video

Part 1 & 2 : VB.Net Source Code

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Private con As New OleDb.OleDbConnection
    Private command As OleDb.OleDbCommand
    Private adapter As OleDbDataAdapter
    Private reader As OleDbDataReader
    Private dataSt As DataSet
    Private sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ibk As String = "(demo by ibasskung.)"
        Me.Text = "Auto-Complete TextBox from Access 2013 Database." & " - " & ibk
        Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath + "\Customers.accdb"
        With con
            If .State = ConnectionState.Closed Then
                .ConnectionString = strCon
                .Open()
            End If
        End With

        With DataGridView1
            .RowHeadersVisible = False
            .SelectionMode = DataGridViewSelectionMode.RowHeaderSelect
            .MultiSelect = False
            .ReadOnly = True
        End With

        txtCount.ReadOnly = True

        cboOptions.DropDownStyle = ComboBoxStyle.DropDownList
        cboOrder.DropDownStyle = ComboBoxStyle.DropDownList

        Dim itms As String() = {"Any Part", "Match Prefix", "Match Suffix", "Whole Word"}
        cboOptions.Items.AddRange(itms)
        cboOptions.SelectedIndex = 0 '-- Any Part

        Dim orderItms As String() = {"Customer ID", "Customer Name", "Job Title"}
        cboOrder.Items.AddRange(orderItms)
        cboOrder.SelectedIndex = 0 '-- Customer ID

        CheckDefault.Checked = True
        CheckDefault.Enabled = False

        AutoComplete()
    End Sub

    Private Sub AutoComplete()
        sql = "SELECT CustomerName FROM Customers"
        command = New OleDbCommand(sql, con)
        reader = command.ExecuteReader()
        Dim autoComp As New AutoCompleteStringCollection()
        While reader.Read()
            autoComp.Add(reader("CustomerName"))
        End While
        reader.Close()
        txtSearch.AutoCompleteMode = AutoCompleteMode.Suggest
        txtSearch.AutoCompleteSource = AutoCompleteSource.CustomSource
        txtSearch.AutoCompleteCustomSource = autoComp
    End Sub

    Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        With con
            If .State = ConnectionState.Open Then
                .Close()
            End If
        End With
    End Sub

    Private Sub LoadData()
        sql = "SELECT * FROM Customers "
        ColumnOrdering()
        command = New OleDbCommand(sql, con)
        adapter = New OleDbDataAdapter(command)
        dataSt = New DataSet()
        adapter.Fill(dataSt, "Customers")
        DataGridView1.DataSource = dataSt.Tables("Customers")
        txtCount.Text = DataGridView1.RowCount.ToString - 1
        txtSearch.Focus()
    End Sub

    Private Sub ColumnOrdering()
        If cboOrder.SelectedIndex = 0 Then
            sql &= "ORDER BY [Customer ID]"
        ElseIf cboOrder.SelectedIndex = 1 Then
            sql &= "ORDER BY [CustomerName]"
        ElseIf cboOrder.SelectedIndex = 2 Then
            sql &= "ORDER BY [Job Title]"
        End If
    End Sub

    Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadData.Click
        LoadData()
    End Sub

    Private Sub CheckShowAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckShowAll.CheckedChanged
        If CheckShowAll.Checked Then
            CheckPhoneFax.Checked = True
            CheckAddressCity.Checked = True
        Else
            CheckPhoneFax.Checked = False
            CheckAddressCity.Checked = False
        End If
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        If String.IsNullOrEmpty(txtSearch.Text) Then
            txtSearch.Focus()
            Exit Sub
        End If

        sql = "SELECT [Customer ID], CustomerName, [Job Title] "

        If CheckPhoneFax.Checked Then
            sql &= ", [Business Phone], [Fax Number] "
        End If

        If CheckAddressCity.Checked Then
            sql &= ", Address, City "
        End If

        If CheckShowAll.Checked Then
            sql &= ", [State/Province], [ZIP/Postal Code], [Country/Region] "
        End If

        sql &= "FROM Customers WHERE CustomerName LIKE "

        Select Case cboOptions.SelectedItem
            Case "Any Part" : sql &= "'%" & txtSearch.Text & "%'"
            Case "Match Prefix" : sql &= "'" & txtSearch.Text & "%'"
            Case "Match Suffix" : sql &= "'%" & txtSearch.Text & "'"
            Case "Whole Word" : sql &= "'" & txtSearch.Text & "'"
        End Select

        ColumnOrdering()

        command = New OleDbCommand(sql, con)
        adapter = New OleDbDataAdapter(command)
        dataSt = New DataSet()
        adapter.Fill(dataSt, "Customers")
        DataGridView1.DataSource = dataSt.Tables("Customers")
        txtCount.Text = DataGridView1.RowCount.ToString - 1
        txtSearch.Focus()
    End Sub

End Class

★✩★ Follow me on ★✩★

Twitter

» https://twitter.com/#!/IBasskung

Facebook

» https://www.facebook.com/CodeAMinute

Google+

» https://plus.google.com/u/0/107523775437712370629/posts

YouTube Channel

» http://www.youtube.com/user/TheRockmankung

Thank you very much.

ขอบคุณครับ.