
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Public Class AdminEmpForm

    Private Sub AdminEmpForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'UserDBDataSet.UserTable' table. You can move, or remove it, as needed.
        Me.UserTableTableAdapter.Fill(Me.UserDBDataSet.UserTable)

        DataGridView1.GridColor = Color.Red
        DataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.None
        DataGridView1.BackgroundColor = Color.LightGray

        DataGridView1.DefaultCellStyle.SelectionBackColor = Color.Red
        DataGridView1.DefaultCellStyle.SelectionForeColor = Color.White

        DataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.[True]

        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige

        Me.BindData()
    End Sub
    Private Sub TextBox4_TextChanged(sender As Object, e As EventArgs) Handles TextBox4.TextChanged
        Dim query As String = "SELECT * FROM UserTable WHERE LASTNAME LIKE'%" & TextBox4.Text & "%' OR FIRSTNAME LIKE '%" & TextBox4.Text & "%'"
        Using con As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using cmd As SqlCommand = New SqlCommand(query, con)
                Using sda As New SqlDataAdapter()
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        DataGridView1.DataSource = dt

                        If dt.Rows.Count > 0 Then
                            TextBox1.Text = dt.Rows(0)(3).ToString()
                            TextBox2.Text = dt.Rows(0)(4).ToString()
                        Else
                            MessageBox.Show("No record found")
                            TextBox1.Text = ""
                            TextBox2.Text = ""
                        End If
                    End Using
                End Using
            End Using
        End Using
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim FIRSTNAME As String = TextBox1.Text
        Dim LASTNAME As String = TextBox2.Text
        Dim USTATUS As String = ComboBox1.SelectedItem
        If ComboBox1.Text = Nothing Or TextBox1.Text = Nothing Or TextBox2.Text = Nothing Then
            MessageBox.Show("Please enter the required fields!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Try
                Dim updatequery As String = "UPDATE UserTable SET USTATUS =@USTATUS WHERE FIRSTNAME=@FIRSTNAME AND LASTNAME=@LASTNAME"
                Using updatecon As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
                    Using cmdupdate As New SqlCommand(updatequery, updatecon)
                        cmdupdate.Parameters.AddWithValue("@FIRSTNAME", FIRSTNAME)
                        cmdupdate.Parameters.AddWithValue("@LASTNAME", LASTNAME)
                        cmdupdate.Parameters.AddWithValue("@USTATUS", USTATUS)
                        updatecon.Open()
                        cmdupdate.ExecuteNonQuery()
                        MessageBox.Show("Employee has been successfully updated!", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        updatecon.Close()
                        BindData()
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show("OPPS, WE HAVE ERROR FOUND", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim FIRSTNAME As String = TextBox1.Text
        Dim LASTNAME As String = TextBox2.Text

        If TextBox1.Text = Nothing Or TextBox2.Text = Nothing Then
            MessageBox.Show("Please enter the required fields!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Try
                Dim updatequery As String = "DELETE FROM UserTable WHERE FIRSTNAME=@FIRSTNAME AND LASTNAME=@LASTNAME"
                Using updatecon As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
                    Using cmdupdate As New SqlCommand(updatequery, updatecon)
                        cmdupdate.Parameters.AddWithValue("@FIRSTNAME", FIRSTNAME)
                        cmdupdate.Parameters.AddWithValue("@LASTNAME", LASTNAME)
                        updatecon.Open()
                        cmdupdate.ExecuteNonQuery()
                        MessageBox.Show("Employee has been successfully Delete!", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        updatecon.Close()
                        BindData()
                        TextBox1.Text = ""
                        TextBox2.Text = ""
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show("OPPS, WE HAVE ERROR FOUND", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    End Sub

    Private Sub BindData()
        Dim query As String = "SELECT * FROM UserTable"
        Using con As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using cmd As SqlCommand = New SqlCommand(query, con)
                Using sda As New SqlDataAdapter()
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        DataGridView1.DataSource = dt
                    End Using
                End Using
            End Using
        End Using
    End Sub
End Class