Public Class frmVoters

    Public Sub displaymember()
        ejbselect("Select votersID as [Voters ID], precentoNo as [Precent], studentID as [Student ID], studentName as [Student Name], yearLevel as [Year Level], course as [Course] , voteMonitor as [Vote Monitor], email as [Email Address], contact, sphoto From tblVoters")
        fillVoters(DataGridView1)

    End Sub

    Public Sub forPhoto()



    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 tblVoters where studentId ='" & txtstudentID.Text & "'"

            cmd.CommandText = sql

            da.SelectCommand = cmd
            da.Fill(table)

            If table.Rows.Count > 0 Then

                MsgBox("Duplicate Student ID")


            Else
                Dim precentoNo As String

                precentoNo = " "

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


                cmd.CommandText = "INSERT INTO tblVoters (StudentId, StudentName, YearLevel, precentoNo, email, contact, course, sphoto) VALUES('" & _
                                                       txtstudentID.Text & "','" & txtstudname.Text & "'," & cbyearlevel.Text & ",'" & precentoNo & "','" & txtemail.Text & "','" & txtcontact.Text & "','" & cbcourse.Text & "'," & strImage & ")"



                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)
        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 Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        clearall(GroupBox1, DataGridView1)
        cbyearlevel.Text = Nothing
        cbcourse.Text = Nothing
        PictureBox1.Image = Nothing
    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(2).Value.ToString
        txtstudname.Text = DataGridView1.CurrentRow.Cells(3).Value.ToString
        cbyearlevel.Text = DataGridView1.CurrentRow.Cells(4).Value.ToString
        cbcourse.Text = DataGridView1.CurrentRow.Cells(5).Value.ToString
        txtemail.Text = DataGridView1.CurrentRow.Cells(7).Value.ToString
        txtcontact.Text = DataGridView1.CurrentRow.Cells(8).Value.ToString

        btnload_Click(sender, e)

    End Sub

    Private Sub btndel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndel.Click
        ejbdelete("Delete * from tblVoters where votersID= " & lblid.Text & "")
        displaymember()
        Button3_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 tblVoters set sphoto =" & strImage & " where votersID = " & lblid.Text & ""

        ejbupdate("UPDATE tblVoters set studentID ='" & txtstudentID.Text & "' , studentName = '" & txtstudname.Text & "', YearLevel =" & cbyearlevel.Text & ", email ='" & txtemail.Text & "', contact ='" & txtcontact.Text & "', course ='" & cbcourse.Text & "' where votersID = " & 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 Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()

    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 btnload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnload.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 tblVoters " & _
                                            " WHERE votersID=" & 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

   
End Class