Search and Retrieve a Record from Access Database and VB.NET

Submitted by: 
Visitors have accessed this post 5901 times.

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).

accessaccess

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:

design

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:
output

Hope this helps! :)

Best Regards,

Engr. Lyndon Bermoy
IT Instructor/System Developer/Android Developer/Freelance Programmer

If you have some queries, feel free to contact the number or e-mail below.
Mobile: 09488225971
Landline: 826-9296
E-mail:[email protected]

Add and Follow me on Facebook: https://www.facebook.com/donzzsky

Visit and like my page on Facebook at: https://www.facebook.com/BermzISware


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.