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.

Form 1



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 Class



For next form instruction (Form2)



ListView of database.

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 Class


For 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)

Updating database

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 Class



Download Source File:
Source File.


Comments



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: