Tutorial + Source Code CRUD & Searching Data dengan VB.NET dan MariaDB

Assalamu'alaikum wr. wb.

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

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

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.