Imports MySql.Data.MySqlClient
Public Class DataEntry
    Public sCon As MySqlConnection = New MySqlConnection
    Public sComand As MySqlDataAdapter = New MySqlDataAdapter
    Public ds As New DataSet
    Dim scrollVal As Integer
    Dim pagingAdapter As New MySqlDataAdapter
    Dim pagingDS As New DataSet
    Dim pagingDS2 As New DataSet
    Private sRecord_Count As Integer
    ' Dim conn As New MySqlConnection
    Dim cmd As New MySqlCommand
    Dim dr As MySqlDataReader
    Dim connectionString As New MySqlConnection("server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706")
    'Private Sub conns()
    '    'Try
    '    conn = New MySqlConnection("server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706")
    '   conn1.Open()
    '    'Catch ex As Exception
    '    '    MsgBox(ex.Message)
    '    'End Try
    'End Sub
    'Private Sub conclos
    '    Try
    '        connectionString.Close()
    '        connectionString.Dispose()
    '       conn1.Close()
    '       conn1.Dispose()
    '        MySqlConnection.ClearPool(conn1)
    '        MySqlConnection.ClearPool(connectionString)
    '    Catch ex As Exception

    '    End Try

    'End Sub
    Private Sub hhldata_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        conn1.Close()
        conn1.Dispose()
        connectionString.Close()
        connectionString.Dispose()
        'MySqlConnection.ClearAllPools()
    End Sub
    Private Sub DataEntry_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CaragavotersdbDataSet.full_data' table. You can move, or remove it, as needed.
        'conns()
        'countdata()
        selectbargy()
        'selectpurok()
        'fillist()
        'namepco()
        'namesugg()
        pccid.Visible = False
        Label8.Visible = False
        conn1.Close()
        conn1.Dispose()
        connectionString.Close()
        connectionString.Dispose()
        'MySqlConnection.ClearAllPools()
    End Sub
    Private Sub selectinfo()
        'Try
        Call conns()
        Dim sqlcmd As New MySqlCommand("SELECT * FROM full_data where name = '" & ComboBox1.Text & "' AND barangay = '" & barangay.Text & "' AND address = '" & purok.Text _
                                            & "'", conn1)
        Dim sdr As MySqlDataReader
        sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
        While sdr.Read()
            birth.Text = sdr("birthday")
            gender.Text = sdr("Gender")
            precint.Text = sdr("precinct_number")
        End While
        conclos()
    End Sub
    Private Sub namesugg()
        'Try
        Dim adapter As New MySqlDataAdapter("SELECT * FROM full_data where barangay = '" & barangay.Text & "' AND address = '" & purok.Text _
                                            & "'  ORDER BY name ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        ' ComboBox1.DataSource = table
        With ComboBox1
            .DataSource = table
            .DisplayMember = "name"
            .ValueMember = "name"
            .DropDownStyle = ComboBoxStyle.DropDown
            .AutoCompleteMode = AutoCompleteMode.Suggest
            .AutoCompleteSource = AutoCompleteSource.ListItems
        End With
        conclos()
    End Sub
    Private Sub selectinfohhlpcc()
        'Try
        Call conns()
        Dim sqlcmd As New MySqlCommand("SELECT * FROM hhl where names = '" & hhl.Text & "' AND barangay =  '" & barangay.Text & "' AND purok = '" & purok.Text _
                                            & "'", conn1)
        Dim sdr As MySqlDataReader
        sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
        While sdr.Read()
            hhlpcc.Text = sdr("pcc")
            'gender.Text = sdr("Gender")
            'precint.Text = sdr("precinct_number")
        End While
        conclos()
        selectinfohhlbcc()
    End Sub
    Private Sub selectinfohhlpcc2()
        'Try
        Dim adapter As New MySqlDataAdapter("SELECT pco FROM full_data_entry  ORDER BY name ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        hhl.DataSource = table
        hhl.ValueMember = "pco"
        hhl.DisplayMember = "pco"
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(0)).Distinct.ToList
        hhl.DataSource = lst
        conclos()

    End Sub
    Private Sub selectinfohhlbcc()
        'Try
        Call conns()
        Dim sqlcmd As New MySqlCommand("SELECT * FROM pco where names = '" & hhlpcc.Text & "' AND barangay =  '" & barangay.Text & "' AND purok =  '" & purok.Text _
                                            & "'", conn1)
        Dim sdr As MySqlDataReader
        sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
        While sdr.Read()
            pccid.Text = sdr("id")
            'gender.Text = sdr("Gender")
            'precint.Text = sdr("precinct_number")
        End While
        conclos()
        selectinfopccbcc()
    End Sub
    Private Sub selectinfopccbcc()
        'Try
        Call conns()
        Dim sqlcmd As New MySqlCommand("SELECT * FROM bccofpcc where pccidno = '" & pccid.Text & "' AND bccbrgy = '" & barangay.Text & "' AND bccpurok = '" & purok.Text & "'", conn1)
        Dim sdr As MySqlDataReader
        sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
        While sdr.Read()
            hhlbcc.Text = sdr("bccname")
            'gender.Text = sdr("Gender")
            'precint.Text = sdr("precinct_number")
        End While
        conclos()
    End Sub
    Private Sub namepco()
        'Try
        Call conns()
        'conn.Open()
        Dim dtt As New DataTable()
        With cmd
            .Connection = conn1
            .CommandText = "SELECT * FROM hhl where barangay =  '" & barangay.Text & "' AND purok = '" & purok.Text _
                                        & "'  ORDER BY names ASC"
        End With
        Dim daa As New MySqlDataAdapter
        daa.SelectCommand = cmd
        daa.Fill(dtt)
        'Dim r As DataRow
        Dim sdr As MySqlDataReader
        sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If sdr.HasRows Then
            With hhl
                .DataSource = dtt
                .DisplayMember = "names"
                .ValueMember = "names"
                .DropDownStyle = ComboBoxStyle.DropDown
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.ListItems
            End With
            'daa.Dispose()
        Else
            With hhl
                .DataSource = dtt
                .DisplayMember = ""
                .ValueMember = ""
                .DropDownStyle = ComboBoxStyle.DropDown
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.ListItems
            End With
            hhl.Text = ""
            'Label10.Text = ""
        End If
        conclos()
    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.TextChanged, ComboBox1.KeyPress
        selectinfo()
        'selectbirth()
        'selectgender()
        'votemj.Text = ""
    End Sub

    Private Sub namereader()
        'Try
        Dim adapter As New MySqlDataAdapter("SELECT * FROM full_data where barangay = '" & barangay.Text & "' AND address = '" & purok.Text _
                                              & "'  ORDER BY name ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        ' ComboBox1.DataSource = table
        With ComboBox1
            .DataSource = table
            .DisplayMember = "name"
            .ValueMember = "name"
            .DropDownStyle = ComboBoxStyle.DropDown
            .AutoCompleteMode = AutoCompleteMode.Suggest
            .AutoCompleteSource = AutoCompleteSource.ListItems
        End With
        conclos()
    End Sub
    Private Sub selectbirth()
        'Try
        Dim adapter As New MySqlDataAdapter("SELECT * FROM full_data where name = '" & ComboBox1.Text & "' ORDER BY barangay ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        birth.DataSource = table
        birth.ValueMember = "birthday"
        birth.DisplayMember = "birthday"
        gender.DataSource = table
        gender.ValueMember = "Gender"
        gender.DisplayMember = "Gender"
        precint.DataSource = table
        precint.ValueMember = "precinct_number"
        precint.DisplayMember = "precinct_number"
        conclos()
    End Sub
    Private Sub selectgender()
        'Try
        Dim adapter As New MySqlDataAdapter("SELECT * FROM full_data where name = '" & ComboBox1.Text & "' ORDER BY barangay ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        gender.DataSource = table
        gender.ValueMember = "Gender"
        gender.DisplayMember = "Gender"
        conclos()
    End Sub
    Private Sub selectbargy()
        'Try
        If Dashboard.Label17.Text = "jeleen" Then
            barangay.Items.Clear()
            barangay.DropDownStyle = ComboBoxStyle.DropDownList
            barangay.Items.Add("POBLACION")
            barangay.SelectedItem = "POBLACION"
        Else
            Dim adapter As New MySqlDataAdapter("SELECT * FROM purok ORDER BY barangay ASC", connectionString)
            Dim table As New DataTable()
            adapter.Fill(table)
            barangay.DataSource = table
            barangay.ValueMember = "barangay"
            barangay.DisplayMember = "barangay"
            Dim lst = (From row In table.AsEnumerable
                       Select row.Field(Of String)(2)).Distinct.ToList
            barangay.DataSource = lst

        End If
        conclos()
    End Sub
    Private Sub selectpurok()
        'Try
        Dim adapter As New MySqlDataAdapter("SELECT * FROM purok WHERE barangay LIKE '%" & barangay.Text & "%'  ORDER BY purok ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        purok.DataSource = table
        purok.ValueMember = "purok"
        purok.DisplayMember = "purok"
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(1)).Distinct.ToList
        purok.DataSource = lst
        conclos()
    End Sub
    Private Sub selectbargy1()
        'Try
        Dim adapter As New MySqlDataAdapter("SELECT * FROM purok WHERE purok LIKE '%" & purok.Text & "%'  ORDER BY barangay ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        barangay.DataSource = table
        barangay.ValueMember = "barangay"
        barangay.DisplayMember = "barangay"
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(2)).Distinct.ToList
        barangay.DataSource = lst
        conclos()
    End Sub
    Private Sub selectpurok1()
        'Try
        Dim adapter As New MySqlDataAdapter("SELECT * FROM purok WHERE barangay = '" & barangay.Text & "'  ORDER BY purok ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        purok.DataSource = table
        purok.ValueMember = "purok"
        purok.DisplayMember = "purok"
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(1)).Distinct.ToList
        purok.DataSource = lst
        conclos()
    End Sub
    Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles purok.SelectedIndexChanged
        'selectbargy1()
        namepco()
        namesugg()
        fillist()
    End Sub
    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles barangay.SelectedIndexChanged
        selectpurok1()
    End Sub
    Private Sub fillist()
        Try
            conns()
            cmd = New MySqlCommand("SELECT * FROM full_data_entry where barangay = '" & barangay.Text & "'  AND pco = '" & hhl.Text & "' AND address = '" & purok.Text & "' ORDER BY id DESC", conn1)
            'cmd.Parameters.Add("@purok1", MySqlDbType.VarChar, 100).Value = purok.SelectedValue
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            'pagingDS2 = New DataSet()
            conn1.Open()
            'pagingAdapter.Fill(pagingDS2, "full_data_entry_table")
            pagingAdapter.Fill(pagingDS, scrollVal, 20, "full_data_entry_table")
            conn1.Close()
            DataGridView1.DataSource = pagingDS
            DataGridView1.DataMember = "full_data_entry_table"
            With DataGridView1
                '.Columns(0).HeaderCell.Value = "ID No."
                .Columns(2).HeaderCell.Value = "Name"
                .Columns(3).HeaderCell.Value = "Purok"
                .Columns(5).HeaderCell.Value = "Gender"
                .Columns(6).HeaderCell.Value = "Precinct number"
                .Columns(7).HeaderCell.Value = "Barangay"
                .Columns(8).HeaderCell.Value = "Province"
                .Columns(9).HeaderCell.Value = "Municipality"
                .Columns(10).HeaderCell.Value = "VoteForMJ?"
                .Columns(11).HeaderCell.Value = "HHL"
            End With
            DataGridView1.Columns.Item("id").Visible = False
            DataGridView1.Columns.Item("name").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("address").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("Gender").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("precinct_number").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("barangay").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("municipality").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("province").Visible = False
            DataGridView1.Columns.Item("number").Visible = False
            DataGridView1.Columns.Item("birthday").Visible = False
            DataGridView1.Columns.Item("statusv").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("pco").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.DefaultCellStyle.Font = New Font("arial", 12)
            'DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            'DataGridView1.Dock = DockStyle.Fill
            Dashboard.counters()

        Catch ex As Exception
            ' MsgBox(ex.Message)
        Finally

        End Try
        countdata()
        showing()
        conclos()
    End Sub
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Try
            Dim i As Integer
            With DataGridView1
                If e.RowIndex >= 0 Then
                    i = .CurrentRow.Index
                    Label8.Text = .Rows(i).Cells(0).Value.ToString
                    ComboBox1.Text = .Rows(i).Cells(2).Value.ToString
                    birth.Text = .Rows(i).Cells(4).Value.ToString
                    gender.Text = .Rows(i).Cells(5).Value.ToString
                    purok.Text = .Rows(i).Cells(3).Value.ToString
                    barangay.Text = .Rows(i).Cells(7).Value.ToString
                    votemj.Text = .Rows(i).Cells(10).Value.ToString
                    hhl.Text = .Rows(i).Cells(11).Value.ToString
                    precint.Text = .Rows(i).Cells(6).Value.ToString
                End If
            End With

        Catch ex As Exception
            ' MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        selectinfohhlbcc()
    End Sub
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        ''Try
        'conn.Open()
        'scrollVal = scrollVal - 20
        'If scrollVal <= 0 Then
        '    scrollVal = 0
        'End If
        'pagingDS.Clear()
        'pagingAdapter.Fill(pagingDS, scrollVal, 20, "full_data_entry_table")
        'conn.Close()
        'conn.Dispose()
        ''Catch ex As Exception
        ''    MsgBox(ex.Message)
        ''Finally
        ''    'conn.Close()
        ''End Try
        Try
            If conn1.State = ConnectionState.Open Then
                conn1.Close()
                conn1.Dispose()
            ElseIf connectionString.State = ConnectionState.Open Then
                connectionString.Close()
                connectionString.Dispose()
            Else
                conn1.Open()
                connectionString.Open()

                scrollVal = scrollVal - 20
                If scrollVal <= 0 Then
                    scrollVal = 0
                End If
                pagingDS.Clear()
                pagingAdapter.Fill(pagingDS, scrollVal, 20, "full_data_entry_table")
                If conn1.State = ConnectionState.Open Then
                    conn1.Close()
                    conn1.Dispose()
                ElseIf connectionString.State = ConnectionState.Open Then
                    connectionString.Close()
                    connectionString.Dispose()
                End If
            End If

        Catch ex As Exception
            ' MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        ''Try
        'conn.Open()
        'scrollVal = scrollVal + 20
        'If scrollVal <= 0 Then
        '    scrollVal = 0
        'End If
        'pagingDS.Clear()
        'pagingAdapter.Fill(pagingDS, scrollVal, 20, "full_data_entry_table")
        'conn.Close()
        'conn.Dispose()
        ''Catch ex As Exception
        ''    MsgBox(ex.Message)
        ''Finally
        ''    'conn.Close()
        ''End Try
        Try
            If conn1.State = ConnectionState.Open Then
                conn1.Close()
                conn1.Dispose()
            ElseIf connectionString.State = ConnectionState.Open Then
                connectionString.Close()
                connectionString.Dispose()
            Else
                conn1.Open()
                connectionString.Open()

                scrollVal = scrollVal + 20
                If scrollVal <= 0 Then
                    scrollVal = 0
                End If
                pagingDS.Clear()
                pagingAdapter.Fill(pagingDS, scrollVal, 20, "full_data_entry_table")
                If conn1.State = ConnectionState.Open Then
                    conn1.Close()
                    conn1.Dispose()
                ElseIf connectionString.State = ConnectionState.Open Then
                    connectionString.Close()
                    connectionString.Dispose()
                End If
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub fillistsearch()

        Dim table As New DataTable()
        'Try
        conns()
        Dim adapter As New MySqlDataAdapter("SELECT * FROM full_data_entry where barangay LIKE  '%" & barangay.Text & "%' AND address LIKE  '%" & purok.Text & "%' AND name LIKE  '%" & TextBox1.Text & "%' ORDER BY id DESC", conn1)
        adapter.Fill(table)

        DataGridView1.DataSource = table
        With DataGridView1
            .Columns(2).HeaderCell.Value = "Name"
            .Columns(3).HeaderCell.Value = "Purok"
            .Columns(5).HeaderCell.Value = "Gender"
            .Columns(6).HeaderCell.Value = "Precinct number"
            .Columns(7).HeaderCell.Value = "Barangay"
            .Columns(8).HeaderCell.Value = "Province"
            .Columns(9).HeaderCell.Value = "Municipality"
            .Columns(10).HeaderCell.Value = "VoteForMJ?"
        End With
        DataGridView1.Columns.Item("id").Visible = False
        DataGridView1.Columns.Item("name").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        DataGridView1.Columns.Item("address").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        DataGridView1.Columns.Item("Gender").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        DataGridView1.Columns.Item("precinct_number").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        DataGridView1.Columns.Item("barangay").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        DataGridView1.Columns.Item("municipality").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        DataGridView1.Columns.Item("province").Visible = False
        DataGridView1.Columns.Item("number").Visible = False
        DataGridView1.Columns.Item("birthday").Visible = False
        DataGridView1.Columns.Item("statusv").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        DataGridView1.Columns.Item("pco").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        DataGridView1.DefaultCellStyle.Font = New Font("arial", 12)
        countdata()

        'Catch ex As Exception
        '    'MsgBox(ex.Message)
        'Finally

        'End Try
        conclos()
    End Sub

    Private Sub clear()
        scrollVal = 0
        ComboBox1.Text = ""
        gender.Text = ""
        TextBox1.Text = ""
        votemj.Text = ""
        Label8.Text = ""
        TextBox1.Text = ""
        conclos()
        'MySqlConnection.ClearAllPools()

    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            If ComboBox1.Text = "" Or purok.Text = "" Or barangay.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM hhl where names = '" & ComboBox1.Text & "'   AND precint = '" & precint.Text & "'", conn1)
                dr = cmd.ExecuteReader
                'Dim sdr As MySqlDataReader
                'sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                Dim bgy
                Dim prk
                If (dr.Read) Then
                    bgy = dr("barangay")
                    prk = dr("purok")
                    If MsgBox("'" & ComboBox1.Text & "' ALREADY IN HHL of '" & bgy & "' & purok '" & prk & "'  ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then

                    End If
                    ' MsgBox("ALREADY IN HHL '" & ComboBox1.Text & "' ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok)
                Else
                    conns()
                    cmd = New MySqlCommand("SELECT * FROM bcc where names = '" & ComboBox1.Text & "'  AND precint = '" & precint.Text & "'", conn1)
                    dr = cmd.ExecuteReader
                    Dim bgy1
                    Dim prk1
                    If (dr.Read) Then
                        bgy1 = dr("barangay")
                        prk1 = dr("purok")
                        If MsgBox("'" & ComboBox1.Text & "' ALREADY IN BCC of '" & bgy1 & "' & purok '" & prk1 & "'  ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then

                        End If
                        ' MessageBox.Show("ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        conns()
                        cmd = New MySqlCommand("SELECT * FROM pco where names = '" & ComboBox1.Text & "'  AND precint = '" & precint.Text & "'", conn1)
                        dr = cmd.ExecuteReader
                        Dim bgy2
                        Dim prk2
                        If (dr.Read) Then
                            bgy2 = dr("barangay")
                            prk2 = dr("purok")
                            If MsgBox("'" & ComboBox1.Text & "' ALREADY IN PCC of '" & bgy2 & "' & purok '" & prk2 & "'  ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then

                            End If
                            ' MessageBox.Show("ALREADY IN PCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Else
                            conns()
                            cmd = New MySqlCommand("SELECT * FROM full_data_entry where name = '" & ComboBox1.Text & "'  AND birthday = '" & birth.Text & "' AND precinct_number = '" & precint.Text & "'", conn1)
                            dr = cmd.ExecuteReader
                            Dim bgy3
                            Dim prk3
                            Dim hhl3
                            If (dr.Read) Then
                                bgy3 = dr("barangay")
                                prk3 = dr("address")
                                hhl3 = dr("pco")
                                If MsgBox("'" & ComboBox1.Text & "' ALREADY IN RECRUITS of '" & bgy3 & "' & purok '" & prk3 & "' under HHL '" & hhl3 & "' ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then

                                End If
                            Else
                                conns()
                                cmd = New MySqlCommand("SELECT * FROM full_data  where d_ LIKE 'D' AND name = '" & ComboBox1.Text & "'  AND birthday = '" & birth.Text & "' AND precinct_number = '" & precint.Text & "'", conn1)
                                dr = cmd.ExecuteReader
                                Dim bgy4
                                Dim prk4
                                Dim prcnt3
                                If (dr.Read) Then
                                    bgy4 = dr("barangay")
                                    prk4 = dr("address")
                                    prcnt3 = dr("precinct_number")
                                    If MsgBox("'" & ComboBox1.Text & "' ALREADY Decease in Barangay '" & bgy4 & "' , Purok '" & prk4 & "' & Precint '" & prcnt3 & "' ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Decease Person") = MsgBoxResult.Ok Then

                                    End If
                                Else
                                    conns()
                                    cmd = New MySqlCommand("SELECT * FROM full_data where name = '" & ComboBox1.Text & "' AND birthday = '" & birth.Text & "' AND precinct_number = '" & precint.Text & "'", conn1)
                                    dr = cmd.ExecuteReader
                                    If (dr.Read) Then
                                        Call conns()
                                        cmd = New MySqlCommand("Insert into full_data_entry (name,birthday,Gender,address,barangay,statusv,municipality,province,pco,precinct_number,cardno) Values" _
                                    & " ('" & ComboBox1.Text & "', '" & birth.Text & "','" & gender.Text & "','" & purok.Text & "','" & barangay.Text & "','" & votemj.Text _
                                    & "','" & "Davao Oriental" & "','" & "Caraga" & "','" & hhl.Text & "','" & precint.Text & "','" & ComboBox3.Text & "')", conn1)
                                        cmd.ExecuteNonQuery()
                                        MsgBox("Successfully Saved", MsgBoxStyle.Information, "Save")
                                        fillist()
                                        clear()
                                    Else
                                        MessageBox.Show("Member Not in Masterlist please retype the name or search from masterlist", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                                        ComboBox1.Focus()
                                    End If

                                End If
                            End If
                        End If
                    End If
                End If
            End If
            'MySqlConnection.ClearAllPools()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        If Dashboard.Label17.Text = "lhoy" Or Dashboard.Label17.Text = "EL" Then
            sqlbackuperclik()
        Else

        End If
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Try
            If ComboBox1.Text = "" Or purok.Text = "" Or barangay.Text = "" Or Label8.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                If MsgBox("Are you sure do you want to delete?", CType(MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Yes Then
                    Call conns()
                    cmd = New MySqlCommand("Delete from full_data_entry where id = '" & Label8.Text & "'", conn1)
                    cmd.ExecuteNonQuery()
                    MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
                    fillist()
                    clear()
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        'If (e.ColumnIndex = 2 AndAlso e.Value IsNot Nothing) Then
        '    'e.Value = New String("*", e.Value.ToString().Length)
        '    e.Value = Decryptel(e.Value, 1)
        'End If
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Try
            If ComboBox1.Text = "" Or purok.Text = "" Or barangay.Text = "" Or Label8.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM hhl where names = '" & ComboBox1.Text & "'  AND precint = '" & precint.Text & "'", conn1)
                dr = cmd.ExecuteReader
                'Dim sdr As MySqlDataReader
                'sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                Dim bgy
                Dim prk
                If (dr.Read) Then
                    bgy = dr("barangay")
                    prk = dr("purok")
                    If MsgBox("'" & ComboBox1.Text & "' ALREADY IN HHL of '" & bgy & "' & purok '" & prk & "'  ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then

                    End If
                    ' MsgBox("ALREADY IN HHL '" & ComboBox1.Text & "' ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok)
                Else
                    conns()
                    cmd = New MySqlCommand("SELECT * FROM bcc where names = '" & ComboBox1.Text & "' AND precint = '" & precint.Text & "'", conn1)
                    dr = cmd.ExecuteReader
                    Dim bgy1
                    Dim prk1
                    If (dr.Read) Then
                        bgy1 = dr("barangay")
                        prk1 = dr("purok")
                        If MsgBox("'" & ComboBox1.Text & "' ALREADY IN BCC of '" & bgy1 & "' & purok '" & prk1 & "'  ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then

                        End If
                        ' MessageBox.Show("ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        conns()
                        cmd = New MySqlCommand("SELECT * FROM full_data  where d_ LIKE 'D' AND name = '" & ComboBox1.Text & "'   AND birthday = '" & birth.Text & "' AND precinct_number = '" & precint.Text & "'", conn1)
                        dr = cmd.ExecuteReader
                        Dim bgy4
                        Dim prk4
                        Dim prcnt3
                        If (dr.Read) Then
                            bgy4 = dr("barangay")
                            prk4 = dr("address")
                            prcnt3 = dr("precinct_number")
                            If MsgBox("'" & ComboBox1.Text & "' ALREADY Decease in Barangay '" & bgy4 & "' , Purok '" & prk4 & "' & Precint '" & prcnt3 & "' ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Decease Person") = MsgBoxResult.Ok Then

                            End If
                        Else
                            conns()
                            cmd = New MySqlCommand("SELECT * FROM pco where names = '" & ComboBox1.Text & "'   AND precint = '" & precint.Text & "'", conn1)
                            dr = cmd.ExecuteReader
                            Dim bgy2
                            Dim prk2
                            If (dr.Read) Then
                                bgy2 = dr("barangay")
                                prk2 = dr("purok")
                                If MsgBox("'" & ComboBox1.Text & "' ALREADY IN PCC of '" & bgy2 & "' & purok '" & prk2 & "'  ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then

                                End If
                                ' MessageBox.Show("ALREADY IN PCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                            Else
                                'conns()
                                'cmd = New MySqlCommand("SELECT * FROM full_data_entry where name LIKE  '%" & ComboBox1.Text & "%'", conn1)
                                'dr = cmd.ExecuteReader
                                'Dim bgy3
                                'Dim prk3
                                'Dim hhl3
                                'If (dr.Read) Then
                                '    bgy3 = dr("barangay")
                                '    prk3 = dr("address")
                                '    hhl3 = dr("pco")
                                '    If MsgBox("'" & ComboBox1.Text & "' ALREADY IN RECRUITS of '" & bgy3 & "' & purok '" & prk3 & "' under HHL '" & hhl3 & "' ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then

                                '    End If
                                'Else
                                conns()
                                cmd = New MySqlCommand("SELECT * FROM full_data where name = '" & ComboBox1.Text & "'  AND birthday = '" & birth.Text & "' AND precinct_number = '" & precint.Text & "'", conn1)
                                dr = cmd.ExecuteReader
                                If (dr.Read) Then
                                    Call conns()
                                    cmd = New MySqlCommand("Update full_data_entry set name = '" & ComboBox1.Text & "', birthday ='" & birth.Text _
                                                           & "', Gender = '" & gender.Text & "', address = '" & purok.Text & "', barangay = '" & barangay.Text _
                                                           & "', statusv = '" & votemj.Text & "', pco = '" & hhl.Text & "', precinct_number = '" & precint.Text & "' , cardno = '" & ComboBox3.Text & "' where id = '" & Label8.Text & "'", conn1)
                                    cmd.ExecuteNonQuery()
                                    MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
                                    fillist()
                                    clear()
                                Else
                                    MessageBox.Show("Member Not in Masterlist please retype the name or search from masterlist", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                                    ComboBox1.Focus()
                                End If

                            End If
                        End If
                    End If
                End If
            End If
            'End If 
            'MySqlConnection.ClearAllPools()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        'fillist()
        'clear()
        conclos()
        Me.Controls.Clear() 'removes all the controls on the form
        InitializeComponent() 'load all the controls again
        DataEntry_Load(e, e)
        'MsgBox("Thank you for sending report", vbInformation, "")
        Refresh()
    End Sub
    Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker1.ValueChanged
        birth.Text = DateTimePicker1.Text
    End Sub
    'Private Sub countdataXX()
    '    Dim pagingAdapter3 As MySqlDataAdapter
    '    Dim pagingDS3 As DataSet
    '    Try
    '        Call conns()
    '        cmd = New MySqlCommand("SELECT * FROM full_data_entry ORDER BY id ASC", conn1)
    '        cmd.ExecuteNonQuery()
    '       conn1.Close()
    '        pagingAdapter3 = New MySqlDataAdapter(cmd)
    '        pagingDS3 = New DataSet()
    '       conn1.Open()
    '        pagingAdapter.Fill(pagingDS3, "full_data_entry_table")
    '       conn1.Close()
    '        sRecord_Count = pagingDS3.Tables(0).Rows.Count
    '        Dim table As New DataTable() 
    '        pagingAdapter.Fill(table)
    '        Dim lst = (From row In table.AsEnumerable
    '                   Select row.Field(Of String)(2)).Distinct.Count
    '        Label10.Text = lst
    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '    Finally
    '       conn1.Close()
    '       conn1.Dispose()
    '    End Try
    'End Sub
    Sub countdata()
        Try
            Dim table As New DataTable()
            conns()
            Dim adapter As New MySqlDataAdapter("SELECT name FROM full_data_entry ORDER BY id ASC", conn1)
            adapter.Fill(table)
            pagingDS2 = New DataSet()
            adapter.Fill(pagingDS2, "full_data_entry_table")
            sRecord_Count = pagingDS2.Tables(0).Rows.Count
            'Dim table As New DataTable()
            'adapter.Fill(table)
            Dim lst = (From row In table.AsEnumerable
                       Select row.Field(Of String)(0)).Distinct.Count
            Label10.Text = lst
            'MySqlConnection.ClearAllPools()
        Catch ex As Exception
            ' MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub showing()
        Try
            Dim table As New DataTable()
            conns()
            Dim adapter As New MySqlDataAdapter("SELECT name FROM full_data_entry where barangay LIKE  '%" & barangay.Text & "%' AND address LIKE  '%" & purok.Text & "%' AND pco LIKE  '%" & hhl.Text & "%'", conn1)
            adapter.Fill(table)
            pagingDS2 = New DataSet()
            adapter.Fill(pagingDS2, "full_data_entry_table")
            sRecord_Count = pagingDS2.Tables(0).Rows.Count
            'Dim table As New DataTable()
            'adapter.Fill(table)
            Dim lst = (From row In table.AsEnumerable
                       Select row.Field(Of String)(0)).Distinct.Count
            Label18.Text = lst
            'MySqlConnection.ClearAllPools()
        Catch ex As Exception
            ' MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        PrintData.Show()
    End Sub
    Private Sub pco_SelectedIndexChanged(sender As Object, e As EventArgs) Handles hhl.TextChanged
        fillist()
    End Sub
    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        If TextBox1.Text = "" Then
            fillist()
        Else
            fillistsearch()
        End If
    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles ComboBox1.KeyPress
        Try
            e.KeyChar = UCase(e.KeyChar)
        Catch ex As Exception

        End Try

    End Sub
    Private Sub ComboBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles ComboBox1.KeyDown
        If e.KeyCode = Keys.Enter Then
            Button1.PerformClick()
            'ElseIf e.KeyCode = Keys.Tab Then
            '    Button3.PerformClick()
            'ElseIf e.KeyCode = Keys.F2 Then
            '    Button7.PerformClick()
            'ElseIf e.KeyCode = Keys.F1 Then
            '    Button8.PerformClick()
            'ElseIf e.KeyCode = Keys.Down Then
            '    ListView1.Focus()
        Else
        End If
    End Sub
    Private Sub hhl_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles hhl.KeyDown
        If e.KeyCode = Keys.Enter Then
            Button1.PerformClick()
        Else
        End If
    End Sub
    Private Sub votemj_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles votemj.KeyDown
        If e.KeyCode = Keys.Enter Then
            Button1.PerformClick()
        Else
        End If
    End Sub
    Private Sub hhl_SelectedIndexChanged(sender As Object, e As EventArgs) Handles hhl.SelectedIndexChanged
        selectinfohhlpcc()
    End Sub
    Private Sub ComboBox2_SelectedIndexChanged_1(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
        If ComboBox2.Text = "HHL Member" Then
            PrintData.Show()
            ComboBox2.Text = ""
        ElseIf ComboBox2.Text = "HHL" Then
            printdatabyhhlinpurok.Show()
            ComboBox2.Text = ""
        Else

        End If
    End Sub

    Private Sub Label16_Click(sender As Object, e As EventArgs) Handles Label16.Click

    End Sub

End Class