Update and Load Data in the ListView Using VB.Net

Submitted by janobe on Tuesday, September 18, 2018 - 09:33.

In this tutorial, I will teach you how to Update and Load Data in the ListView Using VB.Net. With this method, it will help you retrieve the data in the ListView and at the same time you can update the data. See the procedure below.

Creating Database

Create a database named “test”.

Create database test

After that, add a table in the database that you have created.

CREATE TABLE `tblperson` (
  `PersonID` int(11) NOT NULL,
  `Fname` varchar(90) NOT NULL,
  `Lname` varchar(90) NOT NULL,
  `Mname` varchar(90) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then, insert the data in the table.

INSERT INTO `tblperson` (`PersonID`, `Fname`, `Lname`, `Mname`) VALUES
(1, 'Janno', 'Palacios', 'E'),
(2, 'John Craig', 'Nillos', 'P');

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application. Then, do the form just like this. lst2

Step 2

Set the imports.

Imports MySql.Data.MySqlClient

Step 3

Create a connection between MySQL Database and VB.Net. After that, declare all the classes that are needed.

Dim con As New MySqlConnection("server=localhost;user id=root;Password=janobe;database=test;sslMode=none")
    Dim cmd As MySqlCommand
    Dim dr As MySqlDataReader
    Dim sql As String
    Dim result As Integer

Step 4

Add the following code for loading data inside the ListView in the first load of the form.

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            con.Open()
            sql = "SELECT * FROM tblperson"
            cmd = New MySqlCommand
            With cmd
                .Connection = con
                .CommandText = sql
                dr = .ExecuteReader()
            End With
            ListView1.Items.Clear()
            Do While dr.Read = True
                Dim list = ListView1.Items.Add(dr(0))
                list.SubItems.Add(dr(1))
                list.SubItems.Add(dr(2))
                list.SubItems.Add(dr(3))
            Loop
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

Step 5

Do the following code for updating the data in the MySQL database.

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Try
            con.Open()
            sql = "UPDATE tblperson SET Fname='" & txtFname.Text & "', Mname='" & txtMname.Text & "',Lname='" & txtLname.Text & "' WHERE PersonID=" & ListView1.FocusedItem.SubItems(0).Text
            cmd = New MySqlCommand
            With cmd
                .Connection = con
                .CommandText = sql
                result = .ExecuteNonQuery()
            End With
            If result = 1 Then
                MsgBox("Data has been updated in the database.")
                txtFname.Clear()
                txtLname.Clear()
                txtMname.Clear()
            Else
                MsgBox("error query!", MsgBoxStyle.Exclamation)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
        Call Form1_Load(sender, e)
    End Sub

Step 6

Do the following code for passing the data from the listview to the textbox.

Private Sub ListView1_Click(sender As Object, e As EventArgs) Handles ListView1.Click
        Try
            txtFname.Text = ListView1.FocusedItem.SubItems(1).Text
            txtMname.Text = ListView1.FocusedItem.SubItems(2).Text
            txtLname.Text = ListView1.FocusedItem.SubItems(3).Text
        Catch ex As Exception
            MsgBox(ex.Message)
 
        End Try
    End Sub

Note: add MySQL.Data.dll as references to access MySQL Library.

Add new comment

1548 views