Categories
VB.Net

Save/Retrieve Image in SQL Server Database Using VB.Net

In this program you’ll learn how to save and retrieve an image that stored in database with VB.NET and MySQL Database.

We need to create a Module copy the codes below for database connection in order to connect to the SQL Server database and put the following code inside it.

  Imports System.Data.SqlClient


Module Module1
  Public conn As SqlConnection
  Public query As String
  Public DA As SqlDataAdapter
  Public DS As DataSet
  Public cmd As SqlCommand

  Sub connect()
  conn = New SqlConnection
  conn.ConnectionString = "data source=(local); initial catalog=Tutorial;Trusted_Connection=Yes; user=; password="

  Try
  If conn.State = ConnectionState.Closed Then
  conn.Open()
  MsgBox("Connection Success")
  End If
  Catch ex As Exception
  MsgBox("Error Connection" & Err.Description)
  End Try
  End Sub
End Module

Create form to your project and double click the form to enter the code window Copy the code below and paste then save it.

 Imports System.Data.SqlClient
Imports System.IO

Public Class Form1
    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 SqlDataAdapter(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
        Dim sql As String = "INSERT INTO tblInformation VALUES(@FIRSTNAME,@MIDDLENAME,@LASTNAME,@AGE,@PHOTO)"
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.AddWithValue("@FIRSTNAME", TextBox1.Text)
        cmd.Parameters.AddWithValue("@MIDDLENAME", TextBox2.Text)
        cmd.Parameters.AddWithValue("@LASTNAME", TextBox2.Text)
        cmd.Parameters.AddWithValue("@AGE", TextBox2.Text)
        Dim ms As New MemoryStream()
        PictureBox1.BackgroundImage.Save(ms, PictureBox1.BackgroundImage.RawFormat)
        Dim data As Byte() = ms.GetBuffer()
        Dim p As New SqlParameter("@PHOTO", SqlDbType.Image)
        p.Value = data
        cmd.Parameters.Add(p)
        cmd.ExecuteNonQuery()
        MessageBox.Show("Data & Image has been saved", "Save", MessageBoxButtons.OK)
        LoadData()
    End Sub

    Sub LoadData()
        DS = New DataSet
        query = "Select * from tblInformation"
        DA = New SqlDataAdapter(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 SqlCommand
        cmd.Connection = conn

        query = "update tblInformation set FIRSTNAME='" & TextBox1.Text & "',MIDDLENAME='" & TextBox2.Text & "',LASTNAME='" & 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_CellMouseClick(ByVal sender As Object, ByVal e As  _
                 System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
        cmd = New SqlCommand("select PHOTO from tblInformation where FIRSTNAME='" & _
                   DataGridView1.CurrentRow.Cells(1).Value() & "'", conn)
        Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
        If Not imageData Is Nothing Then
            Using ms As New MemoryStream(imageData, 0, imageData.Length)
                ms.Write(imageData, 0, imageData.Length)
                PictureBox1.Image = Image.FromStream(ms, True)
            End Using
        End If

        Me.TextBox7.Text = DataGridView1.CurrentRow.Cells(0).Value()
    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 SqlCommand
            cmd.Connection = conn

            query = "Delete 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 SqlCommand(searchQuery, conn)
        Dim adapter As New SqlDataAdapter(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
        Me.PictureBox1.Image = Nothing
        If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            PictureBox1.BackgroundImage = Image.FromFile(OpenFileDialog1.FileName)
        End If
    End Sub
End Class

Download the complete source code for more information about the system. You can contact me @
Email – larrydaveemol@gmail.com
FB Account – https://www.facebook.com/larrydavelacapagemol

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!

Categories
VB.Net

Login and out time with Registration in VB.6

This is a sample login application that catch duplicate login and out in the system in VB6. Let’s start coding.

Create form to your project and double click the form to enter the code window Copy the code below and paste then save.

Private Sub lvButtons_H2_Click()
Unload Me
End Sub


Private Sub Command2_Click()
On Error Resume Next
 Set rstUserAcct = New ADODB.Recordset
    If rstUserAcct.State = 1 Then rstUserAcct.Close
    rstUserAcct.Open "Select * from tblInfo", MyConn, adOpenDynamic, adLockBatchOptimistic
 
    If Me.Text1.Text = "" Then
    MsgBox "Enter Firstname", vbInformation, "Firstname"
    Me.Text1.SetFocus
    Exit Sub
    End If
 
    If Me.Text3.Text = "" Then
    MsgBox "Enter Lastname", vbInformation, "Lastname"
    Me.Text3.SetFocus
    Exit Sub
    End If
 
    rstUserAcct.AddNew
    rstUserAcct.Fields("FIRSTNAME") = Me.Text1.Text
    rstUserAcct.Fields("MIDDLENAME") = Me.Text2.Text
    rstUserAcct.Fields("LASTNAME") = Me.Text3.Text
    rstUserAcct.Fields("POSITION") = "Student"
    rstUserAcct.UpdateBatch
 
    MsgBox "Registered Successfully", vbInformation, "Save"
    Me.Visible = False
    Mainform.Show
    Mainform.Text1.Text = Me.Text1.Text & " " & Me.Text2.Text & " " & Me.Text3.Text
End Sub

Private Sub Text3_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
Command2_Click
End If
End Sub
Private Sub Command1_Click()
On Error Resume Next
 Set rstUserAcct = New ADODB.Recordset
    If rstUserAcct.State = 1 Then rstUserAcct.Close
    rstUserAcct.Open "Select * from tblTimeInOut where Namex like'%" & Me.Text1.Text & "%' and Datex like'%" & Me.Text2.Text & "'", MyConn, adOpenDynamic, adLockBatchOptimistic
 
 
    If rstUserAcct.RecordCount = 1 Then
        If rstUserAcct.Fields("TimeoutAM") = "NONE" Then
        Else
        MsgBox "You are already Logout!", vbExclamation, "AM"
        Exit Sub
        End If
        rstUserAcct.Fields("TimeoutAM") = Time
        rstUserAcct.UpdateBatch
        MsgBox "Logout Successfully", vbInformation, "AM"
    Else
         MsgBox "WHO ARE YOU!", vbExclamation, "System"
    End If
End Sub

Private Sub Command2_Click()
On Error Resume Next
 Set rstUserAcct = New ADODB.Recordset
    If rstUserAcct.State = 1 Then rstUserAcct.Close
    rstUserAcct.Open "Select * from tblTimeInOut where Namex like'%" & Me.Text1.Text & "%' and Datex like'%" & Me.Text2.Text & "%'", MyConn, adOpenDynamic, adLockBatchOptimistic
 
    If rstUserAcct.Fields("TimeinAM") = Null Then
    rstUserAcct.AddNew
    rstUserAcct.Fields("Namex") = Me.Text1.Text
    rstUserAcct.Fields("Datex") = Me.Text2.Text
    rstUserAcct.Fields("TimeinAM") = Time
    rstUserAcct.Fields("TimeoutAM") = "NONE"
    rstUserAcct.Fields("TimeinPM") = "NONE"
    rstUserAcct.Fields("TimeoutPM") = "NONE"
    rstUserAcct.UpdateBatch
    MsgBox "Login Successfully", vbInformation, "AM"
    Exit Sub
    Else
    MsgBox "You are already Login!", vbExclamation, "AM"
    Exit Sub
    End If
End Sub

Private Sub Command3_Click()
On Error Resume Next
 Set rstUserAcct = New ADODB.Recordset
    If rstUserAcct.State = 1 Then rstUserAcct.Close
    rstUserAcct.Open "Select * from tblTimeInOut where Namex like'%" & Me.Text1.Text & "%' and Datex like'%" & Me.Text2.Text & "'", MyConn, adOpenDynamic, adLockBatchOptimistic
 
 
    If rstUserAcct.RecordCount = 1 Then
        If rstUserAcct.Fields("TimeinPM") = "NONE" Then
        Else
        MsgBox "You are already Login!", vbExclamation, "PM"
        Exit Sub
        End If
        rstUserAcct.Fields("TimeinPM") = Time
        rstUserAcct.UpdateBatch
        MsgBox "Login Successfully", vbInformation, "PM"
    Else
         MsgBox "WHO ARE YOU!", vbExclamation, "System"
    End If
End Sub

Private Sub Command4_Click()
On Error Resume Next
 Set rstUserAcct = New ADODB.Recordset
    If rstUserAcct.State = 1 Then rstUserAcct.Close
    rstUserAcct.Open "Select * from tblTimeInOut where Namex like'%" & Me.Text1.Text & "%' and Datex like'%" & Me.Text2.Text & "'", MyConn, adOpenDynamic, adLockBatchOptimistic
 
 
    If rstUserAcct.RecordCount = 1 Then
        If rstUserAcct.Fields("TimeoutPM") = "NONE" Then
        Else
        MsgBox "You are already Logout!", vbExclamation, "PM"
        Exit Sub
        End If
        rstUserAcct.Fields("TimeoutPM") = Time
        rstUserAcct.UpdateBatch
        MsgBox "Logout Successfully", vbInformation, "PM"
    Else
         MsgBox "WHO ARE YOU!", vbExclamation, "System"
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
End
End Sub

Private Sub Timer1_Timer()
Me.Label1.Caption = Date & " - " & Time
Me.Text2.Text = Date
End Sub

Download the complete source code for more information about the system. You can contact me @
Email – larrydaveemol@gmail.com
FB Account – https://www.facebook.com/larrydavelacapagemol

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!

Categories
VB.Net

Stored Login and out time with Registration in VB.Net & SQL Server

This is a sample login application that catch duplicate login and out in the system in VB.NET and MySQL Database. Let’s start coding.

Create form to your project and double click the form to enter the code window Copy the code below and paste then save.

 Public Class Form1

    Private Sub Logins()
        Try
            query = "SELECT * FROM tblsecurity WHERE USERNAME = '" & txtUser.Text & "' AND PASSWORD = '" & txtPwd.Text & "'"
            cmd = New SqlClient.SqlCommand(query, conn)
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.Read = True Then
                dr.Close()
                cmd.Dispose()
                conn.Close()
                timein()
            Else
                MsgBox("Incorrect username or password!", MsgBoxStyle.Critical, "Login")
                txtPwd.Focus()
                dr.Close()
                cmd.Dispose()
                conn.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
    End Sub

    Private Sub btnLogin_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Logins()
    End Sub

    Private Sub btncancel_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
        If MsgBox("Are you sure you want to close?", MsgBoxStyle.YesNo, "Close Window") = MsgBoxResult.Yes Then
            End
        End If
        txtUser.Focus()
    End Sub

    Private Sub txtPwd_GotFocus1(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPwd.GotFocus
        AcceptButton = btnLogin
    End Sub

    Private Sub txtUser_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtUser.GotFocus
        AcceptButton = btnLogin
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connect()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If (txtid.Text <> vbNullString And txtfn.Text <> vbNullString And _
               txtmn.Text <> vbNullString And txtln.Text <> vbNullString And _
               txtun.Text <> vbNullString And txtp.Text <> vbNullString And _
               txtrp.Text <> vbNullString) Then

            If Me.txtp.Text = Me.txtrp.Text = False Then
                MsgBox("Password didn't match!")
                txtp.Text = ""
                txtrp.Text = ""
            Else
                Dim DS As DataSet = New DataSet
                Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter

                DA.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblsecurity WHERE ID='" & txtid.Text & "'", conn)
                DS.Clear()
                DA.Fill(DS)

                If DS.Tables(0).Rows.Count = 0 Then
                    query = "INSERT INTO tblsecurity VALUES ('" & Me.txtid.Text & "','" & txtfn.Text & "','" & txtmn.Text & "','" & txtln.Text & "','" & _
                                txtun.Text & "','" & txtp.Text & "')"
                    cmd = New SqlClient.SqlCommand(query, conn)
                    cmd.ExecuteNonQuery()
                    cmd.Dispose()

                    MsgBox("New Information Added", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Success")
                    Me.txtfn.Text = ""
                    Me.txtmn.Text = ""
                    Me.txtln.Text = ""
                    Me.txtun.Text = ""
                    Me.txtp.Text = ""
                    Me.txtrp.Text = ""
                Else
                    MsgBox("The ID Number entered is already found? Please check information." & vbCrLf & vbCrLf & "If you think this is an error, " & _
                    "Please inform the Administrator.", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Duplicate ID Number found")
                    txtid.Focus()
                    Exit Sub
                End If

            End If
        Else
            MsgBox("Some fields are left blank. Please fill-up all information.", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Blank Fields found")
            txtid.Focus()
        End If
    End Sub

    Sub timein()
        conn.Open()
        Dim DS As DataSet = New DataSet
        Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        Dim dDR As DataRow

        DA.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblDTR WHERE Userx='" & txtUser.Text & "' and Datex='" & Format(Now, "MM/d/yyyy") & "'", conn)
        DS.Clear()
        DA.Fill(DS)

        If DS.Tables(0).Rows.Count = 0 Then

            TextBox1.Text = Format(Now, "h:mm:ss tt")
            query1 = "INSERT INTO tblDTR (Userx, Datex, Timein) VALUES ('" & txtUser.Text & "', '" & Format(Now, "MM/d/yyyy") & "', '" & TextBox1.Text & "')"
            cmd = New SqlClient.SqlCommand(query1, conn)
            cmd.ExecuteNonQuery()
            cmd.Dispose()

            Dim fx As New Form2
            Me.Hide()
            fx.Show()

        Else

            dDR = DS.Tables(0).Rows(0)
            TextBox2.Text = dDR("Timein")

            If Me.TextBox2.Text = "" Then
            Else
                MsgBox("Wag kang tanga nka Login kana :D !", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Duplicate Login")
                txtid.Focus()
                Exit Sub
            End If
            
        End If

    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Me.Label1.Text = Format(Now, "MMMM d, yyyy  h:mm:ss tt")
    End Sub
End Class
Public Class Form2
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim DS As DataSet = New DataSet
        Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        Dim dDR As DataRow

        DA.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblDTR WHERE Userx='" & Form1.txtUser.Text & "' and Datex='" & Format(Now, "MM/d/yyyy") & "'", conn)
        DS.Clear()
        DA.Fill(DS)

        dDR = DS.Tables(0).Rows(0)
        Form1.TextBox2.Text = dDR("Timeout").ToString

        If Form1.TextBox2.Text = "" Then
        Else
            MsgBox("Wag kang tanga nka Logout kana :D !", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Duplicate Logout")
            Exit Sub
        End If

            Form1.TextBox1.Text = Format(Now, "h:mm:ss tt")
            query1 = "UPDATE tblDTR SET Timeout='" & Form1.TextBox1.Text & "'"

            cmd = New SqlClient.SqlCommand(query1, conn)
            cmd.ExecuteNonQuery()
        cmd.Dispose()
        MsgBox("Logout Successfuly!", MsgBoxStyle.Information, "Logout")

    End Sub

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connect()
    End Sub
End Class

Download the complete source code for more information about the system. You can contact me @
Email – larrydaveemol@gmail.com

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!

Categories
VB.Net

Login with Vb.net and MySQL

This is a sample login application that display information after successful login along with your photo in VB.NET and MySQL Database. Let’s start coding.

Execute the SQL query to create a table

-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jul 14, 2019 at 02:37 PM
-- Server version: 10.1.40-MariaDB
-- PHP Version: 7.2.18

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 `tbluser`
--
CREATE TABLE `tbluser` (
  `ID` int(11) NOT NULL,
  `USERNAME` varchar(50) NOT NULL,
  `PASSWORD` varchar(50) NOT NULL,
  `IMAGE` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tbluser`
--
INSERT INTO `tbluser` (`ID`, `USERNAME`, `PASSWORD`, `IMAGE`) VALUES
(2, 'larrydaveemol@gmail.com', 'admin', 'C:\\Users\\Asus\\Desktop\\Client Tutorial\\Vb.Net and MySQL Login\\Crud Vb.Net and MySQL\\bin\\Debug\\me.jpg');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbluser`
--
ALTER TABLE `tbluser`
  ADD PRIMARY KEY (`ID`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbluser`
--
ALTER TABLE `tbluser`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
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 */;

After creating the table, we need to create a Module copy the codes below for database connection in order to connect to the MySQL database server and put the following code inside it.

Module Module1

    Public conn As MySql.Data.MySqlClient.MySqlConnection
    Public query As String
    Public DA As MySql.Data.MySqlClient.MySqlDataAdapter
    Public DS As DataSet
    Public cmd As MySql.Data.MySqlClient.MySqlCommand
    Public dr As MySql.Data.MySqlClient.MySqlDataReader


    Sub connect()
        conn = New MySql.Data.MySqlClient.MySqlConnection
        conn.ConnectionString = "server=127.0.0.1; uid=root; pwd=; database=tutorial"
     
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                'MsgBox("Connection Success")
            End If
        Catch ex As Exception
            MsgBox("Error Connection" & Err.Description)
        End Try
    End Sub
End Module

Create form to your project and double click the form to enter the code window Copy the code below and paste then save it.

Imports MySql.Data.MySqlClient
Public Class frmLogin

    Private Sub Login()
        Try
            query = "SELECT * FROM tbluser WHERE USERNAME = '" & txtUser.Text & "' AND PASSWORD = '" & txtPwd.Text & "'"
            connect()
            cmd = New MySql.Data.MySqlClient.MySqlCommand(query, conn)
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.Read = True Then
                form1.Label3.Text = dr("USERNAME")
                form1.Label4.Text = dr("PASSWORD")
                form1.PictureBox1.ImageLocation = dr("IMAGE")
                txtUser.Text = ""
                txtPwd.Text = ""
                form1.ShowDialog()
            Else
                MsgBox("Incorrect username or password!", MsgBoxStyle.Critical, "Login")
                txtPwd.Focus()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            conn.Close()
        End Try
    End Sub

    Private Sub btnLogin_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Login()
    End Sub

    Private Sub btncancel_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
        If MsgBox("Are you sure you want to close?", MsgBoxStyle.YesNo, "Close Window") = MsgBoxResult.Yes Then
            End
        End If
        txtUser.Focus()
    End Sub

    Private Sub txtPwd_GotFocus1(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPwd.GotFocus
        AcceptButton = btnLogin
    End Sub

    Private Sub txtUser_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtUser.GotFocus
        AcceptButton = btnLogin
    End Sub

End Class

For more information about the system. You can contact me @
Email – larrydaveemol@gmail.com

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!

Categories
VB.Net

Save/Retrieve Image in MySQL Database Using VB.Net

In this program you’ll learn how to save and retrieve an image that stored in database with VB.NET and MySQL Database. Let’s start by creating the table.

Execute the SQL query to create a table

-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jul 17, 2019 at 06:21 PM
-- Server version: 10.1.40-MariaDB
-- PHP Version: 7.2.18

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 `tblimage`
--
CREATE TABLE `tblimage` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(50) NOT NULL,
  `IMAGE` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tblimage`
--
INSERT INTO `tblimage` (`ID`, `NAME`, `IMAGE`) VALUES
(3, 'me.jpg', '');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tblimage`
--
ALTER TABLE `tblimage`
  ADD PRIMARY KEY (`ID`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tblimage`
--
ALTER TABLE `tblimage`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
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 */;

After creating the table, Add reference to your project: MySQL.data.dll for MySQL connection. We need to create a Module copy the codes below for database connection in order to connect to the MySQL database server and put the following code inside it.

 Module Module1

    Public conn As MySql.Data.MySqlClient.MySqlConnection
    Public query As String
    Public DA As MySql.Data.MySqlClient.MySqlDataAdapter
    Public DS As DataSet
    Public cmd As MySql.Data.MySqlClient.MySqlCommand
    Public dr As MySql.Data.MySqlClient.MySqlDataReader
    Public dt As DataTable

    Sub connect()
        conn = New MySql.Data.MySqlClient.MySqlConnection
        conn.ConnectionString = "server=127.0.0.1; uid=root; pwd=; database=tutorial"
       
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                'MsgBox("Connection Success")
            End If
        Catch ex As Exception
            MsgBox("Error Connection" & Err.Description)
        End Try
    End Sub
End Module

Create form to your project and double click the form to enter the code window Copy the code below and paste then save it.

Imports MySql.Data.MySqlClient
Imports System.IO

Public Class form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Try
            With OpenFileDialog1

                'CHECK THE SELECTED FILE IF IT EXIST OTHERWISE THE DIALOG BOX WILL DISPLAY A WARNING.
                .CheckFileExists = True

                'CHECK THE SELECTED PATH IF IT EXIST OTHERWISE THE DIALOG BOX WILL DISPLAY A WARNING.
                .CheckPathExists = True

                'GET AND SET THE DEFAULT EXTENSION
                .DefaultExt = "jpg"

                'RETURN THE FILE LINKED TO THE LNK FILE
                .DereferenceLinks = True

                'SET THE FILE NAME TO EMPTY 
                .FileName = ""

                'FILTERING THE FILES
                .Filter = "(*.jpg)|*.jpg|(*.png)|*.png|(*.jpg)|*.jpg|All files|*.*"
                'SET THIS FOR ONE FILE SELECTION ONLY.
                .Multiselect = False

                'SET THIS TO PUT THE CURRENT FOLDER BACK TO WHERE IT HAS STARTED.
                .RestoreDirectory = True

                'SET THE TITLE OF THE DIALOG BOX.
                .Title = "Select a file to open"

                'ACCEPT ONLY THE VALID WIN32 FILE NAMES.
                .ValidateNames = True

                If .ShowDialog = DialogResult.OK Then
                    Try
                        PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
                    Catch fileException As Exception
                        Throw fileException
                    End Try
                End If

            End With
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, Me.Text)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
     
        Dim sql As String
        Dim result As Integer
        Dim NAME As String
        Dim arrImage() As Byte
        Dim mstream As New System.IO.MemoryStream()

        NAME = System.IO.Path.GetFileName(OpenFileDialog1.FileName)
        'SPECIFIES THE FILE FORMAT OF THE IMAGE
        PictureBox1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)

        'RETURNS THE ARRAY OF UNSIGNED BYTES FROM WHICH THIS STREAM WAS CREATED
        arrImage = mstream.GetBuffer()

        'GET THE SIZE OF THE STREAM IN BYTES
        Dim FileSize As UInt32
        FileSize = mstream.Length
        'CLOSES THE CURRENT STREAM AND RELEASE ANY RESOURCES ASSOCIATED WITH THE CURRENT STREAM
        mstream.Close()
        Try
            conn.Open()
            sql = "INSERT INTO  `tblImage` (`NAME`, `IMAGE`) VALUES (@NAME, @IMAGE)"
            cmd = New MySql.Data.MySqlClient.MySqlCommand
            With cmd
                .Connection = conn
                .CommandText = sql
                .Parameters.AddWithValue("@NAME", Name)
                .Parameters.AddWithValue("@IMAGE", arrImage)
                result = .ExecuteNonQuery()
            End With
            If result > 0 Then
                MsgBox("Image successfully saved")
            Else
                MsgBox("Error query", MsgBoxStyle.Exclamation)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
        End Try
    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 tblImage"
        DA = New MySql.Data.MySqlClient.MySqlDataAdapter(query, conn)
        DA.Fill(DS)

        DataGridView1.DataSource = DS.Tables(0)
    End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try
            cmd = New MySqlCommand
            With cmd
                .Connection = conn
                .CommandText = "SELECT * FROM tblImage WHERE IMAGE=" & Val(Me.DataGridView1.CurrentRow.Cells(1).FormattedValue)
            End With
            DA = New MySqlDataAdapter
            dt = New DataTable
            Dim arrImage() As Byte

            DA.SelectCommand = cmd
            DA.Fill(dt)

            Label1.Text = dt.Rows(0).Item(1)
            arrImage = dt.Rows(0).Item(2)
            Dim mstream As New System.IO.MemoryStream(arrImage)
            PictureBox1.Image = Image.FromStream(mstream)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            DA.Dispose()
            conn.Close()
        End Try
    End Sub
End Class

Download the complete source code for more information about the system. You can contact me @
Email – larrydaveemol@gmail.com

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!

Categories
VB.Net

Capture Login and Logout Time in database using vb.net

In this program you’ll learn how to capture in and out time from the system and stored in database with VB.NET and SQL Server 2008 Express Edition. This method is very helpful for beginners…

 Public Class Form1

    Private Sub Logins()
        Try

            query = "SELECT * FROM tblsecurity WHERE USERNAME = '" & txtUser.Text & "' AND PASSWORD = '" & txtPwd.Text & "'"
            cmd = New SqlClient.SqlCommand(query, conn)
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.Read = True Then
                dr.Close()
                cmd.Dispose()
                conn.Close()
                timein()
            Else
                MsgBox("Incorrect username or password!", MsgBoxStyle.Critical, "Login")
                txtPwd.Focus()
                dr.Close()
                cmd.Dispose()
                conn.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
    End Sub


    Private Sub btnLogin_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Logins()
    End Sub

    Private Sub btncancel_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
        If MsgBox("Are you sure you want to close?", MsgBoxStyle.YesNo, "Close Window") = MsgBoxResult.Yes Then
            End
        End If
        txtUser.Focus()
    End Sub

    Private Sub txtPwd_GotFocus1(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPwd.GotFocus
        AcceptButton = btnLogin
    End Sub

    Private Sub txtUser_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtUser.GotFocus
        AcceptButton = btnLogin
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connect()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If (txtid.Text <> vbNullString And txtfn.Text <> vbNullString And _
               txtmn.Text <> vbNullString And txtln.Text <> vbNullString And _
               txtun.Text <> vbNullString And txtp.Text <> vbNullString And _
               txtrp.Text <> vbNullString) Then


            If Me.txtp.Text = Me.txtrp.Text = False Then
                MsgBox("Password didn't match!")
                txtp.Text = ""
                txtrp.Text = ""
            Else
                Dim DS As DataSet = New DataSet
                Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter

                DA.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblsecurity WHERE ID='" & txtid.Text & "'", conn)
                DS.Clear()
                DA.Fill(DS)

                If DS.Tables(0).Rows.Count = 0 Then
                    query = "INSERT INTO tblsecurity VALUES ('" & Me.txtid.Text & "','" & txtfn.Text & "','" & txtmn.Text & "','" & txtln.Text & "','" & _
                                txtun.Text & "','" & txtp.Text & "')"
                    cmd = New SqlClient.SqlCommand(query, conn)
                    cmd.ExecuteNonQuery()
                    cmd.Dispose()

                    MsgBox("New Information Added", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Success")
                    Me.txtfn.Text = ""
                    Me.txtmn.Text = ""
                    Me.txtln.Text = ""
                    Me.txtun.Text = ""
                    Me.txtp.Text = ""
                    Me.txtrp.Text = ""
                Else
                    MsgBox("The ID Number entered is already found? Please check information." & vbCrLf & vbCrLf & "If you think this is an error, " & _
                    "Please inform the Administrator.", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Duplicate ID Number found")
                    txtid.Focus()
                    Exit Sub
                End If

            End If
        Else
            MsgBox("Some fields are left blank. Please fill-up all information.", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Blank Fields found")
            txtid.Focus()
        End If
    End Sub

    Sub timein()
        conn.Open()
        Dim DS As DataSet = New DataSet
        Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        Dim dDR As DataRow

        DA.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblDTR WHERE Userx='" & txtUser.Text & "' and Datex='" & Format(Now, "MM/d/yyyy") & "'", conn)
        DS.Clear()
        DA.Fill(DS)

        If DS.Tables(0).Rows.Count = 0 Then

            TextBox1.Text = Format(Now, "h:mm:ss tt")
            query1 = "INSERT INTO tblDTR (Userx, Datex, Timein) VALUES ('" & txtUser.Text & "', '" & Format(Now, "MM/d/yyyy") & "', '" & TextBox1.Text & "')"
            cmd = New SqlClient.SqlCommand(query1, conn)
            cmd.ExecuteNonQuery()
            cmd.Dispose()

            Dim fx As New Form2
            Me.Hide()
            fx.Show()

        Else

            dDR = DS.Tables(0).Rows(0)
            TextBox2.Text = dDR("Timein")

            If Me.TextBox2.Text = "" Then
            Else
                MsgBox("Wag kang tanga nka Login kana :D !", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Duplicate Login")
                txtid.Focus()
                Exit Sub
            End If
            
        End If
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Me.Label1.Text = Format(Now, "MMMM d, yyyy  h:mm:ss tt")
    End Sub
End Class
Public Class Form2

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim DS As DataSet = New DataSet
        Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        Dim dDR As DataRow

        DA.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblDTR WHERE Userx='" & Form1.txtUser.Text & "' and Datex='" & Format(Now, "MM/d/yyyy") & "'", conn)
        DS.Clear()
        DA.Fill(DS)

        dDR = DS.Tables(0).Rows(0)
        Form1.TextBox2.Text = dDR("Timeout").ToString

        If Form1.TextBox2.Text = "" Then
        Else
            MsgBox("Wag kang tanga nka Logout kana :D !", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Duplicate Logout")
            Exit Sub
        End If

            Form1.TextBox1.Text = Format(Now, "h:mm:ss tt")
            query1 = "UPDATE tblDTR SET Timeout='" & Form1.TextBox1.Text & "'"

            cmd = New SqlClient.SqlCommand(query1, conn)
            cmd.ExecuteNonQuery()
        cmd.Dispose()
        MsgBox("Logout Successfuly!", MsgBoxStyle.Information, "Logout")

    End Sub

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connect()
    End Sub
End Class

Download the complete source code for more information about the system. You can contact me @
Email – larrydaveemol@gmail.com

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!