In this tutorial, i will teach you on how to create a program that can search and retrieve a record from an access database and vb.net.

Now, let's start this tutorial!

1. Create an access file with the table, entities, and record below. Name your access database as sample.mdb (2003 format).




2. Create a Windows Form Application for this tutorial by following the following steps in Microsoft Visual Studio: Go to File, click New Project, and choose Windows Application.

3. Next, add only one Button named btnFind and labeled it as "Find". This will search the inputted text in the searched textbox. Insert 4 textbox named txtSearch for the searching of student number, txtName for Student Name, txtCourse for Student course, and txtSection for student section. You must design your interface like this:



4. Create a module in your project and named it, modConnection.

Import Imports

System.

Data

.

OleDb

library. This library package is for ms access database.

In your module connection, initialize the following variables.

1. Module
   
   modConnection
   
2. 
   
3. Public
   
   cn As
   
   New
   
   OleDb.
   
   OleDbConnection
   
4. Public
   
   cm As
   
   New
   
   OleDb.
   
   OleDbCommand
   
5. Public
   
   dr As
   
   OleDbDataReader
   

Now, create a method named connection to have the connection string. This will locate the sample.mdb access database that we have created earlier.

1. Public
   
   Sub
   
   connection(
   
   )
   
2. cn =
   
   New
   
   OleDb.
   
   OleDbConnection
   
3. With
   
   cn
   
4. 'Provider must be Microsoft.Jet.OLEDB.4.0, find the access file, and test the connection
   
5. .
   
   ConnectionString
   
   =
   
   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
   
   &
   
   Application.
   
   StartupPath
   
   &
   
   "\sample.mdb"
   
6. .
   
   Open
   
   (
   
   )
   
7. End
   
   With
   
8. End
   
   Sub
   

Full code of the modConnection module.

1. Imports
   
   System.
   
   Data
   
   .
   
   OleDb
   
2. Module
   
   modConnection
   
3. 
   
4. Public
   
   cn As
   
   New
   
   OleDb.
   
   OleDbConnection
   
5. Public
   
   cm As
   
   New
   
   OleDb.
   
   OleDbCommand
   
6. Public
   
   dr As
   
   OleDbDataReader
   
7. 
   
8. Public
   
   Sub
   
   connection(
   
   )
   
9. cn =
   
   New
   
   OleDb.
   
   OleDbConnection
   
10. With
    
    cn
    
11. .
    
    ConnectionString
    
    =
    
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    
    &
    
    Application.
    
    StartupPath
    
    &
    
    "\sample.mdb"
    
12. .
    
    Open
    
    (
    
    )
    
13. End
    
    With
    
14. End
    
    Sub
    
15. End
    
    Module
    

5. Back to our form, put this code for the form_load. We will call the connetion() method that we have created in our module. Because we all know that module is access throughout the entire program.

1. Private
   
   Sub
   
   Form1_Load(
   
   ByVal
   
   sender As
   
   System.
   
   Object
   
   , ByVal
   
   e As
   
   System.
   
   EventArgs
   
   )
   
   Handles
   
   MyBase
   
   .
   
   Load
   
2. Call
   
   connection(
   
   )
   
3. End
   
   Sub
   

6. For the btnFind as the search button, put this code below.

We will first create a try and catch method to have the exception handling. In the try method, we will create a Boolean variable named found for finding the student number. Here, we will use the OleDbCommand with the method of connection, CommandType.Text, and CommandText, with the OleDbDataReader to execute the CommandText.

1. Dim
   
   found As
   
   Boolean
   
2. Try
   
3. 
   
4. cm =
   
   New
   
   OleDb.
   
   OleDbCommand
   
5. With
   
   cm
   
6. .
   
   Connection
   
   =
   
   cn
   
7. .
   
   CommandType
   
   =
   
   CommandType.
   
   Text
   
8. .
   
   CommandText
   
   =
   
   "SELECT * FROM tblStudent WHERE (Snum = '"
   
   &
   
   txtSearch.
   
   Text
   
   &
   
   "')"
   
9. dr =
   
   .
   
   ExecuteReader
   
10. End
    
    With
    

Next is to create a while statement for dr.Read to retrieve and display the data to the specified textbox, as well as making the Boolean variable found into True. Make cn.Close() in there and exit sub after finding the student number.

1. While
   
   dr.
   
   Read
   
   (
   
   )
   
2. 
   
3. txtName.
   
   Text
   
   =
   
   dr(
   
   "Sname"
   
   )
   
   .
   
   ToString
   
4. txtCourse.
   
   Text
   
   =
   
   dr(
   
   "Scourse"
   
   )
   
   .
   
   ToString
   
5. txtSection.
   
   Text
   
   =
   
   dr(
   
   "Ssection"
   
   )
   
   .
   
   ToString
   
6. found =
   
   True
   
7. 
   
8. End
   
   While
   
9. cn.
   
   Close
   
   (
   
   )
   
10. Exit
    
    Sub
    

If the inputted student number is not in the database, put this code below.

1. If
   
   found =
   
   False
   
   Then
   
   MsgBox
   
   (
   
   "Student ID not found."
   
   , MsgBoxStyle.
   
   Critical
   
   )
   
2. dr.
   
   Close
   
   (
   
   )
   

Here is the full source code of our Form.

1. Public
   
   Class
   
   Form1
   
2. 
   
3. Private
   
   Sub
   
   btnFind_Click(
   
   ByVal
   
   sender As
   
   System.
   
   Object
   
   , ByVal
   
   e As
   
   System.
   
   EventArgs
   
   )
   
   Handles
   
   btnFind.
   
   Click
   
4. Dim
   
   found As
   
   Boolean
   
5. Try
   
6. 
   
7. cm =
   
   New
   
   OleDb.
   
   OleDbCommand
   
8. With
   
   cm
   
9. .
   
   Connection
   
   =
   
   cn
   
10. .
    
    CommandType
    
    =
    
    CommandType.
    
    Text
    
11. .
    
    CommandText
    
    =
    
    "SELECT * FROM tblStudent WHERE (Snum = '"
    
    &
    
    txtSearch.
    
    Text
    
    &
    
    "')"
    
12. dr =
    
    .
    
    ExecuteReader
    
13. End
    
    With
    
14. While
    
    dr.
    
    Read
    
    (
    
    )
    
15. 
    
16. txtName.
    
    Text
    
    =
    
    dr(
    
    "Sname"
    
    )
    
    .
    
    ToString
    
17. txtCourse.
    
    Text
    
    =
    
    dr(
    
    "Scourse"
    
    )
    
    .
    
    ToString
    
18. txtSection.
    
    Text
    
    =
    
    dr(
    
    "Ssection"
    
    )
    
    .
    
    ToString
    
19. found =
    
    True
    
20. 
    
21. End
    
    While
    
22. cn.
    
    Close
    
    (
    
    )
    
23. Exit
    
    Sub
    
24. If
    
    found =
    
    False
    
    Then
    
    MsgBox
    
    (
    
    "Student ID not found."
    
    , MsgBoxStyle.
    
    Critical
    
    )
    
25. dr.
    
    Close
    
    (
    
    )
    
26. Catch
    
    ex As
    
    Exception
    
27. 
    
28. End
    
    Try
    
29. End
    
    Sub
    
30. 
    
31. Private
    
    Sub
    
    Form1_Load(
    
    ByVal
    
    sender As
    
    System.
    
    Object
    
    , ByVal
    
    e As
    
    System.
    
    EventArgs
    
    )
    
    Handles
    
    MyBase
    
    .
    
    Load
    
32. Call
    
    connection(
    
    )
    
33. End
    
    Sub
    
34. End
    
    Class
    

After that, Press F5 to run the program.

Output:


Hope this helps! :)

Best Regards,

Engr. Lyndon R. Bermoy

IT Instructor/System Developer/Android Developer

Mobile: 09079373999

Telephone: 826-9296

E-mail:[email protected]

Visit and like my page on Facebook at: Bermz ISware Solutions

Subscribe at my YouTube Channel at: SerBermz

Download