kali ini saya membuat CRUD sederhana dengan vb.NET dengan database ms.Acces
selanjutnya source code sebagai berikut
Imports System.Data.OleDb
Public Class Form1
Private dt As DataTable
Dim adapter As OleDbDataAdapter
Private Conn As OleDbConnection
Private cmd As OleDbCommand
Private sql As String
Private reader As OleDbDataReader
Private da As OleDbDataAdapter
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 LoadData()
Connect()
sql = "select ID from uas"
cmd = New OleDbCommand(sql, Conn)
reader = cmd.ExecuteReader()
Closedd()
End Sub
Sub Tampil()
Connect()
Try
Dim dt As New DataTable
da = New OleDbDataAdapter("select * from uas", Conn)
da.Fill(dt)
DataGridView1.DataSource = dt
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
Dim sqlstr As String = "Select * from uas"
Dim connstr As String
connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb"
Dim dc As DataColumn
dt = New DataTable
Try
Conn = New OleDbConnection(connstr)
Conn.Open()
adapter = New OleDbDataAdapter(sqlstr, Conn)
adapter.Fill(dt)
'Memasukan nama kolom ke combobox
For Each dc In dt.Columns
CmbFilter.Items.Add(dc.ColumnName)
Next
CmbFilter.Items.RemoveAt(0)
CmbFilter.SelectedIndex = 0
Conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Conn.Dispose()
End Try
Dim ConnString As String
ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Database1.accdb"
Try
Conn = New OleDbConnection(ConnString)
Conn.Open()
LoadData()
Tampil()
Conn.Close()
Catch ex As Exception
MessageBox.Show("Koneksi Error : " + ex.Message)
End Try
CmbKelamin.Items.Add("P")
CmbKelamin.Items.Add("L")
End Sub
Private Sub BtnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit.Click
Connect()
Dim hasil As Integer
sql = "Update uas set nama='" & TxtNama.Text & "',alamat='" & TxtAlamat.Text & "', kelamin='" & CmbKelamin.Text & "', telp='" & TxtTlp.Text & "' where ID='" & CmbId.Text & "'"
cmd = New OleDbCommand(sql, Conn)
Try
hasil = cmd.ExecuteNonQuery
If (hasil > 0) Then
MessageBox.Show("Berhasil", "Sukses", MessageBoxButtons.OK, MessageBoxIcon.Information)
CmbId.Text = ""
CmbKelamin.Text = ""
TxtAlamat.Text = ""
TxtNama.Text = ""
TxtTlp.Text = ""
Tampil()
LoadData()
End If
Catch ex As OleDbException
MessageBox.Show("Failed : " & ex.Message)
End Try
Closedd()
End Sub
Private Sub BtnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSimpan.Click
Connect()
Dim insertquery As String
Dim Kelamin As String
Dim Hasil As Integer
Dim cmd As OleDbCommand
insertquery = ("insert into uas(ID,nama,alamat,telp,kelamin)Values('" & CmbId.Text & "','" & TxtNama.Text & "','" & TxtAlamat.Text & "','" & TxtTlp.Text & "','" & Kelamin & "')")
Try
cmd = New OleDbCommand(insertquery, Conn)
Hasil = cmd.ExecuteNonQuery
If Hasil > 0 Then
MessageBox.Show("Berhasil", "Sukses", MessageBoxButtons.OK, MessageBoxIcon.Information)
CmbId.Text = ""
CmbKelamin.Text = ""
TxtAlamat.Text = ""
TxtNama.Text = ""
TxtTlp.Text = ""
Tampil()
LoadData()
End If
Catch ex As OleDbException
MessageBox.Show("Failed : " & ex.Message, "Gagal", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Closedd()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Connect()
Dim index As Integer = CmbId.SelectedIndex
Dim hasil As Integer
Dim pesan As DialogResult
sql = "delete from uas where ID='" & CmbId.Text & "'"
pesan = MessageBox.Show("Yakin...?", "Konfirmasi", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
cmd = New OleDbCommand(sql, Conn)
Try
If pesan = Windows.Forms.DialogResult.Yes = True Then
hasil = cmd.ExecuteNonQuery
CmbId.Text = ""
CmbKelamin.Text = ""
TxtAlamat.Text = ""
TxtNama.Text = ""
TxtTlp.Text = ""
Tampil()
End If
Catch ex As OleDbException
MsgBox("Failed : " & ex.Message)
End Try
Closedd()
End Sub
End Class
