Insert, Update, Delete, Search & ListView using ADOdb
This article is a beginner's tutorial for understanding what is ADODB.
ADO (ActiveX Data Object) is Microsoft's advanced universal data-access solution, consisting of an object model-based. ActiveX Data Object, which is a technology that allows data-access functionality to different types of data sources.
Follow the steps:
For your understanding of this code you will need to install VB.Net 2005 and you also need MS Access 2003 or higher version.
Create a new Windows Application Project,
Add new reference by click on Project>Add Reference >COM > and Add Microsoft AciveX Data Objects 2.6 Library.
1) Create 3 Form i.e Form1,Form2,Form3.
2) Place 3 Labels on form1
i.e (First Name,Middle Name and Last Name)
3) Place 3 Text Boxes
4) Place 3 Buttons i.e (Insert,View and Search)
5) Create MS Access Database "Database1.mdb"
6) Create a table "ADODB"
7) Create 3 field FirstName, MiddleName, LastName and Rename ID field to SN.
Option Strict Off
Option Explicit On
Friend Class Form1
Inherits System.Windows.Forms.Form
Private Sub Command1_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command1.Click
If Text1.Text = "" Or Text2.Text = "" Or Text3.Text = "" Then
MsgBox("Please Enter All Field", MsgBoxStyle.Information, "ADODB Database")
Exit Sub
End If
Dim strSql As String
Dim Constr As String
Dim con As New ADODB.Connection
Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\Database1.mdb;Persist Security Info=False"
strSql = "insert into ADODB (FirstName,MiddleName,LastName) " & " values('" & Text1.Text & "','" & Text2.Text & "','" & Text3.Text & "')"
con.Open(Constr)
con.Execute(strSql)
con.Close()
MsgBox("Record Inserted into Database", MsgBoxStyle.Information, "ADODB Database Connection")
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
End Sub
Private Sub Command2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command2.Click
Form2.Show()
End Sub
Private Sub Command3_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command3.Click
Dim Constr As Object
If Text1.Text = "" Or Text2.Text = "" Or Text3.Text = "" Then
MsgBox("Please Enter All Field", MsgBoxStyle.Information, "ADODB Database")
Exit Sub
End If
Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\Database1.mdb;Persist Security Info=False"
Dim con As New ADODB.Connection
con.Open(Constr)
Dim rs As New ADODB.Recordset
rs.Open("Select * from ADODB ", con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
If Not rs.EOF Then
While Not rs.EOF
If rs.Fields("FirstName").Value = Text1.Text And rs.Fields("MiddleName").Value = Text2.Text And rs.Fields("LastName").Value = Text3.Text Then
MsgBox("Record Found", MsgBoxStyle.Information, "ADODB Database")
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Exit Sub
End If
rs.MoveNext()
End While
End If
MsgBox("Record Not Found", MsgBoxStyle.Exclamation, "ADODB Search")
End Sub
End ClassFor next form instruction (Form2)
1) Place ListView from Toolbox.
2) Add 3 column by using columns properties of ListView.
i.e 1)First Name 2)Middle Name 3)Last Name.
Change following properties of ListView1:
CheckBoxes = True
FullRowSelect = True
GridLine = True
View = Details
Activation = OneClick
AllColumnRecoeder = True
HotTracking = True
HoverSelection = True
LabelEdit = True
Place 3 Button Refresh, Delete and Closed respectively.Write down following code in Form2.
Option Strict Off
Option Explicit On
Friend Class Form2
Inherits System.Windows.Forms.Form
Private Sub Command1_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command1.Click
Dim sql As Object
Dim a As Object
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sn As String
Dim Constr As String
Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\Database1.mdb;Persist Security Info=False"
con.Open(Constr)
rs.Open("Select * from ADODB order by sn asc", con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
For a = 0 To ListView1.Items.Count - 1
If ListView1.Items.Item(a).Checked = True Then
sn = rs.Fields("SN").Value
sql = "delete from ADODB where SN=" & sn
con.Execute(sql)
End If
rs.MoveNext()
Next
con.Close()
Call ViewDatabase()
End Sub
Private Sub Command2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command2.Click
Call ViewDatabase()
End Sub
Private Sub Command4_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command4.Click
Me.Close()
End Sub
Private Sub Form2_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
Call ViewDatabase()
End Sub
Public Sub ViewDatabase()
Dim con As New ADODB.Connection
Dim Constr As String
Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\Database1.mdb;Persist Security Info=False"
con.Open(Constr)
Dim rs As New ADODB.Recordset
rs.Open("Select * from ADODB order by SN asc", con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
Dim plist As System.Windows.Forms.ListView
plist = ListView1
Dim oLI As System.Windows.Forms.ListViewItem
Dim oSI As System.Windows.Forms.ListViewItem.ListViewSubItem
Dim Row As Object
With plist
.Items.Clear()
If rs.RecordCount <> 0 Then
For Row = 1 To rs.RecordCount
oLI = .Items.Add("")
oLI.Text = rs.Fields("FirstName").Value
oSI = oLI.SubItems.Add("MiddleName")
oSI.Text = rs.Fields("MiddleName").Value
oSI = Nothing
oSI = oLI.SubItems.Add("LastName")
oSI.Text = rs.Fields("LastName").Value
oSI = Nothing
rs.MoveNext()
Next
End If
End With
con.Close()
End Sub
Private Sub ListView1_DoubleClick(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles ListView1.DoubleClick
Form3.Show()
End Sub
End ClassFor next form instruction (Form3)
1) Place 3 Labels on Form3 i.e (First Name,Middle Name and Last Name)
2) Place 3 Text Boxes.
3) Place 1 Buttons i.e (Update) 
Write down following code in Form3
Option Strict Off
Option Explicit On
Friend Class Form3
Inherits System.Windows.Forms.Form
Private Sub Command1_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command1.Click
Dim strSql As Object
Dim a As Object
Dim rs As New ADODB.Recordset
Dim sn As Short
Dim Constr As String
Dim con As New ADODB.Connection
Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\Database1.mdb;Persist Security Info=False"
con.Open(Constr)
rs.Open("Select * from ADODB order by sn asc", con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
For a = 0 To Form2.ListView1.Items.Count - 1
If Form2.ListView1.Items.Item(a).Selected = True Then
sn = rs.Fields("SN").Value
End If
rs.MoveNext()
Next
con.Close()
strSql = "update ADODB " & " set FirstName='" & Text1.Text & "',MiddleName='" & Text2.Text & "',LastName='" & Text3.Text & "' where SN=" & sn
con.Open(Constr)
con.Execute(strSql)
con.Close()
MsgBox("Database update successfully!", MsgBoxStyle.Information, "ADODB Database")
Call Form2.ViewDatabase()
Me.Close()
End Sub
Private Sub Form3_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
Dim a As Object
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sn As Short
Dim Constr As String
Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\Database1.mdb;Persist Security Info=False"
con.Open(Constr)
rs.Open("Select * from ADODB order by sn asc", con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
For a = 0 To Form2.ListView1.Items.Count - 1
If Form2.ListView1.Items.Item(a).Selected = True Then
sn = rs.Fields("SN").Value
Text1.Text = rs.Fields("FirstName").Value
Text2.Text = rs.Fields("MiddleName").Value
Text3.Text = rs.Fields("LastName").Value
End If
rs.MoveNext()
Next
con.Close()
Exit Sub
End Sub
End ClassDownload Source File:
Source File.
