Lesson: Connecting VB.
NET 2010 to MySQL & Displaying Data in ListView
1. Prerequisites
✔ XAMPP installed and running MySQL
✔ MySQL Connector/NET 6.9.12 installed (MySql.Data.dll)
✔ Database created (vbdemo) with a users table
2. MySQL Table Setup
Run this SQL in phpMyAdmin (http://localhost/phpmyadmin):
CREATE DATABASE vbdemo;
USE vbdemo;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
3. VB.NET Form Design
In VB.NET 2010, create a form (Form1.vb) and add:
- TextBoxes: txtName, txtEmail
- Buttons: btnConnect, btnInsert, btnUpdate, btnDelete, btnLoadData
- ListView: lvUsers
Configure ListView (lvUsers)
1. Set View = Details
2. Add 3 Columns (ID, Name, Email)
3. Set FullRowSelect = True
4. VB.NET Code for MySQL Connection and ListView
Imports MySql.Data.MySqlClient
Public Class Form1
Dim conn As MySqlConnection
Dim cmd As MySqlCommand
Dim reader As MySqlDataReader
Dim connectionString As String = "server=localhost;user id=root;password=;database=vbdemo;"
' Function to Connect to MySQL
Private Sub btnConnect_Click(sender As Object, e As EventArgs) Handles btnConnect.Click
Try
conn = New MySqlConnection(connectionString)
conn.Open()
MessageBox.Show("Connected to MySQL successfully!", "Success", MessageBoxButtons.OK,
MessageBoxIcon.Information)
conn.Close()
Catch ex As Exception
MessageBox.Show("Connection failed: " & ex.Message, "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)
End Try
End Sub
……………………….--------------------------------------------------------------------------------------------------------------
' Function to Load Data into ListView
Private Sub LoadData()
Try
lvUsers.Items.Clear()
conn.Open()
Dim query As String = "SELECT * FROM users"
cmd = New MySqlCommand(query, conn)
reader = cmd.ExecuteReader()
While reader.Read()
Dim item As New ListViewItem(reader("id").ToString())
item.SubItems.Add(reader("name").ToString())
item.SubItems.Add(reader("email").ToString())
lvUsers.Items.Add(item)
End While
reader.Close()
conn.Close()
Catch ex As Exception
MessageBox.Show("Error loading data: " & ex.Message, "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)
End Try
End Sub
' Function to Insert Data
Private Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
Try
conn.Open()
Dim query As String = "INSERT INTO users (name, email) VALUES (@name, @email)"
cmd = New MySqlCommand(query, conn)
cmd.Parameters.AddWithValue("@name", txtName.Text)
cmd.Parameters.AddWithValue("@email", txtEmail.Text)
cmd.ExecuteNonQuery()
conn.Close()
MessageBox.Show("Data Inserted Successfully!", "Success", MessageBoxButtons.OK,
MessageBoxIcon.Information)
LoadData()
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
' Function to Update Selected Data
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
Try
If lvUsers.SelectedItems.Count = 0 Then
MessageBox.Show("Please select a record to update.", "Warning", MessageBoxButtons.OK,
MessageBoxIcon.Warning)
Return
End If
conn.Open()
Dim query As String = "UPDATE users SET name=@name, email=@email WHERE id=@id"
cmd = New MySqlCommand(query, conn)
cmd.Parameters.AddWithValue("@name", txtName.Text)
cmd.Parameters.AddWithValue("@email", txtEmail.Text)
cmd.Parameters.AddWithValue("@id", lvUsers.SelectedItems(0).Text)
cmd.ExecuteNonQuery()
conn.Close()
MessageBox.Show("Data Updated Successfully!", "Success", MessageBoxButtons.OK,
MessageBoxIcon.Information)
LoadData()
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
' Function to Delete Selected Data
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
Try
If lvUsers.SelectedItems.Count = 0 Then
MessageBox.Show("Please select a record to delete.", "Warning", MessageBoxButtons.OK,
MessageBoxIcon.Warning)
Return
End If
conn.Open()
Dim query As String = "DELETE FROM users WHERE id=@id"
cmd = New MySqlCommand(query, conn)
cmd.Parameters.AddWithValue("@id", lvUsers.SelectedItems(0).Text)
cmd.ExecuteNonQuery()
conn.Close()
MessageBox.Show("Data Deleted Successfully!", "Success", MessageBoxButtons.OK,
MessageBoxIcon.Information)
LoadData()
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
' Load Data When Form Loads
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
conn = New MySqlConnection(connectionString)
LoadData()
End Sub
' Reload Data on Button Click
Private Sub btnLoadData_Click(sender As Object, e As EventArgs) Handles btnLoadData.Click
LoadData()
End Sub
End Class
5. Summary
You have learned:
How to connect VB.NET 2010 to MySQL using XAMPP
How to display MySQL records in a ListView
How to Insert, Update, Delete records in MySQL
