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


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

    End Sub

    Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click

    End Sub

    Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.Click

    End Sub



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

    End Sub

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

    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', `LeaveType` as 'LEAVE TYPE', `Description` as 'DESCRIPTION' FROM `tblleavetype`"



        Try
            conn.Open()


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




            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)
            Label5.Text = row.Cells("ID").Value.ToString
            TextBox1.Text = row.Cells("LEAVE TYPE").Value.ToString
            TextBox2.Text = row.Cells("DESCRIPTION").Value.ToString



        End If
    End Sub

    Private Sub Label6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label6.Click
        Dim result As Integer
        Dim conn1 As New MySqlConnection
        Dim cmd1 As New MySqlCommand
        Dim dr As MySqlDataReader
        conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"
        conn1.ConnectionString = "server=localhost;userid=root;password=;database=elms"

        If Label5.Text > "" Then
            Try

                With cmd
                    conn1.Open()
                    cmd1.Connection = conn1
                    cmd1.CommandText = "Select * from tblleavetype where LeaveType = '" & TextBox1.Text & "' and Description = '" & TextBox2.Text & "' "
                    dr = cmd1.ExecuteReader
                    If dr.HasRows Then
                        MsgBox("Duplicate Entry")
                        conn1.Close()
                    Else

                        .Connection = conn
                        .CommandText = "update tblleavetype set LeaveType= '" & TextBox1.Text & "', Description = '" & TextBox2.Text & "' where id = '" & Label5.Text & "' "

                        result = .ExecuteNonQuery


                        MessageBox.Show("Updated")
                        load_data()

                    End If
                End With
                TextBox1.Text = ""
                TextBox2.Text = ""
                Label5.Text = ""



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

            Finally
                conn.Dispose()
            End Try
        ElseIf TextBox1.Text = "" Or TextBox2.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 cmd2 As New MySqlCommand

            conn2 = New MySqlConnection
            conn2.ConnectionString = "server=localhost;userid=root;password=;database=elms"
            Try
                conn2.Open()
                cmd2.Connection = conn2
                cmd2.CommandText = "Select * from tblleavetype where LeaveType = '" & TextBox1.Text & "' and Description = '" & TextBox2.Text & "' "
                dr = cmd2.ExecuteReader
                If dr.HasRows Then
                    MsgBox("Duplicate Entry")
                    conn2.Close()
                Else
                    conn.Open()
                    Dim query As String
                    query = ("INSERT INTO elms.tblleavetype (LeaveType, Description) VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "')")
                    comm = New MySqlCommand(query, conn)
                    mdr = comm.ExecuteReader
                    MessageBox.Show("Inserted")

                    TextBox1.Text = ""
                    TextBox2.Text = ""
                    Label5.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 Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

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

    End Sub

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

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