User Profile System Using Visual Basic NET, MySQL and Crystal Report

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.

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

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 */;

DOWNLOAD SOURCE CODE HERE