Imports MySql.Data.MySqlClient
Public Class userman

    Private Sub Label7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label7.Click
        Dim result As Integer
        Dim conn1 As New MySqlConnection
        Dim cmd1 As New MySqlCommand
        Dim dr As MySqlDataReader
        Dim cmd2 As New MySqlCommand
        Dim conn2 As New MySqlConnection

        If Label3.Text > "" Then
            conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"
            conn1.ConnectionString = "server=localhost;userid=root;password=;database=elms"



            Try
                conn1.Open()
                cmd1.Connection = conn1
                cmd1.CommandText = "Select * from tblacc where empId = '" & TextBox1.Text & "' and email = '" & TextBox2.Text & "' and password = '" & TextBox3.Text & "' "
                dr = cmd1.ExecuteReader
                If dr.HasRows Then
                    MsgBox("Duplicate Entry")
                    conn1.Close()
                Else
                    conn.Open()
                    With cmd

                        .Connection = conn
                        .CommandText = "UPDATE `tblacc` SET `empid`= '" & TextBox1.Text & "',`email`= '" & TextBox2.Text & "',`password`= '" & TextBox3.Text & "' ,`usertype`= '" & ComboBox1.Text & "' WHERE `id`= '" & Label3.Text & "'"
                        result = .ExecuteNonQuery
                        MessageBox.Show("Updated")
                        load_data()


                    End With
                    conn.Close()
                End If
            Catch ex As MySqlException
                MessageBox.Show(ex.Message)

            Finally
                conn.Dispose()
            End Try
        ElseIf TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or ComboBox1.Text = "" Then
            MsgBox("Fill All The Form")
        Else
            conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"
            conn2.ConnectionString = "server=localhost;userid=root;password=;database=elms"

            Try
                conn2.Open()
                cmd2.Connection = conn2
                cmd2.CommandText = "Select * from tblacc where empId = '" & TextBox1.Text & "' and email = '" & TextBox2.Text & "' and password = '" & TextBox3.Text & "' "
                dr = cmd2.ExecuteReader
                If dr.HasRows Then
                    MsgBox("Duplicate Entry")
                    conn2.Close()
                Else
                    conn.Open()
                    With cmd



                        .Connection = conn
                        .CommandText = "INSERT INTO `tblacc`(`empId`,`email`,`password`,`usertype`) VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & ComboBox1.Text & "')"

                        result = .ExecuteNonQuery
                        MessageBox.Show("Inserted")
                        load_data()


                    End With
                    conn.Close()
                End If

            Catch ex As MySqlException
                MessageBox.Show(ex.Message)

            Finally
                conn.Dispose()
            End Try


        End If
    End Sub

    Private Sub userman_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        load_data()
    End Sub
    Private Sub pass_load()

        Dim cmd1 As New MySqlCommand(("SELECT  `password` FROM tblacc WHERE `id`= '" & Label3.Text & "' "), conn)

        Try
            conn.Open()

            TextBox3.Text = cmd1.ExecuteScalar().ToString()
            conn.Close()

        Catch ex As Exception

        End Try





    End Sub
    Public Sub load_data()
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"
        Dim query As String
        query = " SELECT `id` as 'ID', `empid` as 'ACCOUNT CODE', `email` as 'EMAIL',`usertype` as 'USER STATUS' FROM `tblacc`  "



        Try
            conn.Open()


            Dim search As New MySqlDataAdapter(query, conn)
            Dim ds As New DataSet
            search.Fill(ds, "tblacc")
            DataGridView1.DataSource = ds.Tables("tblacc")
            conn.Close()

        Catch ex As MySqlException
            MessageBox.Show(ex.Message)

        Finally
            conn.Dispose()
        End Try
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.RowIndex >= 0 Then
            Dim row As DataGridViewRow
            row = Me.DataGridView1.Rows(e.RowIndex)
            Label3.Text = row.Cells("ID").Value.ToString
            TextBox1.Text = row.Cells("ACCOUNT CODE").Value.ToString
            TextBox2.Text = row.Cells("EMAIL").Value.ToString
            ComboBox1.Text = row.Cells("USER STATUS").Value.ToString
            






        End If

    End Sub

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


    End Sub

    Private Sub Label3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.TextChanged
        If Label3.Text > "" Then
            Label7.Text = "UPDATE"

        Else
            Label7.Text = "SAVE"
        End If
        If Label7.Text = "UPDATE" Then
            pass_load()
        Else
            TextBox3.Text = ""

        End If
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged

    End Sub

    Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click
        Label3.Text = ""
        TextBox1.Text = ""
        TextBox2.Text = ""

        ComboBox1.Text = ""
    End Sub
End Class