This Mini Project in VB.NET- Microsoft Access Database Connectivity will help you to understand the Database Connectivity. It uses DataReader, DataAdapter and Dataset for viewing and manipulating the data. We will create a form that accepts four values from user about a student- RollNo, Name, Class and Date of birth. The controls and the names given to the controls is shown in the below image.
Steps to create Mini Project in VB.NET- Microsoft Access Database Connectivity
Step 1- Create Database in Microsoft Access
Before designing and coding in VB.NET we will first create a database in Microsoft Access. The name given to database is StudentDB(with .accdb as extension). If you are using older version of Microsoft Access then the database file will have .mdb extension. In this case you will need the “Mircosoft.Jet.OLEDB.4.0” provider in place of “Microsoft.ACE.OLEDB.12.0”. The database we created contains one table with name tblStudent. The structure of the table is as given in the diagram
Once you create the database and the table, copy StudentDB.accdb (StudentDB.mdb in case of older version of Microsoft Access) file to this location in your solution directory “bin\Debug”. In this example the folder is “F:\DotNet\project\basicApp\basicApp\bin\Debug”. When you do this you don’t have to give the absolute address of database file in the connections string.
Step 2- Declare a global oledeb connection object and create connection in module
The next step to create Mini Project in VB.NET- Microsoft Access Database Connectivity is to make the connection with this database in your VB.NET application code. We will write this code in a module. A module is a project level file in .NET that doesn’t have any interface. It is used to declare the project level variables, constants, procedure and functions. It will help in accessing the common variables, constants, procedure and functions in any form or class of the current project.
Here we will declare a “oledb.connection” object. Then create a procedure to connect with our database stored in the \bin\Debug folder of our application (represented in the code with Application.StartupPath) The connection object conDB and procedure connectDB are declared with Public modifier in the module file name Module1 so that they are global within the project.
Module Module1 Public conDB As New OleDb.OleDbConnection Public Sub connectDB() If conDB.State = ConnectionState.Closed Then conDB.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\studentDB.accdb" conDB.Open() End If End Sub End Module
Step 3- Design Data Entry Form and check for connection
Once you create this module with the connection object and connectDB procedure, next step is to design the entry form. We can check for database connectivity by running the form. Create the form as shown in the diagram by placing the needed controls and giving the name to the controls in the property window.
Now open the form_load event of the form and write this code and run the application.
Private Sub Form2_Load(sender As Object, e As EventArgs) Handles Me.Load Module1.connectDB() Rowno=0 ‘initialization of counter to indicate current record End Sub
Step 4- Add code to the form
First we need to declare global objects and variables in the form code (at top just after Public Class Classname)
Dim stuReader As OleDb.OleDbDataReader ‘an OLEDB data reader object Dim stuCommand As New OleDb.OleDbCommand ‘an OLEDB command object Dim stuDataAdaptor As New OleDb.OleDbDataAdapter ‘an OLEDB dataadapter object Dim stuDS As DataSet ‘ a dataset Dim rowno As Integer ‘ variable to store current record number being accessed from tblStudent through recordset
Next we will add code for different actions of the form.
btnShow– This button is added to fill the Listbox1 with the names and rollnos of the students whose data is stored in the table tblStudent.
This is the code
Private Sub btnShow_Click(sender As Object, e As EventArgs) Handles btnShow.Click ListBox1.Items.Clear() stuCommand = Module1.conDB.CreateCommand() stuCommand.CommandText = "select rollno,Sname from tblstudent" stuReader = stuCommand.ExecuteReader While stuReader.Read ListBox1.Items.Add(stuReader.Item("rollno").ToString.PadRight(10) & " | " & stuReader.Item("Sname")) End While End Sub
Now the next code is for saving the data entered in the table tblStudent
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click Dim strSQL As String strSQL = "Insert into tblSTudent values(" & CInt(txtRollNo.Text) & ",'" _ & txtSName.Text & "','" _ & cboClass.Text & "',#" _ & dtpDOB.Value & "#)" stuCommand = New OleDb.OleDbCommand(strSQL, conDB) stuCommand.ExecuteNonQuery() End Sub
The next task is to write code for the Next(>), Previous(<), First(<<) and Last(>>) buttons. This is done with two user defined procedure getData and showData and four button click events. getData is used to fetch the data from tblSudent into dataset through adapter. showData procedure accepts an integer value as argument that indicates which record from the recordset is to be displayed in the form.
Private Sub getdata() stuDataAdaptor = New OleDb.OleDbDataAdapter("select * from tblStudent", conDB) stuDS = New DataSet stuDataAdaptor.Fill(stuDS, "tblStudent") reccount = stuDS.Tables(0).Rows.Count End Sub Private Sub showdata(i As Integer) txtRollNo.Text = stuDS.Tables("tblStudent").Rows(i).Item(0) txtSName.Text = stuDS.Tables("tblStudent").Rows(i).Item(1) cboClass.Text = stuDS.Tables("tblStudent").Rows(i).Item(2) dtpDOB.Value = stuDS.Tables("tblStudent").Rows(i).Item(3) End Sub
Now the code for browse buttons by calling the above two procedures
Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click getdata() showdata(0) End Sub Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click getdata() showdata(reccount - 1) End Sub Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click rowno = rowno + 1 If rowno >= reccount - 1 Then rowno = reccount - 1 btnLast_Click(sender, e) Else showdata(rowno) End If End Sub Private Sub btnPrev_Click(sender As Object, e As EventArgs) Handles btnPrev.Click rowno = rowno - 1 If rowno <= -1 Then rowno = 0 btnFirst_Click(sender, e) Else showdata(rowno) End If End Sub
Last action is to update the displayed record in the form so that the updated values are stored in the table. The code is written in the btnUpdate_Click event handler
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click Dim strSQL As String strSQL = "update tblSTudent set Sname='" _ & txtSName.Text & "',SClass='" _ & cboClass.Text & "',Sdob=#" _ & dtpDOB.Value & "# where rollno=" & CInt(txtRollNo.Text) stuCommand = New OleDb.OleDbCommand(strSQL, conDB) stuCommand.ExecuteNonQuery() End Sub
This is a very basic Mini Project in VB.NET- Microsoft Access Database Connectivity to make the readers understand the concepts of database connectivity and data manipulation.