Public Class frmCandidates

    Public Sub displaymember()
        ejbselect("Select candidateID as [Candidate ID], studentID as [Student ID], cand_name as [Candidate Name], candidacy as [Candidacy], course as [Course], gov_id as [Year Level], cand_score, precentoNO as [Precenct], sphoto, priority, a_y From tblCandidate")
        fillcandidate(DataGridView1)

    End Sub

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

        Try

            Dim cn As New OleDb.OleDbConnection
            Dim da As New OleDb.OleDbDataAdapter


            cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" &
                                   Application.StartupPath & "\dbEvote.mdb"

            cn.Open()

            Dim arrImage() As Byte
            Dim strImage As String
            Dim myMs As New IO.MemoryStream
            '
            If Not IsNothing(PictureBox1.Image) Then
                PictureBox1.Image.Save(myMs, PictureBox1.Image.RawFormat)
                arrImage = myMs.GetBuffer
                strImage = "?"
            Else
                arrImage = Nothing
                strImage = "NULL"
            End If

            Dim cmd As New OleDb.OleDbCommand

            cmd.Connection = cn

            Dim sql As String
            Dim table As New DataTable


            sql = "select * from tblCandidate where studentId ='" & txtstudentID.Text & "'"

            cmd.CommandText = sql

            da.SelectCommand = cmd
            da.Fill(table)

            If table.Rows.Count > 0 Then

                MsgBox("Duplicate Candidate ID")


            Else
                Dim precentoNo As String

                precentoNo = " "

                If cbyrlevel.Text = 1 Then
                    precentoNo = "LAB1YEAR1"
                ElseIf cbyrlevel.Text = 2 Then
                    precentoNo = "LAB2YEAR2"
                ElseIf cbyrlevel.Text = 3 Then
                    precentoNo = "LAB3YEAR3"
                ElseIf cbyrlevel.Text = 4 Then
                    precentoNo = "LAB4YEAR4"
                End If


                cmd.CommandText = "INSERT INTO tblCandidate (studentID, cand_name, candidacy, course, gov_id, cand_score, precentoNo, sphoto, priority, a_y) VALUES('" & _
                                                       txtstudentID.Text & "','" & txtname.Text & "','" & cbcandidacy.Text & "','" & cbcourse.Text & "','" & cbyrlevel.Text & "'," & 0 & ",'" & precentoNo & "'," & strImage & "," & lblpriority.Text & ",'" & cbay.Text & "')"



                If strImage = "?" Then
                    cmd.Parameters.Add(strImage, OleDb.OleDbType.Binary).Value = arrImage
                End If
                '

                MsgBox("Data save successfully!")

                cmd.ExecuteNonQuery()


                cn.Close()

            End If

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try

        displaymember()
        Button3_Click(sender, e)
        cbcourse.Text = Nothing
        PictureBox1.Image = Nothing

    End Sub


    Private Sub Label6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label6.Click
        displaymember()

    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick

        lblid.Text = DataGridView1.CurrentRow.Cells(0).Value.ToString
        txtstudentID.Text = DataGridView1.CurrentRow.Cells(1).Value.ToString
        txtname.Text = DataGridView1.CurrentRow.Cells(2).Value.ToString
        cbcandidacy.Text = DataGridView1.CurrentRow.Cells(3).Value.ToString
        cbcourse.Text = DataGridView1.CurrentRow.Cells(4).Value.ToString
        cbyrlevel.Text = DataGridView1.CurrentRow.Cells(5).Value.ToString
        lblpriority.Text = DataGridView1.CurrentRow.Cells(9).Value.ToString
        cbay.Text = DataGridView1.CurrentRow.Cells(10).Value.ToString

        Button5_Click(sender, e)

    End Sub

    Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
        Dim cn As New OleDb.OleDbConnection
        Dim da As New OleDb.OleDbDataAdapter

        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" &
                                  Application.StartupPath & "\dbEvote.mdb"

        cn.Open()

        'string image update
        Dim arrImage() As Byte
        Dim strImage As String
        Dim myMs As New IO.MemoryStream
        '
        If Not IsNothing(PictureBox1.Image) Then
            PictureBox1.Image.Save(myMs, PictureBox1.Image.RawFormat)
            arrImage = myMs.GetBuffer
            strImage = "?"
        Else
            arrImage = Nothing
            strImage = "NULL"
        End If

        Dim cmd As New OleDb.OleDbCommand
        cmd.Connection = cn


        cmd.CommandText = "update tblCandidate set sphoto =" & strImage & " where candidateID = " & lblid.Text & ""

        ejbupdate("UPDATE tblCandidate set studentID ='" & txtstudentID.Text & "' , cand_name = '" & txtname.Text & "', candidacy ='" & cbcandidacy.Text & "', course ='" & cbcourse.Text & "', gov_id =" & cbyrlevel.Text & ", priority =" & lblpriority.Text & ", a_y ='" & cbay.Text & "' where candidateId = " & lblid.Text & "")


        If strImage = "?" Then
            cmd.Parameters.Add(strImage, OleDb.OleDbType.Binary).Value = arrImage
        End If

        cmd.ExecuteNonQuery()
        cn.Close()

        PictureBox1.Image = Nothing
        displaymember()
        Button3_Click(sender, e)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        clearall(GroupBox1, DataGridView1)
        cbcandidacy.Text = Nothing
        cbcourse.Text = Nothing
        PictureBox1.Image = Nothing

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()

    End Sub

    Private Sub btndel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndel.Click
        ejbdelete("Delete * from tblCandidate where candidateID= " & lblid.Text & "")
        displaymember()
        Button3_Click(sender, e)
        MsgBox("Succesfully Deleted")
    End Sub


   
  

    
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If OpenFileDialog1.ShowDialog = 1 Then
            PictureBox1.Image = System.Drawing.Image.FromFile(OpenFileDialog1.FileName)
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Dim cn As New OleDb.OleDbConnection
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; " &
                            " Data Source=" & Application.StartupPath & "\dbEvote.mdb"
        cn.Open()

        Dim arrImage() As Byte
        Dim myMS As New IO.MemoryStream
        Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM tblCandidate " & _
                                            " WHERE candidateID=" & lblid.Text, cn)
        Dim dt As New DataTable
        da.Fill(dt)

        If dt.Rows.Count > 0 Then
            'Me.txtName.Text = dt.Rows(0).Item("name") & ""
            If Not IsDBNull(dt.Rows(0).Item("sphoto")) Then
                arrImage = dt.Rows(0).Item("sphoto")
                For Each ar As Byte In arrImage
                    myMS.WriteByte(ar)
                Next
                '
                PictureBox1.Image = System.Drawing.Image.FromStream(myMS)
            End If
            'Me.btnAdd.Enabled = False
        Else
            MsgBox("Record not found!")
        End If

        cn.Close()


    End Sub

    Private Sub cbcandidacy_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbcandidacy.SelectedIndexChanged

        If cbcandidacy.Text = "President" Then
            lblpriority.Text = 1
        ElseIf cbcandidacy.Text = "Vice-President" Then
            lblpriority.Text = 2
        ElseIf cbcandidacy.Text = "Secretary" Then
            lblpriority.Text = 3
        ElseIf cbcandidacy.Text = "Treasurer" Then
            lblpriority.Text = 4
        ElseIf cbcandidacy.Text = "Auditor" Then
            lblpriority.Text = 5
        ElseIf cbcandidacy.Text = "PIO" Then
            lblpriority.Text = 6
        ElseIf cbcandidacy.Text = "Senator" Then
            lblpriority.Text = 7
        ElseIf cbcandidacy.Text = "Governor" Then
            lblpriority.Text = 8
        ElseIf cbcandidacy.Text = "Vice-Governor" Then
            lblpriority.Text = 9

        End If

     

    End Sub
End Class