Program Sederhana - Penerimaan Pegawai Baru (reqruitment) - vb.net

Apa kabar?

Ok, luarbiasa...

Postingan kali ini, saya coba mempublish program sederhana untuk mendata pegawai baru yang akan masuk di sebuah perusahaan.

Berikut adalah tampilan/screenshoot program nya:

Dan berikut adalah source code programnya:

Imports System.Data.OleDb 'untuk koneksi ke database menggunakan OLDB
Imports excel = Microsoft.Office.Interop.Excel 'librarry ms office untuk bisa export ke MsExcel
Imports word = Microsoft.Office.Interop.Word 'librarry ms office untuk bisa export ke MsWord
Public Class formCalonPegawai
    'Dim koneksi As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\02. PERPUSTAKAAN\Kuliah\Semester 5\Visual Programming\aplikasiReqruitmentPegawai\dataPegawai.accdb")
    Private jenisKelamin As String, statusMenikah As String, statusTerima As String
    Private sedangEdit As Boolean = False
    Private Conn As OleDbConnection = Nothing
    Private cmd As OleDbCommand = Nothing
    Private sql As String = Nothing
    Private reader As OleDbDataReader = Nothing
    Private da As OleDbDataAdapter = Nothing
    Function Connect()
        If Not Conn Is Nothing Then
            Conn.Close()
        End If
        Conn.Open()
        Return Conn
    End Function
    Function Closedd()
        Conn.Close()
        Return Conn
    End Function

    Sub Tampil()
        Dim baris As Integer
        Connect()

        Try
            Dim dt As New DataTable
            da = New OleDbDataAdapter("select * from tbl_pegawai", Conn)
            da.Fill(dt)
            Dim alamat As String
            ListView1.Items.Clear()
            For baris = 0 To dt.Rows.Count - 1
                alamat = "Jalan:" & dt.Rows(baris).Item("jalan").ToString & " RT/RW:" & dt.Rows(baris).Item("rt").ToString & "/" & dt.Rows(baris).Item("rw").ToString
                Dim itemListView As New ListViewItem
                itemListView.Text = dt.Rows(baris).Item("noKtp").ToString
                itemListView.SubItems.Add(dt.Rows(baris).Item("nama").ToString)
                itemListView.SubItems.Add(dt.Rows(baris).Item("tempatLahir").ToString)
                itemListView.SubItems.Add(dt.Rows(baris).Item("tanggalLahir").ToString)
                itemListView.SubItems.Add(dt.Rows(baris).Item("jenisKelamin").ToString)
                itemListView.SubItems.Add(alamat)
                itemListView.SubItems.Add(dt.Rows(baris).Item("agama").ToString)
                itemListView.SubItems.Add(dt.Rows(baris).Item("statusNikah").ToString)
                itemListView.SubItems.Add(dt.Rows(baris).Item("statusTerima").ToString)
                ListView1.Items.Add(itemListView)
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        Closedd()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Conn Is Nothing Then Conn.Close()

        Dim ConnString As String

        ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\02. PERPUSTAKAAN\Kuliah\Semester 5\Visual Programming\aplikasiReqruitmentPegawai\dataPegawai.accdb"
        Try
            Conn = New OleDbConnection(ConnString)
            Conn.Open()
            Me.Text = "Data Penerimaan Calon Pegawai"
            Tampil()
            Conn.Close()
        Catch ex As Exception
            MessageBox.Show("Koneksi Error : " + ex.Message)
        End Try

        ListView1.Columns.Add("No. KTP")
        ListView1.Columns.Add("Nama")
        ListView1.Columns.Add("Tempat Lahir")
        ListView1.Columns.Add("Tanggal Lahir")
        ListView1.Columns.Add("Jenis Kelamin")
        ListView1.Columns.Add("Alamat")
        ListView1.Columns.Add("Agama")
        ListView1.Columns.Add("Status Nikah")
        ListView1.Columns.Add("Status")
        ListView1.View = View.Details
        ListView1.FullRowSelect = True

        cAgama.Items.Add("Islam")
        cAgama.Items.Add("Protestan")
        cAgama.Items.Add("Katholik")
        cAgama.Items.Add("Hindu")
        cAgama.Items.Add("Budha")
        cAgama.Items.Add("Konghucu")
    End Sub

    Private Sub RadioButton4_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        statusTerima = "1"
    End Sub

    Private Sub bSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bSimpan.Click
        Connect()
        Dim insertquery As String
        Dim Hasil As Integer
        Dim cmd As OleDbCommand

        If sedangEdit = False Then
            insertquery = ("insert into tbl_pegawai(noKtp, nama, tempatLahir, tanggalLahir, jenisKelamin, jalan, rt, rw, desa, kec, kabKota, agama, statusNikah, statusTerima)Values('" & tNoKTP.Text & "','" & tNama.Text & "','" & tTempatLahir.Text & "','" & tTanggalLahir.Text & "','" & jenisKelamin & "','" & tJalan.Text & "','" & tRT.Text & "','" & tRW.Text & "','" & tDesa.Text & "','" & tKec.Text & "','" & tKab.Text & "','" & cAgama.Text & "','" & statusMenikah & "','" & statusTerima & "')")
        Else
            insertquery = "UPDATE tbl_pegawai set nama='" & tNama.Text & "', tempatLahir='" & tTempatLahir.Text & "', tanggalLahir='" & tTanggalLahir.Value & "', jenisKelamin='" & jenisKelamin & "', jalan='" & tJalan.Text & "', rt='" & tRT.Text & "', rw='" & tRW.Text & "', desa='" & tDesa.Text & "', kec='" & tKec.Text & "', kabKota='" & tKab.Text & "', agama='" & cAgama.Text & "', statusNikah='" & statusMenikah & "', statusTerima='" & statusTerima & "' where noKtp='" & tNoKTP.Text & "'"
        End If

        Try
            cmd = New OleDbCommand(insertquery, Conn)
            Hasil = cmd.ExecuteNonQuery
            If Hasil > 0 Then
                MessageBox.Show("Record Berhasil Dimasukan", "Sukses", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Tampil()

            End If
        Catch ex As OleDbException
            MessageBox.Show("Failed : " & ex.Message, "Gagal", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        bEdit.Enabled = True
        bHapus.Enabled = True
        sedangEdit = False
        tNoKTP.Enabled = True
        kosong()
        Closedd()
    End Sub
    Private Sub bEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bEdit.Click
        Dim baris As Integer
        For baris = 0 To ListView1.Items.Count - 1
            If ListView1.Items(baris).Selected = True Then
                Dim dt As New DataTable
                da = New OleDbDataAdapter("select * from tbl_pegawai where noKtp='" & ListView1.Items(baris).Text & "'", Conn)
                da.Fill(dt)

                tNoKTP.Text = dt.Rows(0).Item("noKtp").ToString
                tNama.Text = dt.Rows(0).Item("nama").ToString
                tTempatLahir.Text = dt.Rows(0).Item("tempatLahir").ToString
                tTanggalLahir.Value = dt.Rows(0).Item("tanggalLahir").ToString
                If dt.Rows(0).Item("jenisKelamin").ToString = "Laki-laki" Then
                    opLaki.Checked = True
                Else
                    opPerempuan.Checked = True
                End If

                tJalan.Text = dt.Rows(0).Item("jalan").ToString
                tRT.Text = dt.Rows(0).Item("rt").ToString
                tRW.Text = dt.Rows(0).Item("rw").ToString
                tDesa.Text = dt.Rows(0).Item("desa").ToString
                tKec.Text = dt.Rows(0).Item("kec").ToString
                tKab.Text = dt.Rows(0).Item("kabkota").ToString
                cAgama.Text = dt.Rows(0).Item("agama").ToString

                If dt.Rows(0).Item("statusNikah").ToString = "1" Then
                    opSudahMenikah.Checked = True
                Else
                    opBelumMenikah.Checked = True
                End If

                If dt.Rows(0).Item("statusTerima").ToString = "1" Then
                    opDiterima.Checked = True
                ElseIf dt.Rows(0).Item("statusTerima").ToString = "2" Then
                    opPending.Checked = True
                ElseIf dt.Rows(0).Item("statusTerima").ToString = "3" Then
                    opDitolak.Checked = True
                End If

                sedangEdit = True
                bEdit.Enabled = False
                bHapus.Enabled = False
                tNoKTP.Enabled = False
            End If
        Next
    End Sub

    Private Sub opLaki_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles opLaki.CheckedChanged
        jenisKelamin = "Laki-laki"
    End Sub

    Private Sub opPerempuan_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles opPerempuan.CheckedChanged
        jenisKelamin = "Perempuan"
    End Sub

    Private Sub opSudahMenikah_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles opSudahMenikah.CheckedChanged
        statusMenikah = "1"
    End Sub

    Private Sub opBelumMenikah_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles opBelumMenikah.CheckedChanged
        statusMenikah = "0"
    End Sub

    Sub kosong()
        tNoKTP.Text = ""
        tNama.Text = ""
        tTempatLahir.Text = ""
        tJalan.Text = ""
        tRT.Text = ""
        tRW.Text = ""
        tDesa.Text = ""
        tKec.Text = ""
        tKab.Text = ""
        cAgama.Text = ""
    End Sub

    Private Sub opDiterima_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles opDiterima.CheckedChanged
        statusTerima = "1"
    End Sub

    Private Sub opDitolak_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles opDitolak.CheckedChanged
        statusTerima = "3"
    End Sub

    Private Sub opPending_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles opPending.CheckedChanged
        statusTerima = "2"
    End Sub

    Private Sub bHapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bHapus.Click
        Connect()
        Dim query As String
        Dim Hasil As Integer
        Dim cmd As OleDbCommand
        For baris = 0 To ListView1.Items.Count - 1
            If ListView1.Items(baris).Selected = True Then
                query = "DELETE FROM tbl_pegawai where noKtp='" & ListView1.Items(baris).Text & "'"
                Try
                    cmd = New OleDbCommand(query, Conn)
                    Hasil = cmd.ExecuteNonQuery
                    If Hasil > 0 Then
                        MessageBox.Show("Record Berhasil dihapus", "Sukses", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Tampil()
                    End If
                Catch ex As OleDbException
                    MessageBox.Show("Failed : " & ex.Message, "Gagal", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End If
        Next
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'UNTUK EXPORT KE EXCEL

        Dim excelApp = New excel.Application
        Dim excelWorkbook = excelApp.Workbooks.Open("E:\02. PERPUSTAKAAN\Kuliah\Semester 5\Visual Programming\aplikasiReqruitmentPegawai\dataPegawai.xlsx")

        Dim noRow As Integer
     
        Dim baris As Integer
        Connect()

        Try
            Dim dt As New DataTable
            da = New OleDbDataAdapter("select * from tbl_pegawai", Conn)
            da.Fill(dt)
            For baris = 0 To dt.Rows.Count - 1
                noRow = noRow + 1
                excelApp.Range("A" & (noRow + 1)).Value = noRow
                excelApp.Range("B" & (noRow + 1)).Value = dt.Rows(baris).Item("noKtp").ToString()
                excelApp.Range("C" & (noRow + 1)).Value = dt.Rows(baris).Item("nama").ToString()
                excelApp.Range("D" & (noRow + 1)).Value = dt.Rows(baris).Item("tempatLahir").ToString()
                excelApp.Range("E" & (noRow + 1)).Value = dt.Rows(baris).Item("tanggalLahir").ToString()
                excelApp.Range("F" & (noRow + 1)).Value = dt.Rows(baris).Item("jenisKelamin").ToString()
                excelApp.Range("G" & (noRow + 1)).Value = dt.Rows(baris).Item("jalan").ToString()
                excelApp.Range("H" & (noRow + 1)).Value = dt.Rows(baris).Item("rt").ToString()
                excelApp.Range("I" & (noRow + 1)).Value = dt.Rows(baris).Item("rw").ToString()
                excelApp.Range("J" & (noRow + 1)).Value = dt.Rows(baris).Item("desa").ToString()
                excelApp.Range("K" & (noRow + 1)).Value = dt.Rows(baris).Item("kec").ToString()
                excelApp.Range("L" & (noRow + 1)).Value = dt.Rows(baris).Item("kabkota").ToString()
                excelApp.Range("M" & (noRow + 1)).Value = dt.Rows(baris).Item("agama").ToString()
                excelApp.Range("N" & (noRow + 1)).Value = dt.Rows(baris).Item("statusNikah").ToString()
                excelApp.Range("O" & (noRow + 1)).Value = dt.Rows(baris).Item("statusTerima").ToString()
            Next

            excelWorkbook.Save()
            If (MessageBox.Show("Data berhasildi export, klik ok untuk membuka file", "Export", MessageBoxButtons.OKCancel) = Windows.Forms.DialogResult.OK) Then
                excelApp.Visible = True
            Else
                excelWorkbook.Close()
                excelApp.Quit()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Closedd()
    End Sub
End Class