Data entry and Autocomplete with Multiple Columns

[sami.offline]

In this tutorial I will show you how to save 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

.

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

3.In the Form_Load

, do this code for the autocomplete of a Textbox.

1. 
   
2. Private
   
   Sub
   
   Form1_Load(
   
   ByVal
   
   sender As
   
   System.
   
   Object
   
   , ByVal
   
   e As
   
   System.
   
   EventArgs
   
   )
   
   Handles
   
   MyBase
   
   .
   
   Load
   
3. 
   
4. Try
   
5. 'OPENING THE CONNECTION
   
6. con.
   
   Open
   
   (
   
   )
   
7. 'PUTING THE VALUE ON A VARIABLE THAT YOU HAVE BEEN DECLARE ON THE TOP
   
8. query =
   
   "SELECT * FROM tblpeople"
   
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. 'DECLARING A VARIABLE AS DATAROW
    
20. 'REPRESENTS A ROW OF DATA IN THE DATATABLE
    
21. Dim
    
    r As
    
    DataRow
    
22. 'CLEARING THE DATA IN THE CUSTOM SOURCE OF A TEXTBOX
    
23. txtsearch.
    
    AutoCompleteCustomSource
    
    .
    
    Clear
    
    (
    
    )
    
24. 'DECLARING A VARIABLE AS INTEGER AND STORE THE MAX COLUMN OF THE DATATABLE
    
25. Dim
    
    maxcolumn As
    
    Integer
    
    =
    
    dt.
    
    Columns
    
    .
    
    Count
    
    -
    
    1
    
26. 
    
27. For
    
    Each
    
    r In
    
    dt.
    
    Rows
    
28. For
    
    i As
    
    Integer
    
    =
    
    0
    
    To
    
    maxcolumn
    
29. 'ADDING THE DATA OF THE DATATABLE IN THE CUSTOM SOURCE OF A TEXTBOX
    
30. txtsearch.
    
    AutoCompleteCustomSource
    
    .
    
    Add
    
    (
    
    r.
    
    Item
    
    (
    
    i)
    
    .
    
    ToString
    
    )
    
31. Next
    
    i
    
32. Next
    
    r
    
33. Catch
    
    ex As
    
    Exception
    
34. MsgBox
    
    (
    
    ex.
    
    Message
    
    )
    
35. End
    
    Try
    
36. 'CLOSING A CONNECTION
    
37. con.
    
    Close
    
    (
    
    )
    
38. End
    
    Sub
    
39. 
    

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.

You can download the complete source code.

Download