VB.NET MS Access : ListView – INSERT SELECT UPDATE DELETE

VB.NET MS Access : ListView – INSERT SELECT UPDATE DELETE



Rating : No ratings yet.




Introduction

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.
MS Access Database
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.

ListView MS Access

Source Code

Lets have a look at the project's source code.

Form1.vbVideo/DemoDownload
  • 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 Class

 

Below is the video tutorial for this project.

Download the full project below:
Coming soon.

How To Run

  1. Download the source code above.
  2. Extract it.
  3. In your Visual Studio: File -> Open ->Project/Solution.
  4. Choose the Solution location.
  5. Open
  6. That's it, you've imported the project to your visual studio.

OR

  1. Drag Drop a datagridview in your Form in visual studio.
  2. Copy the Form1 class above into your Form1.
  3. Change the connection string path.
  4. 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.

Facebook

Oclemy,Cheers.



Rating :

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    five × three =

    COMMENTS