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

Assalamu'alaikum wr. wb.

Kali ini kita akan berguru bersama bagaimana membuat CRUD (Create, Read, Update, Delete) & search data dengan menggunakan VB.NET dan database MySQL. Disini aku menggunakan Microsoft Visual Studio 2010.

Tampilan output-nya kira-kira menyerupai 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 gres 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 bab Solution Explorer (kanan) > Add Reference > .NET > MySql.Data

5. Buat desain form-form nya menyerupai hasil output aktivitas diatas (silakan di explore sesuai impian masing-masing), lalu berikan nama unique pada masing-masing komponen di dalamnya.

6. Buat modul gres dengan nama koneksi.vb dan kemudian sesuaikan script nya menyerupai 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 & "' , kawasan = '" & 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 bab properties komponen-komponen di dalamnya sesuai selera.

8. Selesai. Selamat dan biar berhasil :)

Kira-kira menyerupai itu tutorial pembuatannya. Cukup mudah bukan?

Bagi teman-teman yang belum mampu praktek atau ingin menerima pribadi source code jadi + database nya. Silakan unduh pada link dibawah ini :





Catatan :
Jika Anda mendownload source code ini dan saat dijalankan terjadi error menyerupai gambar dibawah ini,

Maka solusinya yaitu klik kanan project kita di Solution Explorer (biasanya di kanan atas), kemudian cari bab menu References dan remove dulu bab 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 .

Semoga bermanfaat, happy coding :)

Wassalamu'alaikum wr. wb.