In this tutorial, am going to show you how to connect VB.Net to database or how to connect VB.Net to ms access database.
How to connect VB.Net application to database is the question that is always asked by programmers who are just entry into programming or have been in programming but never connect to database.
In whichever ways, I am going to make the steps as easy as possible.
What am using here in this example is MS Access database. So simply put, connecting to ms access database in VB.net.
Let us start the process.
Here is the screenshot of how the program will look like at runtime, so you should design your for as you see it.
First, you should add the following controls on the form and change properties as shown on the table below.
| Control |
Properties |
| Label1 |
Text: Connecting to Database
Font-Weight:bold |
| Label2 |
Text: Name |
| Label3 |
Text: Phone number |
| TextBox1 |
Name: TXTPNAME |
| TextBox2 |
Name: TXTNUMBER |
| TextBox3 |
Name: txtdelete
Text:Enter number to delete |
| Button1 |
Text:Save
Name:BTNSAVE |
| Button2 |
Text: Update Name
Name: BTNUPDATE |
| Button3 |
Text: Delete
Name: BTNDELETE |
| Button1 |
Text:Exit
Name:BTNEXIT |
The above controls are all what we need to design the form so arrange them as you can see on the screenshot.
Secondly, we need a database for this. So create a database using Microsoft Access and save it as version 2003 format.
For steps on how to create database in Microsoft Access, check
creating database.
Thirdly, we need to import the following Namespaces as they are very important. This should be at the top most of the codebehind.
Imports System.Data
Imports System.Data.OleDb
Next, we should declare and hardcode a string to hold our database path so that we don't have to type over and over. So place this at General declaration section i.e after Public Class Form1
Dim str As String = "provider=microsoft.jet.oledb.4.0;data source='" & Application.StartupPath & "\phonebookdatabase.mdb" & "'"
Fourthly, we have to create a subroutine called RefreshGridView(). This subroutine will be called each time a new action is taken so that the datagrid can be refresh and populate new added records.
Sub RefreshGridView()
Dim sql As String = "SELECT pname,phonenumber FROM tblphonebook"
Using conn As New OleDb.OleDbConnection(str)
If conn.State = ConnectionState.Closed Then conn.Open()
Dim cmd As New OleDb.OleDbCommand(sql, conn)
Using da As New OleDbDataAdapter(cmd) 'da = DataAdapter
Using ds As New DataSet 'ds = DataSet
da.Fill(ds)
DGV.Columns(0).Name = "pname"
DGV.Columns(0).HeaderText = "Name"
DGV.Columns(0).DataPropertyName = "pname"
DGV.Columns(1).Name = "phonenumber"
DGV.Columns(1).DataPropertyName = "phonenumber"
DGV.Columns(1).HeaderText = "Phone Number"
DGV.DataSource = ds.Tables(0)
End Using
End Using
End Using
End Sub
Now that we have all things setup, let us start writing codes for each of the buttons click events and form load event.
On Form1_Load(), we are going to set DGV.AutoGenerateColumns = False, initialize the number of columns that the DataGridView should hold and also call the RefreshGridView so that our datagrid can show records that are already existing in the database.
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DGV.AutoGenerateColumns = False
DGV.ColumnCount = 2
RefreshGridView()
End Sub
The save button as the name implies we save record to the phone database. Here is the code
Private Sub BTNSAVE_Click(sender As Object, e As EventArgs) Handles BTNSAVE.Click
Dim sql As String = "INSERT INTO tblphonebook(pname,phonenumber)values(@pname,@phonenumber)"
Using conn As New OleDb.OleDbConnection(str)
If conn.State = ConnectionState.Closed Then conn.Open()
Using cmd As New OleDb.OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@pname", TXTNAME.Text)
cmd.Parameters.AddWithValue("@phonenumber", TXTNUMBER.Text)
cmd.ExecuteReader()
MsgBox("record saved")
RefreshGridView() 'Refresh the DataGridView
End Using
End Using
End Sub
The delete button which you can also guess the function by the name is to remove any record specified by user from the database and here is the code
Private Sub BTNDELETE_Click(sender As Object, e As EventArgs) Handles BTNDELETE.Click
Dim sql As String = "DELETE FROM tblphonebook WHERE phonenumber=@phonenumber"
Using conn As New OleDb.OleDbConnection(str)
If conn.State = ConnectionState.Closed Then conn.Open()
Using cmd As New OleDb.OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@phonenumber", txtdelete.Text)
cmd.ExecuteReader()
MsgBox("record saved")
RefreshGridView() 'Refresh the DataGridView
End Using
End Using
End Sub
The update button as the name implies is used to update existing record and the code is below;
Private Sub BTNUPDATE_Click(sender As Object, e As EventArgs) Handles BTNUPDATE.Click
Dim sql As String = "UPDATE tblphonebook SET pname=@pname, phonenumber=@phonenumber WHERE phonenumber=@phonenumber"
Using conn As New OleDb.OleDbConnection(str)
If conn.State = ConnectionState.Closed Then conn.Open()
Using cmd As New OleDb.OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@pname", TXTNAME.Text)
cmd.Parameters.AddWithValue("@phonenumber", TXTNUMBER.Text)
cmd.ExecuteReader()
MsgBox("Record updated")
RefreshGridView() 'Refresh the DataGridView
End Using
End Using
End Sub
And finally, the last but not the least, is the txtdelete button. This Control text should be cleared if it contains default text and the code to achieve it is;
Private Sub txtdelete_Click(sender As Object, e As EventArgs) Handles txtdelete.Click
If txtdelete.Text.ToLower = "enter number to delete" Then
txtdelete.Text = ""
End If
End Sub
Below is the complete code of the program.
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
Dim str As String = "provider=microsoft.jet.oledb.4.0;data source='" & Application.StartupPath & "\phonebookdatabase.mdb" & "'"
Private Sub BTNSAVE_Click(sender As Object, e As EventArgs) Handles BTNSAVE.Click
Dim sql As String = "INSERT INTO tblphonebook(pname,phonenumber)values(@pname,@phonenumber)"
Using conn As New OleDb.OleDbConnection(str)
If conn.State = ConnectionState.Closed Then conn.Open()
Using cmd As New OleDb.OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@pname", TXTNAME.Text)
cmd.Parameters.AddWithValue("@phonenumber", TXTNUMBER.Text)
cmd.ExecuteReader()
MsgBox("record saved")
RefreshGridView() 'Refresh the DataGridView
End Using
End Using
End Sub
Private Sub BTNEXIT_Click(sender As Object, e As EventArgs) Handles BTNEXIT.Click
Me.Close()
End Sub
Sub RefreshGridView()
Dim sql As String = "SELECT pname,phonenumber FROM tblphonebook"
Using conn As New OleDb.OleDbConnection(str)
If conn.State = ConnectionState.Closed Then conn.Open()
Dim cmd As New OleDb.OleDbCommand(sql, conn)
Using da As New OleDbDataAdapter(cmd) 'da = DataAdapter
Using ds As New DataSet 'ds = DataSet
da.Fill(ds)
DGV.Columns(0).Name = "pname"
DGV.Columns(0).HeaderText = "Name"
DGV.Columns(0).DataPropertyName = "pname"
DGV.Columns(1).Name = "phonenumber"
DGV.Columns(1).DataPropertyName = "phonenumber"
DGV.Columns(1).HeaderText = "Phone Number"
DGV.DataSource = ds.Tables(0)
End Using
End Using
End Using
End Sub
Private Sub BTNDELETE_Click(sender As Object, e As EventArgs) Handles BTNDELETE.Click
Dim sql As String = "DELETE FROM tblphonebook WHERE phonenumber=@phonenumber"
Using conn As New OleDb.OleDbConnection(str)
If conn.State = ConnectionState.Closed Then conn.Open()
Using cmd As New OleDb.OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@phonenumber", txtdelete.Text)
cmd.ExecuteReader()
MsgBox("record saved")
RefreshGridView() 'Refresh the DataGridView
End Using
End Using
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DGV.AutoGenerateColumns = False
DGV.ColumnCount = 2
RefreshGridView()
End Sub
Private Sub BTNUPDATE_Click(sender As Object, e As EventArgs) Handles BTNUPDATE.Click
Dim sql As String = "UPDATE tblphonebook SET pname=@pname, phonenumber=@phonenumber WHERE phonenumber=@phonenumber"
Using conn As New OleDb.OleDbConnection(str)
If conn.State = ConnectionState.Closed Then conn.Open()
Using cmd As New OleDb.OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@pname", TXTNAME.Text)
cmd.Parameters.AddWithValue("@phonenumber", TXTNUMBER.Text)
cmd.ExecuteReader()
MsgBox("Record updated")
RefreshGridView() 'Refresh the DataGridView
End Using
End Using
End Sub
Private Sub txtdelete_Click(sender As Object, e As EventArgs) Handles txtdelete.Click
If txtdelete.Text.ToLower = "enter number to delete" Then
txtdelete.Text = ""
End If
End Sub
End Class
That is all. Thanks for reading!