Imports MySql.Data.MySqlClient
Public Class departmentdatafrm
    Dim conn As MySqlConnection
    Dim comm As MySqlCommand
    Dim dt As New DataTable

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

    End Sub
    Private Sub load_data()
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"
        Dim query As String
        query = "SELECT `id` as 'ID', `DepartmentName` as 'DEPARTMENT NAME', `DepartmentShortName` as 'DEPARTMENT SHORT NAME', `DepartmentCode` as 'DEPARTMENT CODE', `leavedays` as 'DAYS OF LEAVE' FROM `tbldepartments` "




        Try
            conn.Open()


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




            conn.Close()

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

        Finally
            conn.Dispose()
        End Try
    End Sub

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

    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)
            Label5.Text = row.Cells("ID").Value.ToString
            TextBox1.Text = row.Cells("DEPARTMENT NAME").Value.ToString
            TextBox2.Text = row.Cells("DEPARTMENT SHORT NAME").Value.ToString
            TextBox3.Text = row.Cells("DEPARTMENT CODE").Value.ToString




        End If
    End Sub

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

    End Sub

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

    End Sub

    Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click

    End Sub

    Private Sub Label7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label7.Click
        If Label5.Text > "" Then
            conn = New MySqlConnection
            conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"
            Dim mdr As MySqlDataReader
            Dim conn1 As New MySqlConnection
            Dim cmd1 As New MySqlCommand
            Dim dr As MySqlDataReader

            conn1 = New MySqlConnection
            conn1.ConnectionString = "server=localhost;userid=root;password=;database=elms"
            Try
                conn1.Open()
                cmd1.Connection = conn1
                cmd1.CommandText = "Select * from tbldepartments where DepartmentName = '" & TextBox1.Text & "' and DepartmentShortName = '" & TextBox2.Text & "' and DepartmentCode = '" & TextBox3.Text & "' "
                dr = cmd1.ExecuteReader
                If dr.HasRows Then
                    MsgBox("Duplicate Entry")
                    conn1.Close()
                Else
                    conn.Open()
                    Dim query As String
                    query = ("UPDATE elms.tbldepartments SET DepartmentName='" & TextBox1.Text & "', DepartmentShortName='" & TextBox2.Text & "',DepartmentCode='" & TextBox3.Text & "',leavedays='" & TextBox4.Text & "' WHERE id='" & Label5.Text & "' ")

                    comm = New MySqlCommand(query, conn)
                    mdr = comm.ExecuteReader
                    MessageBox.Show("Department Updated Success")

                    TextBox1.Text = ""
                    TextBox2.Text = ""
                    TextBox3.Text = ""
                    Label5.Text = ""
                    TextBox4.Text = ""

                    load_data()

                    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 TextBox4.Text = "" Then
            MsgBox("Fill All The Form ")
        Else
            conn = New MySqlConnection
            conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"
            Dim mdr As MySqlDataReader
            Dim conn2 As New MySqlConnection
            Dim cmd2 As New MySqlCommand
            Dim dr As MySqlDataReader
            conn2 = New MySqlConnection
            conn2.ConnectionString = "server=localhost;userid=root;password=;database=elms"
            Try
                conn2.Open()
                cmd2.Connection = conn2
                cmd2.CommandText = "Select * from tbldepartments where DepartmentName = '" & TextBox1.Text & "' and DepartmentShortName = '" & TextBox2.Text & "' and DepartmentCode = '" & TextBox3.Text & "' "
                dr = cmd2.ExecuteReader
                If dr.HasRows Then
                    MsgBox("Duplicate Entry")
                    conn2.Close()
                Else
                    conn.Open()
                    Dim query As String
                    query = ("INSERT INTO elms.tbldepartments(DepartmentName, DepartmentShortName, DepartmentCode,leavedays) VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')")
                    comm = New MySqlCommand(query, conn)
                    mdr = comm.ExecuteReader
                    MessageBox.Show("New Department Added Success")

                    TextBox1.Text = ""
                    TextBox2.Text = ""
                    TextBox3.Text = ""
                    TextBox4.Text = ""




                    load_data()
                    conn.Close()
                End If

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

            Finally
                conn.Dispose()
            End Try
        End If
    End Sub

    Private Sub Label5_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label5.TextChanged
        If Label5.Text > "" Then
            Label7.Text = "UPDATE"
        Else
            Label7.Text = "SAVE"
        End If
    End Sub

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

    End Sub

    Private Sub PictureBox3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox3.Click
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        Label5.Text = ""
        TextBox4.Text = ""


    End Sub
End Class