I started learning and working again with Visual Basic NET because our application in my workplace is not working it is written in Microsoft Access but after a day I was able to make the application works again by fixing the printer configuration. While I'm learning Visual Basic NET I have decided to create a User Profile System which uses MySQL as it's backend database and Crystal Report for report generation.
Actually, this will be my first time to use MySQL as my backend database and Crystal Report for my report generation. Most of the time when I am writing a Visual Basic NET program my backend is Microsoft Access. I want my program to become scalable by using MySQL as my backend. In terms of reporting Crystal Report is much better compared to the built-in reporting tool in Visual Basic NET. I am having a hard time understanding first it's concepts and all the configuration like MySQL ODBC connector configuration but I was able to connect all of them correctly in my application. Remember Crystal Report is a third party product of SAP, not a Microsoft product which requires proper and correct configuration in order to work properly within your application.
Overall my learning process is a very productive and enjoyable one for me. I learned a lot through trial and error process, use of analytical thinking skills and never give up attitude towards programming.
I hope you will find my work useful. Thank you.
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.
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
My personal website is http://www.jakerpomperada.com
Screenshots of the Sample Program Output
Program Listing
Form1 Code
Imports MySql.Data.MySqlClient
'Simple CRUD application in Visual Basic NET and MySQL with Crystal Report
' Written By Mr. Jake R. Pomperada,MAED-IT
' January 19, 2019 Friday
' jakerpomperada@gmail.com and jakerpomperada@aol.com
' http://www.jakerpomperada.com
Public Class Form1
Dim str As String = "server=localhost; uid=root; pwd=; database=dbprofile"
Dim con As New MySqlConnection(Str)
Sub loadme()
Label6.Text = "Date : " & Date.Today
Label7.Text = "Time : " & DateTime.Now.ToString("HH:mm:ss tt")
Dim query As String = "select * from profile"
Dim adpt As New MySqlDataAdapter(query, con)
Dim ds As New DataSet()
adpt.Fill(ds, "users")
DataGridView1.DataSource = ds.Tables(0)
con.Close()
TextBox1.Clear()
TextBox2.Clear()
TextBox3.Clear()
TextBox4.Clear()
TextBox1.Focus()
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
loadme()
End Sub
Private Sub btn_add_Click(sender As Object, e As EventArgs) Handles btn_add.Click
Dim cmd As MySqlCommand
con.Open()
Try
cmd = con.CreateCommand
cmd.CommandText = "insert into profile(id,name,mobile,email)values(@id,@name,@mobile,@email);"
cmd.Parameters.AddWithValue("@id", TextBox1.Text)
cmd.Parameters.AddWithValue("@name", UCase(TextBox2.Text))
cmd.Parameters.AddWithValue("@mobile", TextBox3.Text)
cmd.Parameters.AddWithValue("@email", LCase(TextBox4.Text))
cmd.ExecuteNonQuery()
MessageBox.Show("Record has been saved successfully", "Save Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
loadme()
Catch ex As Exception
End Try
End Sub
Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
Dim row As DataGridViewRow = DataGridView1.CurrentRow
Try
TextBox1.Text = row.Cells(0).Value.ToString()
TextBox2.Text = row.Cells(1).Value.ToString()
TextBox3.Text = row.Cells(2).Value.ToString()
TextBox4.Text = row.Cells(3).Value.ToString()
Catch ex As Exception
End Try
End Sub
Private Sub btn_update_Click(sender As Object, e As EventArgs) Handles btn_update.Click
Dim cmd As MySqlCommand
con.Open()
Try
cmd = con.CreateCommand()
cmd.CommandText = "update profile set name=@name,mobile=@mobile,email=@email where id=@id;"
cmd.Parameters.AddWithValue("@id", TextBox1.Text)
cmd.Parameters.AddWithValue("@name", UCase(TextBox2.Text))
cmd.Parameters.AddWithValue("@mobile", TextBox3.Text)
cmd.Parameters.AddWithValue("@email", LCase(TextBox4.Text))
cmd.ExecuteNonQuery()
MessageBox.Show("Record has been updated successfully", "Update Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
loadme()
Catch ex As Exception
End Try
End Sub
Private Sub btn_delete_Click(sender As Object, e As EventArgs) Handles btn_delete.Click
Dim cmd As MySqlCommand
con.Open()
Try
Select Case MsgBox("Are you sure to delete this record?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Delete Record")
Case MsgBoxResult.Yes
cmd = con.CreateCommand()
cmd.CommandText = "delete from profile where id=@id;"
cmd.Parameters.AddWithValue("@id", TextBox1.Text)
cmd.ExecuteNonQuery()
MessageBox.Show("Record has been deleted successfully", "Delete Record", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
loadme()
Case MsgBoxResult.No
loadme()
End Select
Catch ex As Exception
End Try
End Sub
Private Sub btn_reset_Click(sender As Object, e As EventArgs) Handles btn_reset.Click
loadme()
End Sub
Private Sub btn_quit_Click(sender As Object, e As EventArgs) Handles btn_quit.Click
Select Case MsgBox("Are you sure to quit program?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Quit Program")
Case MsgBoxResult.Yes
End
Case MsgBoxResult.No
loadme()
End Select
End Sub
Private Sub TextBox5_TextChanged(sender As Object, e As EventArgs) Handles txtsearch.TextChanged
Dim adapater As MySqlDataAdapter
Dim ds As New DataSet
Try
con.Open()
adapater = New MySqlDataAdapter("select * from profile where name like '%" & txtsearch.Text & "%'", con)
adapater.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
con.Close()
TextBox1.Clear()
TextBox2.Clear()
TextBox3.Clear()
TextBox4.Clear()
Catch ex As Exception
End Try
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btn_about.Click
MessageBox.Show("This Program is Created By Mr. Jake R. Pomperada" & vbNewLine _
& vbNewLine _
& "Date : January 17, 2019 Thursday" & vbNewLine & vbNewLine _
& "Bacolod City, Negros Occidental Philippines" & vbNewLine _
& vbNewLine _
& "Website : http://www.jakerpomperada.com " & vbNewLine _
& vbNewLine _
& "Email Address : jakerpomperada@gmail.com" & vbNewLine _
, "About This Program", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
Private Sub btn_print_Click(sender As Object, e As EventArgs) Handles btn_print.Click
Form2.Show()
End Sub
End Class
Form2 Code
Imports CrystalDecisions.CrystalReports.Engine
Public Class Form2
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim cryRpt As New ReportDocument
cryRpt.Load("D:\crud_vb_mysql\crud\crud\CrystalReport7.rpt")
CrystalReportViewer1.ReportSource = cryRpt
CrystalReportViewer1.Refresh()
End Sub
Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub
End Class
dbprofile.sql
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 10.1.37-MariaDB : Database - dbprofile
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`dbprofile` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `dbprofile`;
/*Table structure for table `profile` */
DROP TABLE IF EXISTS `profile`;
CREATE TABLE `profile` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`mobile` varchar(20) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*Data for the table `profile` */
insert into `profile`(`id`,`name`,`mobile`,`email`) values (1,'LYDIA POMPERADA','091212121223','lydiapomperada@yahoo.com'),(2,'JAKE POMPERADA','09173084360','jakerpomperada@gmail.com'),(3,'KOBE BRYANT','3434','kobe@lakers.com'),(4,'PETER NORTON','34343','peternorton@gmail.com'),(5,'ROMMEL ADDUCUL','09173949346','rommel@hotmail.com'),(6,'WILLIAM GATES','34434343','gates@taiwan.tw'),(7,'LESLIE PEPITO','12121212','leslie@ford.com');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
