Book record Navigation Using Visual Basic.Net

Submitted by joken on Thursday, November 28, 2013 - 11:48.
In this tutorial, I'm going to teach you how to create a Book Navigation using Visual basic and Microsoft Access for database. This tutorial will help both novice, or an enthusiast programmer to understand the concept of how to add a record navigation in any system especially when you are planning to create or developing a library system. To start on lesson, open visual basic and add save it as “Record Navigation”. Then we’re going to add controls such four labels, four Text box and four Buttons. Next, arrange all controls that look like as shown below: At this time, since we have already done add controls to our application. We are now going to add functionality to our program. To start with this, double click the form and under public class, add the following code: 
  1. 'cmd represent an SQL statement or stored procedure to execute against a data source
  2. Dim cmd As New OleDb.OleDbCommand
  3. 'it represent data commands and database connection that are used to fill the dataset or datatable
  4. Dim da As New OleDb.OleDbDataAdapter
  5. 'it represent an open connection to a data source and we equal this to our function name mycon()
  6. Dim con As OleDb.OleDbConnection = mycon()
  7. 'we create data table as our imaginary table to our memory
  8. Dim maindt As New DataTable
  9. 'this sql will hold later our SQL statements 
  10. Dim sql As String
  11. 'our initial value for our minimun value to be used later for navigation
  12. Dim minval As Integer = 0
  13. 'the integer thata will hold the maximum value
  14. Dim maxval As Integer
Next, we’re going to set a database function that will return our connection string as a new connection string. And this function holds our data source and the database provider. And here’s the following code: 
  1. Public Function mycon() As OleDb.OleDbConnection
  2.     Return New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\library.mdb")
  3. End Function
Then double click the form, and add the following code: 
  1. Try
  2.     'set the SQL Statements getting all the data of books
  3.     sql = "Select * from tblbooks"
  4.     'opens the connections
  5.     con.Open()
  6.  
  7.     'gets the sql connection and storede procedure to execute the at the data source
  8.     With cmd
  9.         .CommandText = sql
  10.         .Connection = con
  11.     End With
  12.     'get the stored procedure used to select records in the data source
  13.     da.SelectCommand = cmd
  14.     'refreshes the rows and fill the value to the Data table named maindt
  15.     da.Fill(maindt)
  16.     'Get the result number of result and assigne to maxval
  17.     maxval = maindt.Rows.Count
  18. Catch ex As Exception
  19.     MsgBox(ex.Message, MsgBoxStyle.Information)
  20. End Try
  21. con.Close()
At this time, we’re going to create a new sub procedure to be used for the navigation of data and we will call the sub procedure as “naive”. And here’s the following code: 
  1. Public Sub nav(ByVal a As Integer)
  2.     Try
  3.         'gets the value from the specific column in the data table 
  4.         'and assign to a specific text box
  5.         txtouttile.Text = maindt.Rows(a).Item(1)
  6.         txtoutauthor.Text = maindt.Rows(a).Item(2)
  7.         txtoutcategory.Text = maindt.Rows(a).Item(4)
  8.         txtisbn.Text = maindt.Rows(a).Item(3)
  9.  
  10.     Catch ex As Exception
  11.         MsgBox(ex.Message)
  12.     End Try
  13.  
  14. End Sub
Next, we will add a functionality to our Next record Button. To do this, double click this button and Add the following code: 
  1. 'check if minval is not equal to maxval minus 1
  2. If minval <> maxval - 1 Then
  3.     'minval is incremented by 1
  4.     minval = minval + 1
  5.     'throw the value of minval to nav procedure
  6.     nav(minval)
  7.  
  8. Else
  9.     MsgBox("Last record!")
  10. End If
For Last Record, double click the button and add the following code: 
  1. If minval <> maxval Then
  2. 	minval = maxval - 1
  3. 	nav(minval)
  4.  
  5. End If
And for the Previous record, add the following code: 
  1. 'check if minval is still greater than zero
  2. If minval > 0 Then
  3.     'minval will be decremented by one
  4.     minval = minval - 1
  5.     nav(minval)
  6.  
  7. ElseIf minval = -1 Then
  8.     MsgBox("No results found!")
  9.  
  10. ElseIf minval = 0 Then
  11.     MsgBox("First Record")
  12.     nav(minval)
  13. End If
And finally, for the First record button, here’s the following code: 
  1. If minval <> 0 Then
  2.     minval = 0
  3.     nav(minval)
  4. End If
The database name is ”library” and it is place inside “Record Navigation\Record Navigation\bin\Debug”. At this time you can now test your program by pressing “F5”.

Tags

Add new comment

About text formats
  • 258 views