Showing posts with label User Profile System Using Visual Basic NET. Show all posts
Showing posts with label User Profile System Using Visual Basic NET. Show all posts

Friday, January 18, 2019

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