This tutorial is about how to connect to an MS Access database
and retrieve and insert values to that. We’ll talk about a simple example that
is Sign Up a user and allow the user to login to a system. That is we’ll create
a Sign Up form and a Login form. Also we need to create corresponding tables in
the database. Please note that this tutorial is not for very beginners. But it’s
aimed at beginners in “Database Programming”. Here we need to know about
another language called Structured Query Language (SQL). But here it is not necessary that you should
be expert in SQL.
Ok let’s start. I don’t like to talk too much about the theories
about the Databases or ADO.NET and stuffs like that. We’ll go straight away to the practical
section. But you should understand what a Class is. You know .NET programming
languages are highly object oriented. A simple example for a class is given
below.
Public Class Database
End Class
You can add a class to your Visual Basic.NET project simply
by right clicking the project name and then enter the class name. Checkout the following
screen shot for reference.
Step 1:
Step 2:
Now we created and saved our database. It’s time to add
tables to the database. Tables are the area in which the data are stored. Since
we are creating an extremely simple example, we need just 3 Fields or columns
in the Table. Field Name, Data type and field size are given below.
Table Name : tblUser
ID : AutoNumber
USER_NAME :Text
PASSWORD:Text
When you create the database you will be getting a default
table. To continue with that table, you have to press
You will be asked for the table name after clicked the view button.
Step 3:
Insert some values in the table. This is because we are
creating the Login form first. So in order to validate, we need some existing
data.
Step 4:
Now back to Classes. Hope you created the class namely Database.
This class will be the central part of our program and the same class can be
used for any applications you create wit .NET and MS Access database.
First of all we need to import a namespace called ”System.Data.OleDb”.
Don’t worry about namespaces; it’s just a group of pre written classes. This
namespace should be imported before you start the class. Look at how our class
has changed.
Imports System.Data.OleDb
Public Class Database
End Class
Step 5:
Declare data access
objects as private members of the class. All the following members should be
present in the class in order to make database access. Please note that these
members are not yet initialized with new keyword. The OleDbConnection object is used
for managing connection to the database. And OleDbCommand object is used for
sending and receiving data to/from the database by means of SQL queries. The OleDbDataReader object
will read data from data table which is retuned by a OleDbCommand object. And finally
the constr string is used to set the driver for the con object.
Private con As OleDbConnection
Private cmd As OleDbCommand
Private reader As OleDbDataReader
Private constr
As String
Step 6:
Add Constructor to the class. This will initialize all the
private members of the class we created except the reader object.
Public Sub New(ByVal conString As String)
con =
New OleDbConnection
cmd =
New OleDbCommand
constr = conString
End Sub
Step 7:
Now add the following function to our class. This is the
function which is used to connect to the data base. It will return True is the
connection to the database is successfully established.
Public Function
ConnectToDatabase() As Boolean
If Me.con.State = ConnectionState.Open Then
Me.con.Close()
End If
Me.con.ConnectionString = constr
Me.con.Open()
If Me.con.State = ConnectionState.Open Then
Return True
Else
Return False
End If
End Function
Similarly we also need a function to disconnect from the
database.
Public Function
DisonnectFromDatabase() As Boolean
If Me.con.State = ConnectionState.Open Then
Me.con.Close()
Return True
Else
Return False
End If
End Function
Step 8:
The above two functions are generic and that can be used in
any of the application you create. Here we are going to create a function which
is specific to this application. First we need to set all the settings required
by the cmd object. You can see the Me.cmd.CommandText section. That is
assigned to a string. This string is an SQL Query. Modify this according to
your table name and field name.
Public Function
DoLogin(ByVal UserName As
String, ByVal
Password As String)
Me.ConnectToDatabase()
Me.cmd.Connection = Me.con
Me.cmd.CommandType = CommandType.Text
Me.cmd.CommandText = "SELECT
ID FROM tblUser WHERE USER_NAME='" & UserName & "' AND PASSWORD='" & Password & "'"
reader = cmd.ExecuteReader
If reader.HasRows Then
DisonnectFromDatabase()
reader.Close()
Return True
Else
DisonnectFromDatabase()
reader.Close()
Return False
End If
End Function
Step 9:
Now we completed the class for login purpose. It’s time for
creating the form. Add two text box, two labels and a button to the form.
Change the password char property of the text box you are planning to make as
password field.
Step 10:
Generate the connection string. Under Data menu of the VS
IDE, click on Add New Data Source. Choose Data base and click next. Then Choose
Data set and click next and click on the New Connection Button.
From this window you need to change the data source into MS
Access Database file.
Browse you database file and press Test Connection. If that
is success then press ok. Now you can see a connection string node is folded. Open
that to get your connection string. And copy it then Close it please do not press ok.
Step 11:
Add the following code to the button’s click event. Please
note that the connection string is passed through the constructor of the class
we created.
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal
e As System.EventArgs)
Handles Button1.Click
Dim db As New Database("Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\Users\user\Documents\User.accdb")
If db.DoLogin(Me.TextBox1.Text,
Me.TextBox2.Text) = True
Then
MsgBox("Login OK")
Else
MsgBox("Login Failed")
End If
End Sub
Step 12:
Run!!
Hope you enjoyed the tutorial. Thank you.Stay tuned for the next tutorial about inserting data to a database.
Also read:
Also read:
No comments:
Post a Comment