Save and Load Data in the Listview Using VB.Net and MySQL Database

Submitted by janobe on Friday, September 14, 2018 - 15:58.

If you find it hard to save and load the data in the Listview, this tutorial is just right for you. ListView is commonly used by many programmers to avoid logging in retrieving the data when the content is too much. By simply following the procedure below you will be able to perform it with ease.

Let's get started

Creating Database

Create a database named “test”

Create database test

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

CREATE TABLE `test`.`tblperson` ( `PersonID` INT NOT NULL AUTO_INCREMENT , `Fname`VARCHAR(90) NOT NULL , `Lname` VARCHAR(90) NOT NULL , `Mname` VARCHAR(90) NOT NULL, PRIMARY KEY (`PersonID`)) ENGINE = InnoDB;

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application. Then, do the form just like shown below. lst

Step 2

Add MySQL.Data.dll as references.

Step 3

Double click the form and set the imports above the “Public Class”.

Imports MySql.Data.MySqlClient

Step 4

Set a connection between MySQL Database and VB.Net. Then, 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 5

Do the following code inside the “form1_load” for retrieving data in the list view.

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(1))
                list.SubItems.Add(dr(2))
                list.SubItems.Add(dr(3))
            Loop
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try

Step 6

Go back to the design view, double click the “Save” button to fire the click event handler and do the following code for saving the data in the database.

Try
            con.Open()
            sql = "INSERT INTO tblperson (Fname,Mname,Lname) VALUES ('" & txtFname.Text & "','" & txtMname.Text & "','" & txtLname.Text & "')"
            cmd = New MySqlCommand
            With cmd
                .Connection = con
                .CommandText = sql
                result = .ExecuteNonQuery()
            End With
            If result = 1 Then
                MsgBox("Data has been saved 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)

The complete sourcecode is included. You can download it and run it on your computer. For more question about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT FB Account – https://www.facebook.com/onnaj.soicalap Or feel free to comment below.

Add new comment

1981 views