This tutorial is all about 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.
2.Double click the Form and do the following codes above the Form_Load.
- 'DECLARE A STRING VARIABLE TO PUT YOUR QUERY ON IT
- Dim query As String
- 'DECLARING A VARIABLE AS OLEBDCONNECTION TO REPRESENT AN OPEN CONNECTION TO THE DATA SOURCE
- 'IN THIS AREA, YOUR GOING TO PUT A CONNECTION STRING THAT REPRESENTS A PROVIDER AND A DATA SOURCE.
- Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
- & Application.StartupPath & "\people.accdb;")
3.In the Form_Load , do this code for the autocomplete of a Textbox.
- Private Sub Form1_Load(ByVal sender As System. Object, ByVal e As System. EventArgs) Handles MyBase.Load
- Try
- 'OPENING THE CONNECTION
- con.Open()
- 'PUTING THE VALUE ON A VARIABLE THAT YOU HAVE BEEN DECLARE ON THE TOP
- query = "SELECT * FROM tblpeople"
- 'DECLARING A VARIABLE AS OLEDBDATAADAPTER
- 'TO REPRESENTS A SET OF DATA COMMANDS AND A DATABASE CONNECTION
- 'THAT ARE USED TO FILL THE DATASET/DATATABLE AND UPDATE THE DATA SOURCE
- 'PUT HERE YOUR COMMAND TEXT AND A CONNECTION STRING
- Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(query, con)
- 'DECLARING A VARIABLE AS DATATABLE
- 'REPRESENT ONE TABLE IN THE DATABASE
- Dim dt As New DataTable
- 'FILLING THE DATATABLE
- da.Fill(dt)
- 'DECLARING A VARIABLE AS DATAROW
- 'REPRESENTS A ROW OF DATA IN THE DATATABLE
- Dim r As DataRow
- 'CLEARING THE DATA IN THE CUSTOM SOURCE OF A TEXTBOX
- txtsearch.AutoCompleteCustomSource.Clear()
- 'DECLARING A VARIABLE AS INTEGER AND STORE THE MAX COLUMN OF THE DATATABLE
- Dim maxcolumn As Integer = dt.Columns.Count - 1
- For Each r In dt.Rows
- For i As Integer = 0 To maxcolumn
- 'ADDING THE DATA OF THE DATATABLE IN THE CUSTOM SOURCE OF A TEXTBOX
- txtsearch.AutoCompleteCustomSource.Add(r.Item(i).ToString)
- Next i
- Next r
- Catch ex As Exception
- End Try
- 'CLOSING A CONNECTION
- 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.
- Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
- Try
- 'OPENING A CONNECTION
- con.Open()
- 'PUTTING THE VALUE ON A VARIABLE THAT YOU HAVE BEEN DECLARE ON THE TOP
- query = "INSERT INTO tblpeople (FIRSTNAME,LASTNAME,ADDRESS) " _
- & "VALUES ('" & txtfname.Text & "','" & txtlname.Text & "','" & txtaddress.Text & "')"
- 'DECLARING A VARIABLE AS OLEDBDATAADAPTER
- 'TO REPRESENTS A SET OF DATA COMMANDS AND A DATABASE CONNECTION
- 'THAT ARE USED TO FILL THE DATASET/DATATABLE AND UPDATE THE DATA SOURCE
- 'PUT HERE YOUR COMMAND TEXT AND A CONNECTION STRING
- Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(query, con)
- 'DECLARING A VARIABLE AS DATATABLE
- 'REPRESENT ONE TABLE IN THE DATABASE
- Dim dt As New DataTable
- 'FILLING THE DATATABLE
- da.Fill(dt)
- Catch ex As Exception
- End Try
- 'CLOSING A CONNECTION
- 'CALLING THE FIRST LOAD TO REFRESH THE DATA IN THE CUSTOM SOURCE OF A TEXTBOX
- Call Form1_Load(sender, e)
- End Sub
Reminder: you cannot autocomplete a Textbox without doing this, on its properties.
You can download the complete source 
here
Readers might read also:
