VB.NET MS Access + ListView – SAVE, RETRIEVE

VB.NET MS Access + ListView – SAVE, RETRIEVE



Rating : 5/5 (2)




Introduction

Performing CRUD operations is such an important part of management systems. Be it school management system, pharmacy management system or even point of sale systems, inserting and retrieving data is must. It’s why databases are there after all.
In this simple example, we see how to insert data to MS Access database, retrieve that data and bind it to a ListView. This is a vb.net tutorial.

Questions this Examples helps answer.

  • How to save and retrieve records to and from database.
  • How to show MS Access data in a ListView in vb.net.
  • How to save from textbox to access database.
  • How to use OleDBConnection class 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 CRUD

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

VB-NET CRUD

VB.NET MS ACCESS DATABASE

  • There’s our access database with data.

MS Access

Source Code

Lets have a look at the source code.

Form1.vbVideo/Preview
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
        'CONSTRUCT COLUMNS
        ListView1.Columns.Add("Name", 150)
        ListView1.Columns.Add("Position", 150)
        ListView1.Columns.Add("Team", 150)
    End Sub
    Private Sub Add(name As String, pos As String, team As String)
        'SQL STMT
        Dim sql As String = "INSERT INTO peopleTB(N,P,T) VALUES(@name,@pos,@te)"
        cmd = New OleDbCommand(sql, con)
        'PARAMETERS
        cmd.Parameters.AddWithValue("@name", name)
        cmd.Parameters.AddWithValue("@pos", pos)
        cmd.Parameters.AddWithValue("@te", team)
        'OPEN CON,EXECUTE,CLOSE CON
        Try
            con.Open()
            If cmd.ExecuteNonQuery() > 0 Then
                MsgBox("Successfully Added Data")
                nameTxt.Text = ""
                positionTxt.Text = ""
                teamTxt.Text = ""
            End If
            con.Close()
            Retrieve()
        Catch ex As Exception
            MsgBox(ex.Message)
            con.Close()
        End Try
    End Sub
    Private Sub addBtn_Click(sender As Object, e As EventArgs) Handles addBtn.Click
        Add(nameTxt.Text, positionTxt.Text, teamTxt.Text)
    End Sub
    'POPULATE OUR LISTVIEW
    Private Sub Populate(name As String, position As String, team As String)
        'ROW ARRAY
        Dim row As String() = New String() {name, position, team}
        Dim item As New ListViewItem(row)
        ListView1.Items.Add(item)
    End Sub
    'RETRIEVE FROM DB
    Private Sub Retrieve()
        ListView1.Items.Clear()
        'SQL STMT
        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)
            'FILL
            For Each row In dt.Rows
                Populate(row(1), row(2), row(3))
            Next
            'CLEAR DT
            dt.Rows.Clear()
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
            con.Close()
        End Try
    End Sub
    'retrieving
    Private Sub retrieveBtn_Click(sender As Object, e As EventArgs) Handles retrieveBtn.Click
        Retrieve()
    End Sub
    'clear
    Private Sub clearBtn_Click(sender As Object, e As EventArgs) Handles clearBtn.Click
        ListView1.Items.Clear()
    End Sub
    
End Class

 

VB.NET Access DataBase Ep.01 : ListView - ADD,RETRIEVE,POPULATE Dynamically

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.



Rating :

    Leave a Reply

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

    7 + 18 =

    COMMENTS