Progaram Koneksi Database Access Dengan Gambar
Pada VB 2010
Pada kesempatan kali ini saya ingin berbagi hal yang saya tau kepada reader untuk menghadapi Uji Kompetensi ( UK ) bagi Jurusan RPL ( Rekayasa Perangkat Lunak ). Biasanya UK tersebut kita dituntut untuk membuat suatu program koneksi database menggunakan Ms.Access ataupun Sql Server.
Awalnya saya juga merasa kesulitan dalam menghadapi UK terlebih lagi kalau dalam database tersebut terdapat gambar, namun setelah saya belajar dan melihat-lihat di youtube maupun di google akhirnya saya menumakan solusinya, yaitu bagaimana cara meng Add, Update, Delete, and Search baik databasenya terdapat foto maupun tidak.
Berikut Desain Form dan Coding Programnyaa......
- Desain Form :
2. Coding Program
Imports System.Data.OleDb
Imports System.IO
Public Class DaftarSiswa
Private Con As New OleDbConnection
Private cmd As New OleDbCommand
Private da As New OleDbDataAdapter
Private rd As OleDbDataReader
Private ds As New DataSet
Private str As String
Sub koneksi()
str="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ADMIN™\Documents\DataSiswaPKL.accdb"
Con = New OleDbConnection(str)
If Con.State = ConnectionState.Closed Then
Con.Open()
End If
End Sub
Sub tampil()
da = New OleDbDataAdapter("select * from Siswa", Con)
ds = New DataSet
da.Fill(ds, "Siswa")
DG.AllowUserToAddRows = False
DG.RowTemplate.Height = 100
Dim imgc As New DataGridViewImageColumn
DG.DataSource = ds.Tables("Siswa")
imgc = DG.Columns(8)
imgc.ImageLayout = DataGridViewImageCellLayout.Stretch
End Sub
Sub bersih()
TBNIS.Text = ""
TBNAMA.Text = ""
TBTEMPAT.Text = ""
TBTANGGAL.Text = ""
CBJK.Text = Nothing
CBAGAMA.Text = Nothing
TBHP.Text = ""
TBALAMAT.Text = ""
FOTO.Image = Nothing
TBNIS.Focus()
End Sub
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
koneksi()
tampil()
BNcloses.Enabled = False
End Sub
Private Sub BNCleanForm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNCleanForm.Click
bersih()
End Sub
Private Sub BNcencel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNcencel.Click
TBcari.Text = ""
TBcari.Focus()
bersih()
End Sub
Private Sub BNSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNSave.Click
Dim ms As New MemoryStream
FOTO.Image.Save(ms, FOTO.Image.RawFormat)
Dim img() As Byte
img = ms.ToArray()
Using cmd As New OleDbCommand("INSERT INTO Siswa(NIS,NAMA,TEMPAT,TANGGAL,JEN_KEL,AGAMA,HP,ALAMAT,FOTO)VALUES('" & TBNIS.Text & "','" & TBNAMA.Text & "','" & TBTEMPAT.Text & "','" & TBTANGGAL.Text & "','" & CBJK.Text & "','" & CBAGAMA.Text & "','" & TBHP.Text & "','" & TBALAMAT.Text & "',@img)", Con)
cmd.Parameters.Add("@img", OleDbType.Binary).Value = img
cmd.ExecuteNonQuery()
End Using
MsgBox("Add Success...!!!")
tampil()
bersih()
End Sub
Private Sub BNDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNDelete.Click
cmd = New OleDbCommand("SELECT * FROM Siswa where NIS='" & TBNIS.Text & "'", Con)
rd = cmd.ExecuteReader
rd.Read()
If rd.HasRows Then
Dim pesan As MsgBoxResult
pesan = MsgBox("Apakah Anda Yakin Ingin Menghapus Data Ini....!!!", MsgBoxStyle.YesNo, "Pemberitahuan..!!")
If pesan = MsgBoxResult.Yes Then
Using cmd As New OleDbCommand("delete * from Siswa where NIS='" & TBNIS.Text & "'", Con)
cmd.ExecuteNonQuery()
End Using
MsgBox("Success Hapus Data..!!")
tampil()
bersih()
End If
ElseIf TBNIS.Text = "" Then
MsgBox("Mohon Masukkan NIS Terlebih Dahulu...!!!")
Else
MsgBox("Maaf NIS " & TBNIS.Text & " Tidak Terdaftar Atau Tidak Ada..!!")
bersih()
End If
Con.Close()
End Sub
Private Sub BNUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNUpdate.Click
Dim ms As New MemoryStream
FOTO.Image.Save(ms, FOTO.Image.RawFormat)
Dim img() As Byte
img = ms.ToArray()
Using cmd As New OleDbCommand("Update Siswa Set NAMA='" & TBNAMA.Text & "',TEMPAT='" & TBTEMPAT.Text & "',TANGGAL='" & TBTANGGAL.Text & "',JEN_KEL='" & CBJK.Text & "',AGAMA='" & CBAGAMA.Text & "',HP='" & TBHP.Text & "',ALAMAT='" & TBALAMAT.Text & "',FOTO=@img Where NIS='" & TBNIS.Text & "'", Con)
cmd.Parameters.Add("@img", OleDbType.Binary).Value = img
cmd.ExecuteNonQuery()
End Using
MsgBox("Update Success...!!!")
tampil()
bersih()
End Sub
Private Sub DG_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG.CellContentClick
Try
TBNIS.Text = DG.CurrentRow.Cells(0).Value
TBNAMA.Text = DG.CurrentRow.Cells(1).Value
TBTEMPAT.Text = DG.CurrentRow.Cells(2).Value
TBTANGGAL.Text = DG.CurrentRow.Cells(3).Value
CBJK.Text = DG.CurrentRow.Cells(4).Value
CBAGAMA.Text = DG.CurrentRow.Cells(5).Value
TBHP.Text = DG.CurrentRow.Cells(6).Value
TBALAMAT.Text = DG.CurrentRow.Cells(7).Value
Dim img As Byte()
img = DG.CurrentRow.Cells(8).Value
Dim ms As New MemoryStream(img)
FOTO.Image = Image.FromStream(ms)
Catch ex As Exception
End Try
End Sub
Private Sub BNSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNSearch.Click
cmd = New OleDbCommand("SELECT * FROM Siswa where NIS='" & TBcari.Text & "'", Con)
rd = cmd.ExecuteReader
rd.Read()
If rd.HasRows Then
MsgBox("Data Ditemukan..!!")
TBNIS.Text = rd.Item("NIS")
TBNAMA.Text = rd.Item("NAMA")
TBTEMPAT.Text = rd.Item("TEMPAT")
TBTANGGAL.Text = rd.Item("TANGGAL")
CBJK.Text = rd.Item("JEN_KEL")
CBAGAMA.Text = rd.Item("AGAMA")
TBHP.Text = rd.Item("HP")
TBALAMAT.Text = rd.Item("ALAMAT")
Dim img As Byte()
img = DG.CurrentRow.Cells(8).Value
Dim ms As New MemoryStream(img)
FOTO.Image = Image.FromStream(ms)
TBcari.Text = ""
TBcari.Focus()
ElseIf TBcari.Text = "" Then
MsgBox("Mohon Masukkan NIS Terlebih Dahulu...!!!")
Else
MsgBox("Maaf NIS " & TBcari.Text & " Tidak Terdaftar Atau Tidak Ada..!!")
TBcari.Text = ""
TBcari.Focus()
End If
End Sub
Private Sub BNcloses_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNcloses.Click
Dim pesan As MsgBoxResult
pesan = MsgBox("Apakah Anda Yakin Ingin Keluar Dari Program Ini...!!!", MsgBoxStyle.OkCancel, "Pemberitahuan..!!")
If pesan = MsgBoxResult.Ok Then
Me.Close()
MenuUtama.Show()
End If
End Sub
Private Sub Bnbrowsh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Bnbrowsh.Click
On Error Resume Next
OpenFileDialog1.Filter = Nothing
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
FOTO.Image = Image.FromFile(OpenFileDialog1.FileName)
End If
Dim ms As New MemoryStream
FOTO.Image.Save(ms, FOTO.Image.RawFormat)
End Sub
Private Sub BNremove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNremove.Click
FOTO.Image = Nothing
End Sub
Private Sub TBcari_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TBcari.KeyPress
'validasi hanya memasukkan angka
If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack) Then e.Handled = True
End Sub
Private Sub TBNAMA_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TBNAMA.KeyPress
'validasi hanya memasukkan huruf
If Not ((e.KeyChar >= "a" And e.KeyChar <= "z" Or e.KeyChar >= "A" And e.KeyChar <= "Z") Or e.KeyChar = vbBack) Then e.Handled = True
End Sub
End Class
Imports System.IO
Public Class DaftarSiswa
Private Con As New OleDbConnection
Private cmd As New OleDbCommand
Private da As New OleDbDataAdapter
Private rd As OleDbDataReader
Private ds As New DataSet
Private str As String
Sub koneksi()
str="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ADMIN™\Documents\DataSiswaPKL.accdb"
Con = New OleDbConnection(str)
If Con.State = ConnectionState.Closed Then
Con.Open()
End If
End Sub
Sub tampil()
da = New OleDbDataAdapter("select * from Siswa", Con)
ds = New DataSet
da.Fill(ds, "Siswa")
DG.AllowUserToAddRows = False
DG.RowTemplate.Height = 100
Dim imgc As New DataGridViewImageColumn
DG.DataSource = ds.Tables("Siswa")
imgc = DG.Columns(8)
imgc.ImageLayout = DataGridViewImageCellLayout.Stretch
End Sub
Sub bersih()
TBNIS.Text = ""
TBNAMA.Text = ""
TBTEMPAT.Text = ""
TBTANGGAL.Text = ""
CBJK.Text = Nothing
CBAGAMA.Text = Nothing
TBHP.Text = ""
TBALAMAT.Text = ""
FOTO.Image = Nothing
TBNIS.Focus()
End Sub
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
koneksi()
tampil()
BNcloses.Enabled = False
End Sub
Private Sub BNCleanForm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNCleanForm.Click
bersih()
End Sub
Private Sub BNcencel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNcencel.Click
TBcari.Text = ""
TBcari.Focus()
bersih()
End Sub
Private Sub BNSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNSave.Click
Dim ms As New MemoryStream
FOTO.Image.Save(ms, FOTO.Image.RawFormat)
Dim img() As Byte
img = ms.ToArray()
Using cmd As New OleDbCommand("INSERT INTO Siswa(NIS,NAMA,TEMPAT,TANGGAL,JEN_KEL,AGAMA,HP,ALAMAT,FOTO)VALUES('" & TBNIS.Text & "','" & TBNAMA.Text & "','" & TBTEMPAT.Text & "','" & TBTANGGAL.Text & "','" & CBJK.Text & "','" & CBAGAMA.Text & "','" & TBHP.Text & "','" & TBALAMAT.Text & "',@img)", Con)
cmd.Parameters.Add("@img", OleDbType.Binary).Value = img
cmd.ExecuteNonQuery()
End Using
MsgBox("Add Success...!!!")
tampil()
bersih()
End Sub
Private Sub BNDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNDelete.Click
cmd = New OleDbCommand("SELECT * FROM Siswa where NIS='" & TBNIS.Text & "'", Con)
rd = cmd.ExecuteReader
rd.Read()
If rd.HasRows Then
Dim pesan As MsgBoxResult
pesan = MsgBox("Apakah Anda Yakin Ingin Menghapus Data Ini....!!!", MsgBoxStyle.YesNo, "Pemberitahuan..!!")
If pesan = MsgBoxResult.Yes Then
Using cmd As New OleDbCommand("delete * from Siswa where NIS='" & TBNIS.Text & "'", Con)
cmd.ExecuteNonQuery()
End Using
MsgBox("Success Hapus Data..!!")
tampil()
bersih()
End If
ElseIf TBNIS.Text = "" Then
MsgBox("Mohon Masukkan NIS Terlebih Dahulu...!!!")
Else
MsgBox("Maaf NIS " & TBNIS.Text & " Tidak Terdaftar Atau Tidak Ada..!!")
bersih()
End If
Con.Close()
End Sub
Private Sub BNUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNUpdate.Click
Dim ms As New MemoryStream
FOTO.Image.Save(ms, FOTO.Image.RawFormat)
Dim img() As Byte
img = ms.ToArray()
Using cmd As New OleDbCommand("Update Siswa Set NAMA='" & TBNAMA.Text & "',TEMPAT='" & TBTEMPAT.Text & "',TANGGAL='" & TBTANGGAL.Text & "',JEN_KEL='" & CBJK.Text & "',AGAMA='" & CBAGAMA.Text & "',HP='" & TBHP.Text & "',ALAMAT='" & TBALAMAT.Text & "',FOTO=@img Where NIS='" & TBNIS.Text & "'", Con)
cmd.Parameters.Add("@img", OleDbType.Binary).Value = img
cmd.ExecuteNonQuery()
End Using
MsgBox("Update Success...!!!")
tampil()
bersih()
End Sub
Private Sub DG_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG.CellContentClick
Try
TBNIS.Text = DG.CurrentRow.Cells(0).Value
TBNAMA.Text = DG.CurrentRow.Cells(1).Value
TBTEMPAT.Text = DG.CurrentRow.Cells(2).Value
TBTANGGAL.Text = DG.CurrentRow.Cells(3).Value
CBJK.Text = DG.CurrentRow.Cells(4).Value
CBAGAMA.Text = DG.CurrentRow.Cells(5).Value
TBHP.Text = DG.CurrentRow.Cells(6).Value
TBALAMAT.Text = DG.CurrentRow.Cells(7).Value
Dim img As Byte()
img = DG.CurrentRow.Cells(8).Value
Dim ms As New MemoryStream(img)
FOTO.Image = Image.FromStream(ms)
Catch ex As Exception
End Try
End Sub
Private Sub BNSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNSearch.Click
cmd = New OleDbCommand("SELECT * FROM Siswa where NIS='" & TBcari.Text & "'", Con)
rd = cmd.ExecuteReader
rd.Read()
If rd.HasRows Then
MsgBox("Data Ditemukan..!!")
TBNIS.Text = rd.Item("NIS")
TBNAMA.Text = rd.Item("NAMA")
TBTEMPAT.Text = rd.Item("TEMPAT")
TBTANGGAL.Text = rd.Item("TANGGAL")
CBJK.Text = rd.Item("JEN_KEL")
CBAGAMA.Text = rd.Item("AGAMA")
TBHP.Text = rd.Item("HP")
TBALAMAT.Text = rd.Item("ALAMAT")
Dim img As Byte()
img = DG.CurrentRow.Cells(8).Value
Dim ms As New MemoryStream(img)
FOTO.Image = Image.FromStream(ms)
TBcari.Text = ""
TBcari.Focus()
ElseIf TBcari.Text = "" Then
MsgBox("Mohon Masukkan NIS Terlebih Dahulu...!!!")
Else
MsgBox("Maaf NIS " & TBcari.Text & " Tidak Terdaftar Atau Tidak Ada..!!")
TBcari.Text = ""
TBcari.Focus()
End If
End Sub
Private Sub BNcloses_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNcloses.Click
Dim pesan As MsgBoxResult
pesan = MsgBox("Apakah Anda Yakin Ingin Keluar Dari Program Ini...!!!", MsgBoxStyle.OkCancel, "Pemberitahuan..!!")
If pesan = MsgBoxResult.Ok Then
Me.Close()
MenuUtama.Show()
End If
End Sub
Private Sub Bnbrowsh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Bnbrowsh.Click
On Error Resume Next
OpenFileDialog1.Filter = Nothing
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
FOTO.Image = Image.FromFile(OpenFileDialog1.FileName)
End If
Dim ms As New MemoryStream
FOTO.Image.Save(ms, FOTO.Image.RawFormat)
End Sub
Private Sub BNremove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BNremove.Click
FOTO.Image = Nothing
End Sub
Private Sub TBcari_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TBcari.KeyPress
'validasi hanya memasukkan angka
If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack) Then e.Handled = True
End Sub
Private Sub TBNAMA_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TBNAMA.KeyPress
'validasi hanya memasukkan huruf
If Not ((e.KeyChar >= "a" And e.KeyChar <= "z" Or e.KeyChar >= "A" And e.KeyChar <= "Z") Or e.KeyChar = vbBack) Then e.Handled = True
End Sub
End Class
3. Hasil :
