Kali ini kita akan belajar bersama bagaimana membuat CRUD (Create, Read, Update, Delete) & search data dengan menggunakan VB.NET dan database MySQL.
Aplikasi yang dibutuhkan ialah Microsoft Visual Studio, disini saya menggunakan Microsoft Visual Studio 2010. Sedangkan untuk database nya saya menggunakan MariaDB bawaan web server XAMPP karena OS nya windows.
Tampilan output-nya kira-kira seperti ini :
Tutorial :
1. Siapkan alat tempur yaitu Microsoft Visual Studio (versi terserah) dan install database MySQL (lebih enak install xampp yang sudah satu paket).
2. Buat project baru di vb.net dan jangan lupa pastikan sudah terinstall MySQL Connector Net (Anda dapat mendownload-nya disini).
3. Buat database perkuliahandb (nama optional), dan buat tabel mahasiswa (nama optional) dengan deskripsi field-field sebagai berikut.
4. Klik kanan pada project kita di bagian Solution Explorer (kanan) > Add Reference > .NET > MySql.Data
5. Buat desain form-form nya seperti hasil output program diatas (silakan di explore sesuai keinginan masing-masing), lalu berikan nama unique pada masing-masing komponen di dalamnya.
6. Buat modul baru dengan nama koneksi.vb dan kemudian sesuaikan script nya seperti dibawah ini
koneksi.vb
Imports MySql.Data.MySqlClient
Module koneksi
  Public conn As New MySqlConnection
  Public MySQLReader As MySqlDataReader
  Public CMD As New MySqlCommand
  Public DA As New MySqlDataAdapter
  Public Sub konek(ByVal server As String, ByVal user As String, ByVal pass As String, ByVal db As String)
    If conn.State = ConnectionState.Closed Then
      Dim myString As String = "server=" & server & ";user=" & user & ";password=" & pass & ";database=" & db
      Try
        conn.ConnectionString = myString
        conn.Open()
      Catch ex As MySql.Data.MySqlClient.MySqlException
        MessageBox.Show("Koneksi Gagal" & vbCrLf & "Mohon cek apakah server sudah siap!", "Koneksi ke server", MessageBoxButtons.OK, MessageBoxIcon.Warning)
      End Try
    End If
  End Sub
  Public Sub disconnect()
    Try
      conn.Open()
    Catch ex As MySql.Data.MySqlClient.MySqlException
    End Try
  End Sub
End Module
Module koneksi
  Public conn As New MySqlConnection
  Public MySQLReader As MySqlDataReader
  Public CMD As New MySqlCommand
  Public DA As New MySqlDataAdapter
  Public Sub konek(ByVal server As String, ByVal user As String, ByVal pass As String, ByVal db As String)
    If conn.State = ConnectionState.Closed Then
      Dim myString As String = "server=" & server & ";user=" & user & ";password=" & pass & ";database=" & db
      Try
        conn.ConnectionString = myString
        conn.Open()
      Catch ex As MySql.Data.MySqlClient.MySqlException
        MessageBox.Show("Koneksi Gagal" & vbCrLf & "Mohon cek apakah server sudah siap!", "Koneksi ke server", MessageBoxButtons.OK, MessageBoxIcon.Warning)
      End Try
    End If
  End Sub
  Public Sub disconnect()
    Try
      conn.Open()
    Catch ex As MySql.Data.MySqlClient.MySqlException
    End Try
  End Sub
End Module
Form1.vb
Imports Pertemuan9.koneksi
Imports MySql.Data.MySqlClient
Public Class Form1
  Dim conn As New MySqlConnection("Server=localhost; user=root; database=perkuliahandb")
  Dim perintah As New MySqlCommand
  Dim data As New MySqlDataAdapter
  Dim ds As New DataSet
  Private Sub tampildata()
    Dim dt As DataTable
    Dim adapter As MySqlDataAdapter
    Dim sqlstr As String
    Dim data As Integer
    sqlstr = "SELECT * FROM mahasiswa"
    adapter = New MySqlDataAdapter(sqlstr, conn)
    dt = New DataTable
    data = adapter.Fill(dt)
    If data > 0 Then
      tabelMhs.DataSource = dt
      tabelMhs.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.Fill
      tabelMhs.Columns(0).HeaderText = "NIM"
      tabelMhs.Columns(1).HeaderText = "NAMA"
      tabelMhs.Columns(2).HeaderText = "TEMPAT"
      tabelMhs.Columns(3).HeaderText = "TGL LAHIR"
      tabelMhs.Columns(4).HeaderText = "JENIS KELAMIN"
      tabelMhs.Columns(5).HeaderText = "ALAMAT"
    Else
      tabelMhs.DataSource = Nothing
    End If
    bersih()
  End Sub
  Private Sub bersih()
    txtNim.Text = ""
    txtNama.Text = ""
    txtAlamat.Text = ""
    txtTempat.Text = ""
    cbJk.SelectedIndex = -1
    dtpTgl.Text = ""
    txtNim.Focus()
  End Sub
  Private Sub btnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSimpan.Click
    conn.Open()
    Try
      perintah.CommandType = CommandType.Text
      perintah.CommandText = "INSERT INTO mahasiswa (nim, nama, jekel, tempat, tgl_lahir, alamat) VALUES ('" & txtNim.Text & "', '" & txtNama.Text & "', '" & cbJk.Text & "', '" & txtTempat.Text & "', '" & dtpTgl.Text & "', '" & txtAlamat.Text & "')"
      perintah.Connection = conn
      perintah.ExecuteNonQuery()
      MsgBox("Data berhasil disimpan", MsgBoxStyle.Information, "Informasi")
    Catch ex As Exception
      MsgBox("Data gagal disimpan" + ex.Message, MsgBoxStyle.Critical)
    End Try
    conn.Close()
    tampildata()
  End Sub
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    konek("localhost", "root", "", "perkuliahandb")
    tampildata()
    dtpTgl.Format = DateTimePickerFormat.Custom
    dtpTgl.CustomFormat = "yyyy/MM/dd"
    btnEdit.Enabled = False
    btnHapus.Enabled = False
    btnBatal.Enabled = False
  End Sub
  Private Sub btnKeluar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnKeluar.Click
    Me.Close()
  End Sub
  Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
    conn.Open()
    Try
      Dim perintah As New MySql.Data.MySqlClient.MySqlCommand
      perintah.CommandType = CommandType.Text
      perintah.CommandText = "UPDATE mahasiswa SET nama = '" & txtNama.Text & "' , jekel = '" & cbJk.Text & "' , tempat = '" & txtTempat.Text & "' , tgl_lahir = '" & dtpTgl.Text & "' , alamat = '" & txtAlamat.Text & "' WHERE nim = '" & txtNim.Text & "'"
      perintah.Connection = conn
      perintah.ExecuteNonQuery()
      MySQLReader = perintah.ExecuteReader
      MsgBox("Data berhasil diubah", MsgBoxStyle.Information, "Informasi")
    Catch ex As Exception
      MsgBox("Data gagal diubah" + ex.Message, MsgBoxStyle.Critical)
    End Try
    conn.Close()
    tampildata()
    btnSimpan.Enabled = True
    txtNim.Enabled = True
    txtNim.Focus()
  End Sub
  Private Sub btnHapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHapus.Click
    Dim hasil As MsgBoxResult = MessageBox.Show("Apakah data ingin dihapus?", "Pesan", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
    If hasil = vbOK Then
      conn.Open()
      perintah.Connection = conn
      perintah.CommandType = CommandType.Text
      perintah.CommandText = "DELETE FROM mahasiswa WHERE nim = '" & txtNim.Text & "'"
      perintah.ExecuteNonQuery()
      conn.Close()
    End If
    tampildata()
    btnSimpan.Enabled = True
    txtNim.Focus()
  End Sub
  Private Sub tabelMhs_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles tabelMhs.CellClick
    Dim i As Integer
    i = Me.tabelMhs.CurrentRow.Index
      With tabelMhs.Rows.Item(i)
        Me.txtNim.Text = .Cells(0).Value
        Me.txtNama.Text = .Cells(1).Value
        Me.cbJk.Text = .Cells(4).Value
        Me.txtTempat.Text = .Cells(2).Value
        Me.dtpTgl.Text = .Cells(3).Value
        Me.txtAlamat.Text = .Cells(5).Value
      End With
      txtNim.Enabled = False
      btnSimpan.Enabled = False
      btnEdit.Enabled = True
      btnHapus.Enabled = True
      btnBatal.Enabled = True
  End Sub
  Private Sub txtCari_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCari.KeyPress
    Dim dt As DataTable
    Dim adapter As MySqlDataAdapter
    Dim sqlstr As String
    Dim data As Integer
    If cbCari.Text = "NIM" Then
      sqlstr = "SELECT * FROM mahasiswa WHERE nim LIKE '%" & txtCari.Text & "%'"
    ElseIf cbCari.Text = "Nama" Then
      sqlstr = "SELECT * FROM mahasiswa WHERE nama LIKE '%" & txtCari.Text & "%'"
    Else
      sqlstr = "SELECT * FROM mahasiswa WHERE nim or nama LIKE '%" & txtCari.Text & "%'"
    End If
    adapter = New MySqlDataAdapter(sqlstr, conn)
    dt = New DataTable
    data = adapter.Fill(dt)
    If data > 0 Then
      tabelMhs.DataSource = dt
      tabelMhs.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.Fill
      tabelMhs.Columns(0).HeaderText = "NIM"
      tabelMhs.Columns(1).HeaderText = "NAMA"
      tabelMhs.Columns(2).HeaderText = "JEKEL"
      tabelMhs.Columns(3).HeaderText = "TEMPAT"
      tabelMhs.Columns(4).HeaderText = "TGL. LAHIR"
      tabelMhs.Columns(5).HeaderText = "ALAMAT"
    Else
      tabelMhs.DataSource = Nothing
      'MsgBox("Data tidak ditemukan!", MsgBoxStyle.Information, "Informasi")
    End If
  End Sub
  Private Sub btnBatal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBatal.Click
    bersih()
    txtNim.Enabled = True
    btnSimpan.Enabled = True
    btnEdit.Enabled = False
    btnHapus.Enabled = False
  End Sub
End Class
Imports MySql.Data.MySqlClient
Public Class Form1
  Dim conn As New MySqlConnection("Server=localhost; user=root; database=perkuliahandb")
  Dim perintah As New MySqlCommand
  Dim data As New MySqlDataAdapter
  Dim ds As New DataSet
  Private Sub tampildata()
    Dim dt As DataTable
    Dim adapter As MySqlDataAdapter
    Dim sqlstr As String
    Dim data As Integer
    sqlstr = "SELECT * FROM mahasiswa"
    adapter = New MySqlDataAdapter(sqlstr, conn)
    dt = New DataTable
    data = adapter.Fill(dt)
    If data > 0 Then
      tabelMhs.DataSource = dt
      tabelMhs.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.Fill
      tabelMhs.Columns(0).HeaderText = "NIM"
      tabelMhs.Columns(1).HeaderText = "NAMA"
      tabelMhs.Columns(2).HeaderText = "TEMPAT"
      tabelMhs.Columns(3).HeaderText = "TGL LAHIR"
      tabelMhs.Columns(4).HeaderText = "JENIS KELAMIN"
      tabelMhs.Columns(5).HeaderText = "ALAMAT"
    Else
      tabelMhs.DataSource = Nothing
    End If
    bersih()
  End Sub
  Private Sub bersih()
    txtNim.Text = ""
    txtNama.Text = ""
    txtAlamat.Text = ""
    txtTempat.Text = ""
    cbJk.SelectedIndex = -1
    dtpTgl.Text = ""
    txtNim.Focus()
  End Sub
  Private Sub btnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSimpan.Click
    conn.Open()
    Try
      perintah.CommandType = CommandType.Text
      perintah.CommandText = "INSERT INTO mahasiswa (nim, nama, jekel, tempat, tgl_lahir, alamat) VALUES ('" & txtNim.Text & "', '" & txtNama.Text & "', '" & cbJk.Text & "', '" & txtTempat.Text & "', '" & dtpTgl.Text & "', '" & txtAlamat.Text & "')"
      perintah.Connection = conn
      perintah.ExecuteNonQuery()
      MsgBox("Data berhasil disimpan", MsgBoxStyle.Information, "Informasi")
    Catch ex As Exception
      MsgBox("Data gagal disimpan" + ex.Message, MsgBoxStyle.Critical)
    End Try
    conn.Close()
    tampildata()
  End Sub
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    konek("localhost", "root", "", "perkuliahandb")
    tampildata()
    dtpTgl.Format = DateTimePickerFormat.Custom
    dtpTgl.CustomFormat = "yyyy/MM/dd"
    btnEdit.Enabled = False
    btnHapus.Enabled = False
    btnBatal.Enabled = False
  End Sub
  Private Sub btnKeluar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnKeluar.Click
    Me.Close()
  End Sub
  Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
    conn.Open()
    Try
      Dim perintah As New MySql.Data.MySqlClient.MySqlCommand
      perintah.CommandType = CommandType.Text
      perintah.CommandText = "UPDATE mahasiswa SET nama = '" & txtNama.Text & "' , jekel = '" & cbJk.Text & "' , tempat = '" & txtTempat.Text & "' , tgl_lahir = '" & dtpTgl.Text & "' , alamat = '" & txtAlamat.Text & "' WHERE nim = '" & txtNim.Text & "'"
      perintah.Connection = conn
      perintah.ExecuteNonQuery()
      MySQLReader = perintah.ExecuteReader
      MsgBox("Data berhasil diubah", MsgBoxStyle.Information, "Informasi")
    Catch ex As Exception
      MsgBox("Data gagal diubah" + ex.Message, MsgBoxStyle.Critical)
    End Try
    conn.Close()
    tampildata()
    btnSimpan.Enabled = True
    txtNim.Enabled = True
    txtNim.Focus()
  End Sub
  Private Sub btnHapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHapus.Click
    Dim hasil As MsgBoxResult = MessageBox.Show("Apakah data ingin dihapus?", "Pesan", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
    If hasil = vbOK Then
      conn.Open()
      perintah.Connection = conn
      perintah.CommandType = CommandType.Text
      perintah.CommandText = "DELETE FROM mahasiswa WHERE nim = '" & txtNim.Text & "'"
      perintah.ExecuteNonQuery()
      conn.Close()
    End If
    tampildata()
    btnSimpan.Enabled = True
    txtNim.Focus()
  End Sub
  Private Sub tabelMhs_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles tabelMhs.CellClick
    Dim i As Integer
    i = Me.tabelMhs.CurrentRow.Index
      With tabelMhs.Rows.Item(i)
        Me.txtNim.Text = .Cells(0).Value
        Me.txtNama.Text = .Cells(1).Value
        Me.cbJk.Text = .Cells(4).Value
        Me.txtTempat.Text = .Cells(2).Value
        Me.dtpTgl.Text = .Cells(3).Value
        Me.txtAlamat.Text = .Cells(5).Value
      End With
      txtNim.Enabled = False
      btnSimpan.Enabled = False
      btnEdit.Enabled = True
      btnHapus.Enabled = True
      btnBatal.Enabled = True
  End Sub
  Private Sub txtCari_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCari.KeyPress
    Dim dt As DataTable
    Dim adapter As MySqlDataAdapter
    Dim sqlstr As String
    Dim data As Integer
    If cbCari.Text = "NIM" Then
      sqlstr = "SELECT * FROM mahasiswa WHERE nim LIKE '%" & txtCari.Text & "%'"
    ElseIf cbCari.Text = "Nama" Then
      sqlstr = "SELECT * FROM mahasiswa WHERE nama LIKE '%" & txtCari.Text & "%'"
    Else
      sqlstr = "SELECT * FROM mahasiswa WHERE nim or nama LIKE '%" & txtCari.Text & "%'"
    End If
    adapter = New MySqlDataAdapter(sqlstr, conn)
    dt = New DataTable
    data = adapter.Fill(dt)
    If data > 0 Then
      tabelMhs.DataSource = dt
      tabelMhs.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.Fill
      tabelMhs.Columns(0).HeaderText = "NIM"
      tabelMhs.Columns(1).HeaderText = "NAMA"
      tabelMhs.Columns(2).HeaderText = "JEKEL"
      tabelMhs.Columns(3).HeaderText = "TEMPAT"
      tabelMhs.Columns(4).HeaderText = "TGL. LAHIR"
      tabelMhs.Columns(5).HeaderText = "ALAMAT"
    Else
      tabelMhs.DataSource = Nothing
      'MsgBox("Data tidak ditemukan!", MsgBoxStyle.Information, "Informasi")
    End If
  End Sub
  Private Sub btnBatal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBatal.Click
    bersih()
    txtNim.Enabled = True
    btnSimpan.Enabled = True
    btnEdit.Enabled = False
    btnHapus.Enabled = False
  End Sub
End Class
7. Silakan di atur-atur juga bagian properties komponen-komponen di dalamnya sesuai selera.
8. Selesai. Selamat dan semoga berhasil :)
Kira-kira seperti itu tutorial pembuatannya. Cukup mudah bukan?
Bagi teman-teman yang belum bisa praktek atau ingin mendapatkan langsung source code jadi + database nya. Silakan download pada link dibawah ini :
Catatan :
Jika Anda mendownload source code ini dan ketika dijalankan terjadi error seperti gambar dibawah ini,
Maka solusinya adalah klik kanan project kita di Solution Explorer (biasanya di kanan atas), kemudian cari bagian menu References dan remove dulu bagian MySql.Data nya (biasanya paling atas). Setelah itu ulangi Add Reference seperti pada tutorial nomor 4 diatas.
* Referensi :
Modul Praktikum Pemrograman Terstuktur (Pertemuan ke IXb dan Xa) jurusan Teknik Informatika Semester 2, yang diampu oleh Dosen Ahmad Abdul Chamid, S.Kom., M.Kom. di Universitas Muria Kudus. Tetapi source code sudah di modifikasi sesuai kebutuhan oleh YukCoding.
Semoga bermanfaat, happy coding :)
Wassalamu'alaikum wr. wb.
No comments:
Post a Comment