Imports MySql.Data.MySqlClient
Public Class hhldata
    Public sCon As MySqlConnection = New MySqlConnection
    Public sComand As MySqlDataAdapter = New MySqlDataAdapter
    Public ds As New DataSet
    Dim scrollVal As New Integer
    Dim pagingAdapter As New MySqlDataAdapter
    Dim pagingAdapter2 As New MySqlDataAdapter
    Dim pagingDS As New DataSet
    Dim pagingDS2 As New DataSet
    Dim pagingDS3 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
    '          MessageBox.Show("Please Close to finish update 5 seconds & Open the program again. Or contact the programmer", "hhl no connection",
    'MessageBoxButtons.OK, MessageBoxIcon.Error)
    '          End
    '      End Try
    '  End Sub
    '  Private Sub conclos()
    '      Try
    '          connectionString.Close()
    '          connectionString.Dispose()
    '          conn1.Close()
    '          conn1.Dispose()
    '          MySqlConnection.ClearPool(conn)
    '          MySqlConnection.ClearPool(connectionString)
    '      Catch ex As Exception

    '      End Try

    '  End Sub
    Private Sub hhldata_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        conclos()
    End Sub
    Private Sub hhldata_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        conns()
        selectbargy()
        'selectpurok()
        'namesugg()
        countpco()
        purokid.Visible = False
        precint.Enabled = False
        conn1.Close()
        conn1.Dispose()
        connectionString.Close()
        connectionString.Dispose()
        MySqlConnection.ClearAllPools()
    End Sub
    Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles purok.SelectedIndexChanged
        'selectbargy1()
        namesugg()
        fillist()
        namesuggbcc()
    End Sub
    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles barangay.SelectedIndexChanged
        selectpurok()
        bcc.Text = ""
    End Sub
    Private Sub name_SelectedIndexChanged(sender As Object, e As EventArgs) Handles names.SelectedIndexChanged
        conclos()
        selectinfo()
    End Sub
    Private Sub names_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles names.KeyDown
        If e.KeyCode = Keys.Enter Then
            'Button1.PerformClick()
        Else
        End If
    End Sub
    Private Sub bcc_SelectedIndexChanged(sender As Object, e As EventArgs) Handles bcc.SelectedIndexChanged
        conclos()
        selectinfobcc()
    End Sub
    Private Sub selectpurok1()
        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

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub selectbargy()
        If Dashboard.Label17.Text = "jeleen" Then
            barangay.Items.Clear()
            barangay.DropDownStyle = ComboBoxStyle.DropDownList
            barangay.Items.Add("POBLACION")
            barangay.SelectedItem = "POBLACION"
        Else
            Try
                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
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally

            End Try
        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
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub selectinfo()
        Try
            Call conns()
            Dim sqlcmd As New MySqlCommand("SELECT * FROM full_data where name LIKE '%" & names.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
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub selectinfobcc()
        Try
            Call conns()
            Dim sqlcmd As New MySqlCommand("SELECT * FROM full_data where name LIKE '%" & bcc.Text & "%'", conn1)
            Dim sdr As MySqlDataReader
            sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
            While sdr.Read()
                'birth.Text = sdr("birthday")
                'gender.Text = sdr("Gender")
                Label10.Text = sdr("precinct_number")
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub namesugg()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT * FROM full_data where barangay LIKE  '%" & barangay.Text & "%' AND address LIKE  '%" & purok.Text _
                                                & "%'  ORDER BY name ASC", connectionString)
            Dim table As New DataTable()
            adapter.Fill(table)
            With names
                .DataSource = table
                .DisplayMember = "name"
                .ValueMember = "name"
                .DropDownStyle = ComboBoxStyle.DropDown
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.ListItems
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub namesuggbcc()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT * FROM pco where barangay LIKE  '%" & barangay.Text & "%' ORDER  BY id DESC", connectionString)
            Dim table As New DataTable()
            adapter.Fill(table)
            With bcc
                .DataSource = table
                .DisplayMember = "names"
                .ValueMember = "names"
                .DropDownStyle = ComboBoxStyle.DropDownList
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.ListItems
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub clear()
        fillist()
        scrollVal = 0
        'name.Text = ""
        'purok.Text = ""
        'barangay.Text = ""
        Label3.Text = ""
        purokid.Text = ""
        conclos()
    End Sub
    Private Sub fillist()
        Try
            conns()
            cmd = New MySqlCommand("SELECT * FROM hhl where barangay LIKE  '%" & barangay.Text & "%' AND purok =@purok1  ORDER BY id DESC", conn1)
            cmd.Parameters.Add("@purok1", MySqlDbType.VarChar, 100).Value = purok.SelectedValue
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter2 = New MySqlDataAdapter(cmd)
            pagingDS3 = New DataSet()
            'pagingDS2 = New DataSet()
            conn1.Open()
            'pagingAdapter.Fill(pagingDS2, "hhl_table")
            pagingAdapter2.Fill(pagingDS3, scrollVal, 20, "hhl_table")
            conn1.Close()
            DataGridView1.DataSource = pagingDS3
            DataGridView1.DataMember = "hhl_table"
            With DataGridView1
                .Columns(1).HeaderCell.Value = "Name"
                .Columns(2).HeaderCell.Value = "Purok"
                .Columns(3).HeaderCell.Value = "Barangay"
                .Columns(4).HeaderCell.Value = "PCC"
                .Columns(7).HeaderCell.Value = "Status"
            End With
            DataGridView1.Columns.Item("id").Visible = False
            DataGridView1.Columns.Item("names").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("purok").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("barangay").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("pcc").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("boo").Visible = False
            DataGridView1.Columns.Item("contact").Visible = False
            DataGridView1.Columns.Item("status").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
        conclos()
        countpco()
    End Sub
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        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
                pagingDS3.Clear()
                pagingAdapter2.Fill(pagingDS3, scrollVal, 20, "hhl_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
            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
                pagingDS3.Clear()
                pagingAdapter2.Fill(pagingDS3, scrollVal, 20, "hhl_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 DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Try
            Dim i As Integer
            With DataGridView1
                If e.RowIndex >= 0 Then
                    i = .CurrentRow.Index
                    purokid.Text = .Rows(i).Cells(0).Value.ToString
                    names.Text = .Rows(i).Cells(1).Value.ToString
                    Label1.Text = .Rows(i).Cells(1).Value.ToString
                    purok.Text = .Rows(i).Cells(2).Value.ToString
                    barangay.Text = .Rows(i).Cells(3).Value.ToString
                    bcc.Text = .Rows(i).Cells(4).Value.ToString
                    contact.Text = .Rows(i).Cells(6).Value.ToString
                    precint.Text = .Rows(i).Cells(8).Value.ToString
                    votemj.Text = .Rows(i).Cells(7).Value.ToString
                End If
            End With
            ' name.Text = Decryptel(name.Text, 1)
        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        conclos()
        Try
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or bcc.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM hhl where names LIKE  '%" & names.Text & "%'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    MessageBox.Show("NAME ALREADY IN HHL", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    conns()
                    cmd = New MySqlCommand("SELECT * FROM pco where names LIKE  '%" & names.Text & "%'", conn1)
                    dr = cmd.ExecuteReader
                    If (dr.Read) Then
                        MessageBox.Show("NAME ALREADY IN PCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        conns()
                        cmd = New MySqlCommand("SELECT * FROM full_data_entry where name LIKE  '%" & names.Text & "%'", conn1)
                        dr = cmd.ExecuteReader
                        If (dr.Read) Then
                            MessageBox.Show("NAME ALREADY IN Data Entry", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Else
                            conns()
                            cmd = New MySqlCommand("SELECT * FROM bcc where names LIKE  '%" & names.Text & "%'", conn1)
                            dr = cmd.ExecuteReader
                            If (dr.Read) Then
                                MessageBox.Show("NAME ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                            Else
                                conns()
                                cmd = New MySqlCommand("SELECT * FROM full_data where name LIKE  '%" & names.Text & "%'", conn1)
                                dr = cmd.ExecuteReader
                                If (dr.Read) Then
                                    savehhl()
                                Else
                                    MessageBox.Show("Name Not in Master List", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                                End If
                            End If
                        End If
                    End If
                End If
            End If
            clear()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

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

        End If
        conclos()
    End Sub
    Private Sub savehhl()
        Try
            conns()
            cmd = New MySqlCommand("SELECT * FROM pco where names LIKE  '%" & bcc.Text & "%'", conn1)
            dr = cmd.ExecuteReader
            If (dr.Read) Then
                Call conns()
                cmd = New MySqlCommand("Insert into hhl (names,purok,barangay,pcc,contact,precint,status,cardno) Values" _
            & " ('" & names.Text & "', '" & purok.Text & "','" & barangay.Text _
            & "', '" & bcc.Text & "', '" & contact.Text & "', '" & precint.Text & "', '" & votemj.Text & "', '" & cardno.Text & "')", conn1)
                cmd.ExecuteNonQuery()
                conn1.Close()
                MsgBox("Successfully Saved", MsgBoxStyle.Information, "Save")
            Else
                MessageBox.Show("NAME IS NOT PCC, PLEASE SELECT PCC NAME", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
            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 = 1 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
        conclos()
        Try
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or bcc.Text = "" Or purokid.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM pco where names LIKE '%" & names.Text & "%'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    MessageBox.Show("NAME ALREADY IN PCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    conns()
                    cmd = New MySqlCommand("SELECT * FROM full_data_entry where name LIKE '%" & names.Text & "%'", conn1)
                    dr = cmd.ExecuteReader
                    If (dr.Read) Then
                        MessageBox.Show("NAME ALREADY IN Data Entry", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        conns()
                        cmd = New MySqlCommand("SELECT * FROM bcc where names LIKE '%" & names.Text & "%'", conn1)
                        dr = cmd.ExecuteReader
                        If (dr.Read) Then
                            MessageBox.Show("NAME ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Else
                            conns()
                            cmd = New MySqlCommand("SELECT * FROM full_data where name LIKE '%" & names.Text & "%'", conn1)
                            dr = cmd.ExecuteReader
                            If (dr.Read) Then
                                updateentry2()
                                Call conns()

                                Dim command As New MySqlCommand("UPDATE `hhl` SET `names`=@d1,`purok`=@d2,`barangay`=@d3,`pcc`=@d4,`contact`=@d5,`precint`=@d6,`status`=@d8,`cardno`=@d9 WHERE `id` =@d7", conn1)
                                command.Parameters.Add("@d7", MySqlDbType.Int64).Value = purokid.Text
                                command.Parameters.Add("@d1", MySqlDbType.Text).Value = names.Text
                                command.Parameters.Add("@d2", MySqlDbType.VarChar).Value = purok.Text
                                command.Parameters.Add("@d3", MySqlDbType.VarChar).Value = barangay.Text
                                command.Parameters.Add("@d4", MySqlDbType.VarChar).Value = bcc.Text
                                command.Parameters.Add("@d5", MySqlDbType.VarChar).Value = contact.Text
                                command.Parameters.Add("@d6", MySqlDbType.VarChar).Value = precint.Text
                                command.Parameters.Add("@d8", MySqlDbType.VarChar).Value = votemj.Text
                                command.Parameters.Add("@d9", MySqlDbType.VarChar).Value = cardno.Text
                                ' command.Parameters.Add("@adds", MySqlDbType.VarChar).Value = TextBox3.Text
                                ' command.Parameters.Add("@brd", MySqlDbType.Date).Value = DateTimePicker1.Value

                                If command.ExecuteNonQuery() = 1 Then
                                    MessageBox.Show("Successfully Updated")
                                Else
                                    MessageBox.Show("ERROR Update")
                                End If

                                'cmd = New MySqlCommand("Update hhl set names = '" & names.Text & "', purok ='" & purok.Text & "', barangay ='" & barangay.Text & "', pcc ='" & bcc.Text & "', contact ='" & contact.Text & "', precint ='" & precint.Text & "' where id = '" & purokid.Text & "'", conn1)
                                '    cmd.ExecuteNonQuery()
                                '    MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
                                clear()
                            Else
                                If MsgBox("'" & names.Text & "' Not found in Masterlist, please retype the name or search from masterlist", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then

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

        End Try
        conclos()
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Try
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or purokid.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
                    updateentry()
                    Call conns()
                    cmd = New MySqlCommand("Delete from hhl where id = '" & purokid.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 updateentry()
        Try
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or purokid.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM full_data_entry where pco LIKE  '%" & names.Text & "%' and barangay LIKE '%" & barangay.Text & "%'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    If MsgBox("Member with assigned HHL will update to blank HHL", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then
                        ' MessageBox.Show("NAME ALREADY BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Call conns()
                        cmd = New MySqlCommand("Update full_data_entry set pco = '' where pco LIKE  '" & names.Text & "' and barangay LIKE '%" & barangay.Text & "%'", conn1)
                        cmd.ExecuteNonQuery()
                        'MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
                        'clear()
                    End If

                Else

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

        End Try
        conclos()
    End Sub
    Private Sub updateentry2()
        Try
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or purokid.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM full_data_entry where pco LIKE  '%" & names.Text & "%' and barangay LIKE '%" & barangay.Text & "%'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then


                Else
                    If MsgBox("Member with assigned HHL will update to '" & names.Text & "' ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then
                        ' MessageBox.Show("NAME ALREADY BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Call conns()
                        cmd = New MySqlCommand("Update full_data_entry set pco = '" & names.Text & "' where pco LIKE  '" & Label1.Text & "' and barangay LIKE '%" & barangay.Text & "%'", conn1)
                        cmd.ExecuteNonQuery()
                        'MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
                        'clear()
                    End If
                End If
            End If
        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, Button7.Click
        Me.Controls.Clear() 'removes all the controls on the form
        InitializeComponent() 'load all the controls again
        hhldata_Load(e, e)
        'MsgBox("Thank you for sending report", vbInformation, "")
        Refresh()
        'clear()
    End Sub
    Private Sub countpco()
        Try
            Call conns()
            cmd = New MySqlCommand("SELECT * FROM hhl ORDER BY id ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS2 = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS2, "hhl_table")
            conn1.Close()
            sRecord_Count = pagingDS2.Tables(0).Rows.Count
            Dim table As New DataTable()
            pagingAdapter.Fill(table)
            Dim lst = (From row In table.AsEnumerable
                       Select row.Field(Of String)(1)).Distinct.Count
            Label5.Text = lst
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub names_SelectedIndexChanged(sender As Object, e As KeyPressEventArgs) Handles names.KeyPress
        e.KeyChar = UCase(e.KeyChar)
    End Sub
 
End Class