In this article, I would like to share with you an application that I wrote in Microsoft Visual Basic NET and MySQL I would like to thank my close friend, business partner, and fellow software engineer Mr. Larry Lacapag Emol who help me understand how to create CRUD in VB.NET and MySQL. Please install MySQL Net/connector 5.2 with MySQL Server 5.0 run in Visual Studio 2010 Express Edition before you run the code to avoid unnecessary errors in the program.
I am currently accepting programming work, IT projects, school and application development, programming projects, thesis and capstone projects, IT consulting work, computer tutorials, and web development work kindly contact me in the following email address for further details. If you want to advertise on my website kindly contact me also in my email address also. Thank you.
My email address is the following jakerpomperada@gmail.com, jakerpomperada@aol.com, and jakerpomperada@yahoo.com.
My mobile number here in the Philippines is 09173084360.
My telephone number at home here in Bacolod City, Negros Occidental Philippines is +63 (034) 4335675.
Here in Bacolod I also accepting computer repair, networking and Arduino Project development at a very affordable price.
My personal website is http://www.jakerpomperada.com
Sample Program Output
Program Listing
Public Class Form1
Public Sub clear()
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""
Me.TextBox4.Text = ""
Me.TextBox1.Focus()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
connect()
DS = New DataSet
query = "Select * from tblInformation"
DA = New MySql.Data.MySqlClient.MySqlDataAdapter(query, conn)
DA.Fill(DS)
DataGridView1.DataSource = DS.Tables(0)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
cmd = New MySql.Data.MySqlClient.MySqlCommand
cmd.Connection = conn
query = "insert into tblInformation values('" & "" & "','" & UCase(TextBox1.Text) & "','" & UCase(TextBox2.Text) & "','" & UCase(TextBox3.Text) & "','" & TextBox4.Text & "')"
cmd.CommandText = query
cmd.ExecuteNonQuery()
LoadData()
MsgBox("Successfully Added", MsgBoxStyle.Information, "Save")
End Sub
Sub LoadData()
DS = New DataSet
query = "Select * from tblInformation"
DA = New MySql.Data.MySqlClient.MySqlDataAdapter(query, conn)
DA.Fill(DS)
Me.DataGridView1.DataSource = DS.Tables(0)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
cmd = New MySql.Data.MySqlClient.MySqlCommand
cmd.Connection = conn
query = "update tblInformation set FIRSTNAME='" & UCase(TextBox1.Text) & "',MIDDLENAME='" & UCase(TextBox2.Text) & "',LASTNAME='" & UCase(TextBox3.Text) & "',AGE='" & TextBox4.Text & "' where ID='" & Me.TextBox5.Text & "'"
cmd.CommandText = query
cmd.ExecuteNonQuery()
LoadData()
MsgBox("Successfully Updated", MsgBoxStyle.Information, "Updated")
End Sub
Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
Dim i As Integer
i = DataGridView1.CurrentRow.Index
Me.TextBox1.Text = DataGridView1.Item(1, i).Value
Me.TextBox2.Text = DataGridView1.Item(2, i).Value
Me.TextBox3.Text = DataGridView1.Item(3, i).Value
Me.TextBox4.Text = DataGridView1.Item(4, i).Value
Me.TextBox5.Text = DataGridView1.Item(0, i).Value
End Sub
Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
If MsgBox("Do you want to delete data", vbInformation + vbYesNo) = vbYes Then
cmd = New MySql.Data.MySqlClient.MySqlCommand
cmd.Connection = conn
query = "Delete from tblInformation where ID='" & Me.TextBox5.Text & "'"
cmd.CommandText = query
cmd.ExecuteNonQuery()
LoadData()
MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
End If
End Sub
Private Sub TextBox6_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox6.TextChanged
Filterdata(TextBox6.Text)
End Sub
Sub Filterdata(ByVal valueToSearch As String)
Dim searchQuery As String = "Select * from tblInformation where FIRSTNAME like '%" & Me.TextBox6.Text & "%' OR LASTNAME like '%" & Me.TextBox6.Text & "%' OR MIDDLENAME like '%" & Me.TextBox6.Text & "%'"
Dim command As New MySql.Data.MySqlClient.MySqlCommand(searchQuery, conn)
Dim adapter As New MySql.Data.MySqlClient.MySqlDataAdapter(command)
Dim table As New DataTable()
adapter.Fill(table)
Me.DataGridView1.DataSource = table
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
End
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""
Me.TextBox4.Text = ""
Me.TextBox1.Focus()
End Sub
End Class
tblinformation.sql
-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jul 08, 2019 at 08:13 AM
-- Server version: 10.1.38-MariaDB
-- PHP Version: 7.1.28
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `tutorial`
--
-- --------------------------------------------------------
--
-- Table structure for table `tblinformation`
--
CREATE TABLE `tblinformation` (
`ID` int(11) NOT NULL,
`FIRSTNAME` varchar(50) NOT NULL,
`MIDDLENAME` varchar(50) NOT NULL,
`LASTNAME` varchar(50) NOT NULL,
`AGE` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tblinformation`
--
INSERT INTO `tblinformation` (`ID`, `FIRSTNAME`, `MIDDLENAME`, `LASTNAME`, `AGE`) VALUES
(2, 'LARRY DAVE', 'LACAPAG', 'EMOL', '24'),
(3, 'JAKE', 'RODRIGUEZ', 'POMPERADA', '41'),
(5, 'MA. JUNALLIE ', 'FUENTEBELLA', 'POMPERADA', '47'),
(7, 'JACOB SAMUEL', 'FUENTEBELLA', 'POMPERADA', '6'),
(8, 'JULIANNA RAE', 'FUENTEBELLA', 'POMPERADADA', '5');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tblinformation`
--
ALTER TABLE `tblinformation`
ADD PRIMARY KEY (`ID`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tblinformation`
--
ALTER TABLE `tblinformation`
MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
