ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

How to Autocomplete a TextBox with Multiple Columns Using Visual Basic 2008 And MS Acces Database

In this tutorial I will show you how to save data and autocomplete a textbox with multiple columns in Visual Basic 2008 and MS Access Database. As I expand my knowledge about autocomplete I discovered that you can put multiple columns/fields on it. So, it depends on you or your query, on how many columns/fields you want to exist.

Let’s begin:

1.Open Visual Basic 2008, create a project and do the Form just like this.

multipleautocomplete_form

2.Double click the Form and do the following codes above the Form_Load.

  1. 'DECLARE A STRING VARIABLE TO PUT YOUR QUERY ON IT
  2.     Dim query As String
  3.     'DECLARING A VARIABLE AS OLEBDCONNECTION TO REPRESENT AN OPEN CONNECTION TO THE DATA SOURCE
  4.     'IN THIS AREA, YOUR GOING TO PUT A CONNECTION STRING THAT REPRESENTS A PROVIDER AND A DATA SOURCE.
  5.     Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
  6.                                       & Application.StartupPath & "\people.accdb;")

3.In the Form_Load , do this code for the autocomplete of a Textbox.

  1. Private Sub Form1_Load(ByVal sender As System. Object, ByVal e As System. EventArgs) Handles MyBase.Load
  2.  
  3.         Try
  4.             'OPENING THE CONNECTION
  5.             con.Open()
  6.             'PUTING THE VALUE ON A VARIABLE THAT YOU HAVE BEEN DECLARE ON THE TOP
  7.             query = "SELECT * FROM tblpeople"
  8.             'DECLARING A VARIABLE AS OLEDBDATAADAPTER
  9.             'TO REPRESENTS A SET OF DATA COMMANDS AND A DATABASE CONNECTION
  10.             'THAT ARE USED TO FILL THE DATASET/DATATABLE AND UPDATE THE DATA SOURCE
  11.             'PUT HERE YOUR COMMAND TEXT AND A CONNECTION STRING
  12.             Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(query, con)
  13.             'DECLARING A VARIABLE AS DATATABLE
  14.             'REPRESENT ONE TABLE IN THE DATABASE
  15.             Dim dt As New DataTable
  16.             'FILLING THE DATATABLE
  17.             da.Fill(dt)
  18.             'DECLARING A VARIABLE AS DATAROW
  19.             'REPRESENTS A ROW OF DATA IN THE DATATABLE
  20.             Dim r As DataRow
  21.             'CLEARING THE DATA IN THE CUSTOM SOURCE OF A TEXTBOX
  22.             txtsearch.AutoCompleteCustomSource.Clear()
  23.             'DECLARING A VARIABLE AS INTEGER AND STORE THE MAX COLUMN OF THE DATATABLE
  24.             Dim maxcolumn As Integer = dt.Columns.Count - 1
  25.  
  26.             For Each r In dt.Rows
  27.                 For i As Integer = 0 To maxcolumn
  28.                     'ADDING THE DATA OF THE DATATABLE IN THE CUSTOM SOURCE OF A TEXTBOX
  29.                     txtsearch.AutoCompleteCustomSource.Add(r.Item(i).ToString)
  30.                 Next i
  31.             Next r
  32.         Catch ex As Exception
  33.             MsgBox(ex.Message)
  34.         End Try
  35.         'CLOSING A CONNECTION
  36.         con.Close()
  37.     End Sub

4.Go back to the Design Views, double click the Save Button and do the following code for inserting the data in the Database.

  1. Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
  2.  
  3.         Try
  4.             'OPENING A CONNECTION
  5.             con.Open()
  6.             'PUTTING THE VALUE ON A VARIABLE THAT YOU HAVE BEEN DECLARE ON THE TOP
  7.             query = "INSERT INTO tblpeople (FIRSTNAME,LASTNAME,ADDRESS) " _
  8.             & "VALUES ('" & txtfname.Text & "','" & txtlname.Text & "','" & txtaddress.Text & "')"
  9.             'DECLARING A VARIABLE AS OLEDBDATAADAPTER
  10.             'TO REPRESENTS A SET OF DATA COMMANDS AND A DATABASE CONNECTION
  11.             'THAT ARE USED TO FILL THE DATASET/DATATABLE AND UPDATE THE DATA SOURCE
  12.             'PUT HERE YOUR COMMAND TEXT AND A CONNECTION STRING
  13.             Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(query, con)
  14.             'DECLARING A VARIABLE AS DATATABLE
  15.             'REPRESENT ONE TABLE IN THE DATABASE
  16.             Dim dt As New DataTable
  17.             'FILLING THE DATATABLE
  18.             da.Fill(dt)
  19.  
  20.             MsgBox("Data has been save", MsgBoxStyle.SystemModal, "Save")
  21.         Catch ex As Exception
  22.             MsgBox(ex.Message)
  23.         End Try
  24.         'CLOSING A CONNECTION
  25.         con.Close()
  26.         'CALLING THE FIRST LOAD TO REFRESH THE DATA IN THE CUSTOM SOURCE OF A TEXTBOX
  27.         Call Form1_Load(sender, e)
  28.     End Sub

Reminder: you cannot autocomplete a Textbox without doing this, on its properties.

multipleautocomplete_properties

You can download the complete source herehere

Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!