ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

How to create a Quick Search using VB.net and MS Access

When you are about to create any application you always ask yourself “what are the basic features your going to include in your system?”.Well the most common features that are always included in the system is that the user can Search a record or a list of records from the database using the system. For instance you going to create a Library System you must have a search option for your user. So in this tutorial I will show you “How to Create a Quick Search using Visual basic.Net”. 

 

First download the database to be used for this lesson. Click this—>books

Next we will now create a Visual basic Project named “Bookfinder” then, extract the download database and put it inside the Bin folder.

After creating a project in visual Basic. Let’s now design the form. Follow the Image below on the form looks like.

bookfinder

 

Next, Let’s add functionality to our application by adding some code to our objects.

 

First we will add declaration under public class: and here’s the code:

  1. 'this OleDbDataAdapter serves as bridge to our connection and SQL statements
  2. Dim da As New OleDb.OleDbDataAdapter
  3. Dim con As New OleDb.OleDbConnection
  4. 'this is a temporary or imaginary tables in the memory of computer same with the datasets
  5. Dim dt As New DataTable
  6. Dim sql As String

Next, Double click the form, and on the form load add the following code:

  1. con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\books.accdb"
  2.  
  3. btntxtbox.Enabled = False
  4. txtquick.Visible = False

 

Double the “Search” button and add the following code:

  1. 'we set her our SQL statements
  2. sql = "Select * from book_profile where title='" & txttitle.Text & "'" & _
  3. " OR author= '" & txtauthor.Text & "' OR Dewey_no='" & txtdewey.Text & "' OR subject='" & txtsubj.Text & "'"
  4. Try
  5. 'open the connection
  6. con.Open()
  7. 'bind the SQL and the connection through OleDBDataAdaoter and stored to da
  8. da = New OleDb.OleDbDataAdapter(sql, con)
  9. 'and whatever the value of da will be fill into dt our imaginary data table
  10. da.Fill(dt)
  11. 'get the datasource of datagridview from our data table
  12. dtgresult.DataSource = dt
  13.  
  14. Catch ex As Exception
  15. 'will throw an error if something went wrong.
  16. MsgBox(ex.Message, MsgBoxStyle.Information)
  17. End Try
  18. 'close connection
  19. con.Close()

Next double click the “Use Quick Search” button. And ad the following code:

  1. txtquick.Visible = True
  2. btnquick.Enabled = False
  3. btntxtbox.Enabled = True
  4. txttitle.Enabled = False
  5. txtauthor.Enabled = False
  6. txtdewey.Enabled = False
  7. txtsubj.Enabled = False
  8. txttitle.BackColor = Color.Aqua
  9. txtauthor.BackColor = Color.Aqua
  10. txtdewey.BackColor = Color.Aqua
  11. txtsubj.BackColor = Color.Aqua

Then, double click the textbox under “Quick Search” Label make sure the you will be redirected to “Texhchanged Event“. This allows you to perform quick search because every time you type on the textbox provided it will automatically gives you the results on the datagridiview based on the keyword inputted by the user.

And here’s add the following code:

 

  1. 'we set her our SQL statements
  2. sql = "Select * from book_profile where title like '%" & txtquick.Text & "%' or author LIKE '%" & txtquick.Text & "%'"
  3.  
  4. dt = New DataTable
  5. Try
  6. 'open the connection
  7. con.Open()
  8. 'bind the SQL and the connection through OleDBDataAdaoter and stored to da
  9. da = New OleDb.OleDbDataAdapter(sql, con)
  10. 'and whatever the value of da will be fill into dt our imaginary data table
  11. da.Fill(dt)
  12. 'get the datasource of datagridview from our data table
  13. dtgresult.DataSource = dt
  14.  
  15. Catch ex As Exception
  16. 'will throw an error if something went wrong.
  17. MsgBox(ex.Message, MsgBoxStyle.Information)
  18. End Try
  19. 'close connection
  20. con.Close()

and you can now press the “F5” on your keyboard or simply run your project.

You can download the full source code here.

 

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!