VB.NET being part of powerful of the .NET framework inherits alot of capabilities of modern languages like C#.
Most of the libraries and APIs are actually shared. Most of the winform components found in C# can be found in VB.NET as well. One example is ListView which allows us display data in multiple columns.
VB.NET ListViews are quite easy to use and efficient. You can build pwoerful management systems with VB.NET when you combine it with a RDBMS(Relational Database Management System) like Microsoft Access. Hence our today's aim is to perform CRUD operations against MS access database with VB.NET using OleDBData classes.
We insert/save data to ms access database from textboxes. We retrieve/select data from the database and bind it to a listview component. We update/edit data. We also delete/remove data row by row.
Overview
The ListView component allows us display multi-column data in Lists that are very flexible. The class itself belongs to System.Windows.Forms namespace, meaning it's a winform component. Futhermore, it resides inside the System.Windows.Forms.dll assembly. It's a component that exists for C#, VB.NET, C++ and F#.
Questions this Examples helps answer.
- How to use VB.NET ListView component with ms access database?
- How to insert/save data from textbox to ms access database in Visual Basic.NET
- How to select/retrieve ms access data to a winform ListView.
- How to update/edit ms access database via textbox and refresh ListView changes.
- How to delete/remove ms access database data row by row in a ListView.
- How to set the selected ListView row to corresponding textboxes.
- How to clear a ListView component on button click.
Tools
These are the tools we used:
- Platform : .NET
- Language : VB.NET
- IDE : Visual Studio 2013
- Database : Microsoft Access
Asssumptions.
We assume that you can drag a ListView component in visual studio onto your winforms. Note that We used metro theme to theme our winforms. You don't have to do so. However, if you would want to install metro theme look at this link in youtube. For this project, simply extend the System.Windows.Form instead of MetroForm. We also assume that you ms office access software installed in your machine and can create a database project in it with three 4 columns as shown below.
MS Database
Below is the overview of our MS Access database table.
Database Name : spacecraftsDB Database
Table : spacecraftsTB Columns:
Below are our columns with database data types.
ID => Integer => Autoincrements
S_Name => Short Text
S_Propellant => Short Text
S_Destination Short Text
We have attached the MS Access database with the project, it's in the mdb format. Just copy it to some directory and then copy the path of the mdb file you'll use it in the connection string.
Screenshot
- Here's the screenshot of the project.
Source Code
Lets have a look at the project's source code.
Form1.vb
- This is where we INSERT,SELECT,UPDATE,DELETE data to and from MS Access database.
Imports System.Data.OleDb
Public Class Form1
'DECLARATIONS
Private Const conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/Hp/Documents/DataBases/spacecraftsDB.mdb;"
ReadOnly con As OleDbConnection = New OleDbConnection(conString)
Dim cmd As OleDbCommand
Dim adapter As OleDbDataAdapter
ReadOnly dt As DataTable = New DataTable()
'WHEN FORM IS LOADED
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.SetupListView()
End Sub
'SETUP LISTVIEW
Private Sub SetupListView()
'SET LISTVIEW PROPERTIES
mListView.View = View.Details
mListView.FullRowSelect = True
'CONSTRUCT COLUMNS
mListView.Columns.Add("ID", 50)
mListView.Columns.Add("Name", 160)
mListView.Columns.Add("Propellant", 170)
mListView.Columns.Add("Destination", 170)
End Sub
'INSERT INTO DATABASE
Public Sub Add()
'INSERT SQL STATEMENT
Const SQL As String = "INSERT INTO spacecraftsTB(S_Name,S_Propellant,S_Destination) VALUES(@NAME,@PROPELLANT,@DESTINATION)"
cmd = New OleDbCommand(SQL, con)
'ADD PARAMETERS
cmd.Parameters.AddWithValue("@NAME", nameTxt.Text)
cmd.Parameters.AddWithValue("@PROPELLANT", propellantTxt.Text)
cmd.Parameters.AddWithValue("@DESTINATION", destinationTxt.Text)
'OPEN CONNECTION And INSERT INTO DATABASE THEN CLOSE CONNECTION
Try
con.Open()
If cmd.ExecuteNonQuery() > 0 Then
MsgBox("Successfully Inserted")
CleartextBoxes()
End If
con.Close()
Retrieve()
Catch ex As Exception
MsgBox(ex.Message)
con.Close()
End Try
End Sub
'POPULATE LISTVIEW
Private Sub Populate(id As String, spacecraft As String, propellant As String, destination As String)
'ROW ARRAY
Dim row As String() = New String() {id, spacecraft, propellant, destination}
Dim item As ListViewItem = New ListViewItem(row)
'ADD TO ROWS COLLECTION
mListView.Items.Add(item)
End Sub
'RETRIEVE FROM DataBase
Private Sub Retrieve()
mListView.Items.Clear()
'SELECT SQL STATEMENT
Dim sql As String = "SELECT * FROM spacecraftsTB "
cmd = New OleDbCommand(sql, con)
'OPEN CONNECTION,RETRIEVE,FILL LISTVIEW
Try
con.Open()
adapter = New OleDbDataAdapter(cmd)
adapter.Fill(dt)
'LOOP THRU DATATABLE
For Each row In dt.Rows
Populate(row(0), row(1), row(2), row(3))
Next
'CLEAR DATATABLE
dt.Rows.Clear()
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
con.Close()
End Try
End Sub
'UPDATE DATABASE
Private Sub UpdateLV(id As String, spacecraft As String, propellant As String, destination As String)
'SQL STATEMENT
Dim sql As String = "UPDATE spacecraftsTB SET S_Name='" + spacecraft + "',S_Propellant='" + propellant + "',S_Destination='" + destination + "' WHERE ID=" + id + ""
cmd = New OleDbCommand(sql, con)
'OPEN CONNECTION,EXECUTE UPDATE,CLOSE CONNECTION'
Try
con.Open()
adapter = New OleDbDataAdapter(cmd)
adapter.UpdateCommand = con.CreateCommand()
adapter.UpdateCommand.CommandText = sql
If (adapter.UpdateCommand.ExecuteNonQuery() > 0) Then
MsgBox("Successfully Updated")
CleartextBoxes()
End If
con.Close()
Retrieve()
Catch ex As Exception
MsgBox(ex.Message)
con.Close()
End Try
End Sub
'DELETE FROM DATABASE
Private Sub Delete(id As String)
'ELETE SQL STATEMENT
Dim sql As String = "DELETE FROM spacecraftsTB WHERE ID=" + id + ""
cmd = New OleDbCommand(sql, con)
'OPEN CONNECTION,EXECUTE DELETE,CLOSE CONNECTION
Try
con.Open()
adapter = New OleDbDataAdapter(cmd)
adapter.DeleteCommand = con.CreateCommand()
adapter.DeleteCommand.CommandText = sql
'PROMPT FOR CONFIRMATION BEFORE DELETE
If MessageBox.Show("Are you sure to permanently delete this?", "DELETE", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.OK Then
If cmd.ExecuteNonQuery() > 0 Then
CleartextBoxes()
MsgBox("Successfully deleted")
End If
End If
con.Close()
Retrieve()
Catch ex As Exception
MsgBox(ex.Message)
con.Close()
End Try
End Sub
'RESET TEXTBOXES
Private Sub CleartextBoxes()
nameTxt.Text = ""
propellantTxt.Text = ""
destinationTxt.Text = ""
End Sub
'ADD BUTTON CLICKED
Private Sub addBtn_Click(sender As Object, e As EventArgs) Handles addBtn.Click
Add()
End Sub
'RETRIEVE BUTTON CLICKED
Private Sub retrieveBtn_Click(sender As Object, e As EventArgs) Handles retrieveBtn.Click
Retrieve()
End Sub
'UPDATE BUTTON CLICKED
Private Sub updateBtn_Click(sender As Object, e As EventArgs) Handles updateBtn.Click
Dim selectedIndex As Int32 = mListView.SelectedIndices(0)
If Not selectedIndex = -1 Then
Dim id As String = mListView.SelectedItems(0).SubItems(0).Text
' Dim id As Int32 = Convert.ToInt32(selected)
UpdateLV(id, nameTxt.Text, propellantTxt.Text, destinationTxt.Text)
End If
End Sub
'DELETE BUTTON CLICKED
Private Sub deleteBtn_Click(sender As Object, e As EventArgs) Handles deleteBtn.Click
Dim selectedIndex As Int32 = mListView.SelectedIndices(0)
If Not selectedIndex = -1 Then
Dim id As String = mListView.SelectedItems(0).SubItems(0).Text
' Dim id As Int32 = Convert.ToInt32(selected)
Delete(id)
End If
End Sub
'CLEAR BUTTON CLICKED
Private Sub clearBtn_Click(sender As Object, e As EventArgs) Handles clearBtn.Click
mListView.Items.Clear()
CleartextBoxes()
End Sub
'LISTVIEW SELECTION CHANGED
Private Sub mListView_SelectedIndexChanged(sender As Object, e As EventArgs) Handles mListView.SelectedIndexChanged
Try
Dim selectedIndex As Int32 = mListView.SelectedIndices(0)
If Not selectedIndex = -1 Then
If mListView.SelectedItems(0).SubItems(0).Text IsNot Nothing Then
'GET SELECTED ITEMS
Dim spacecraft As String = mListView.SelectedItems(0).SubItems(1).Text
Dim propellant As String = mListView.SelectedItems(0).SubItems(2).Text
Dim destination As String = mListView.SelectedItems(0).SubItems(3).Text
'SET THEM TO TEXTBOXES
nameTxt.Text = spacecraft
propellantTxt.Text = propellant
destinationTxt.Text = destination
End If
End If
Catch ex As ArgumentOutOfRangeException
End Try
End Sub
End ClassDownload
Download the full project below: Coming soon. Download
How To Run
- Download the source code above.
- Extract it.
- In your Visual Studio: File -> Open ->Project/Solution.
- Choose the Solution location.
- Open
- That's it, you've imported the project to your visual studio.
OR
- Drag Drop a datagridview in your Form in visual studio.
- Copy the Form1 class above into your Form1.
- Change the connection string path.
- Change database details.
Conclusion
We've seen how to insert, select, update and delete data to and from microsoft access database using vb.net. Our component is ListView.
More
YouTube
- Visit our channel for more examples like these.
- Lets share tips and ideas in our Facebook Page.
Oclemy,Cheers.