VB.NET MS Access - ListView - INSERT SELECT UPDATE DELETE

vb.net crud

If there is one area where a language like VB.NET excels is making management systems. Be it school management system, hospital managements system,sales management system etc. From point of sale applications to registration systems, .NET generally provides us with all the tools we require.

The best way normally to start learning how to make such systems is to learn basic CRUD with database. In this tutorial that’s what we see, how to INSERT,SELECT,UPDATE and DELETE data to and from MS Access database with VB.NET. The component we use is ListView.

Questions this Examples helps answer.

  • How to insert,select,update and delete data to and from database.
  • How to show MS Access data in a ListView in vb.net.
  • How to insert from textbox to access database.
  • How to use OleDBConnection in vb.net.

Asssumptions.

We assume that you can create a ms access database. Create one and also make sure you create a table. You then specify the path to the database in the connection string in our code. For instance, mine was located at : C:/Users/Sedan/Documents/DataBases/peopleDB.mdb.

Screenshot

  • Here’s the screenshot of the project.

VB.NET MS ACCESS DATABASE

  • There’s our access database with data.

MS Access VB.NET CRUD

  • We can insert,select, update and delete data to and from ms access database.

VB-NET CRUD

Source Code

Lets have a look at the source code.

Form1.vb

Imports System.Data.OleDb

Public Class Form1

    'GLOBAL DECLARATIONS
    Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/Sedan/Documents/DataBases/peopleDB.mdb;"
    Dim con As OleDbConnection = New OleDbConnection(conString)
    Dim cmd As OleDbCommand
    Dim adapter As OleDbDataAdapter
    Dim dt As DataTable = New DataTable()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'SET LISTVIEW PROPERTIES
        ListView1.View = View.Details
        ListView1.FullRowSelect = True

        'CONSTRUCT COLUMNS
        ListView1.Columns.Add("Name", 150)
        ListView1.Columns.Add("Position", 150)
        ListView1.Columns.Add("Team", 150)

    End Sub

    Public Sub Add()
        'SQL STMT
        Dim SQL As String = "INSERT INTO peopleTB(N,P,T) VALUES(@PNAME,@POSITION,@TEAM)"
        cmd = New OleDbCommand(SQL, con)

        'ADD PARAMETERS
        cmd.Parameters.AddWithValue("@NAME", nameTxt.Text)
        cmd.Parameters.AddWithValue("@POSITION", positionTxt.Text)
        cmd.Parameters.AddWithValue("@TEAM", teamTxt.Text)

        'OPEN CONNECTION And INSERT
        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(name As String, pos As String, team As String)
        'ROW ARRAY
        Dim row As String() = New String() {name, pos, team}

        Dim item As ListViewItem = New ListViewItem(row)

        'ADD TO ROWS COLLECTION
        ListView1.Items.Add(item)

    End Sub

    'RETRIEVE FROM DB
    Private Sub Retrieve()
        ListView1.Items.Clear()
        'SQL STM
        Dim sql As String = "SELECT * FROM peopleTB "

        cmd = New OleDbCommand(sql, con)

        'OPEN CON,RETRIEVE,FILL LISTVIEW
        Try
            con.Open()
            adapter = New OleDbDataAdapter(cmd)

            adapter.Fill(dt)

            'LOOP THRU DT
            For Each row In dt.Rows
                Populate(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(name As String)
        'SQL STMT
        Dim sql As String = "UPDATE peopleTB SET N='" + nameTxt.Text + "',P='" + positionTxt.Text + "',T='" + teamTxt.Text + "' WHERE N='" + name + "'"

        cmd = New OleDbCommand(sql, con)

        'OPEN CON,EXECUTE UPDATE,CLOSE'
        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
    Private Sub Delete(name As String)
        'SQL STMT
        Dim sql As String = "DELETE FROM peopleTB WHERE N='" + name + "'"

        cmd = New OleDbCommand(sql, con)

        'OPEN CON,EXECUTE DELETE,CLOSE CON
        Try
            con.Open()

            adapter = New OleDbDataAdapter(cmd)

            adapter.DeleteCommand = con.CreateCommand()
            adapter.DeleteCommand.CommandText = sql

            'PROMPT FOR CONFIRMATION

            If MessageBox.Show("Sure ??", "DELETE", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.OK Then
                If cmd.ExecuteNonQuery() > 0 Then
                    MsgBox("Successfully deleted")
                    CleartextBoxes()
                End If
            End If

            con.Close()

            Retrieve()
        Catch ex As Exception
            MsgBox(ex.Message)
            con.Close()
        End Try

    End Sub

    Private Sub CleartextBoxes()
        nameTxt.Text = ""
        positionTxt.Text = ""
        teamTxt.Text = ""
    End Sub

    'ADD BTN CLICKED
    Private Sub addBtn_Click(sender As Object, e As EventArgs) Handles addBtn.Click
        Add()
    End Sub

    Private Sub retrieveBtn_Click(sender As Object, e As EventArgs) Handles retrieveBtn.Click
        Retrieve()
    End Sub

    Private Sub updateBtn_Click(sender As Object, e As EventArgs) Handles updateBtn.Click
        Dim name As String = ListView1.SelectedItems(0).SubItems(0).Text
        UpdateLV(name)
    End Sub

    Private Sub ListView1_MouseClick(sender As Object, e As MouseEventArgs) Handles ListView1.MouseClick
        Dim name As String = ListView1.SelectedItems(0).SubItems(0).Text
        Dim pos As String = ListView1.SelectedItems(0).SubItems(1).Text
        Dim team As String = ListView1.SelectedItems(0).SubItems(2).Text

        nameTxt.Text = name
        positionTxt.Text = pos
        teamTxt.Text = team
    End Sub

    Private Sub deleteBtn_Click(sender As Object, e As EventArgs) Handles deleteBtn.Click
        Dim name As String = ListView1.SelectedItems(0).SubItems(0).Text
        Delete(name)
    End Sub
End Class

 

Video/Preview

https://www.youtube.com/watch?v=Newv-ea-yRg

How To Run

  1. Just copy the code above.
  2. Make some changes with regards to the path to your database. You need to create a database in ms access and copy its path to our connection string.
  3. Make sure the columns in your table match the fields in the project. That’s it.

We’ve seen how to perform CRUD operations with VB.NET and access database.To establish our connection to MS Access,we have used OleDBConnection class.

More

YouTube

  • Visit our channel for more examples like these.

Facebook

Oclemy,Cheers.

How do You Feel after reading this?

According to scientists, we humans have 8 primary innate emotions: joy, acceptance, fear, surprise, sadness, disgust, anger, and anticipation. Feel free to tell us how you feel about this article using these emotes or via the comment section. This feedback helps us gauge our progress.

Help me Grow.

I set myself some growth ambitions I desire to achieve by this year's end regarding this website and my youtube channel. Am halfway. Help me reach them by:




Recommendations


What do You Think


Previous Post Next Post