Cara Membuat CRUD VB NET Dengan Database MySql

Langkah – Langkah Membuat CRUD VB NET Dengan MySql

• Buat database “db_latihan” pada MySql
• Buat tabel “tb_mahasiswa” atau bisa langsung mengeksekusi query di bawah ini

-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Waktu pembuatan: 11 Feb 2022 pada 17.59
-- Versi server: 10.4.19-MariaDB
-- Versi PHP: 8.0.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `db_latihan`
--

-- --------------------------------------------------------

--
-- Struktur dari tabel `tb_mahasiswa`
--

CREATE TABLE `tb_mahasiswa` (
  `nim` varchar(20) NOT NULL,
  `nama` varchar(100) NOT NULL,
  `kelas` varchar(10) NOT NULL,
  `alamat` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data untuk tabel `tb_mahasiswa`
--

INSERT INTO `tb_mahasiswa` (`nim`, `nama`, `kelas`, `alamat`) VALUES
('nim001', 'andi aryadi', '10A', 'Cilegon'),
('nim002', 'budi', '10b', 'serang'),
('nim003', 'cici', '10c', 'serang');

--
-- Indexes for dumped tables
--

--
-- Indeks untuk tabel `tb_mahasiswa`
--
ALTER TABLE `tb_mahasiswa`
  ADD PRIMARY KEY (`nim`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

• Kemudian buat project VB.NET dengan cara New Project>VB.Net Application form>nama project(bebas)>create. Untuk penamaan project bebas dan lokasi project bisa anda sesuaikan sendiri.
• Install Mysql Connector pada project yang anda buat dengan cara buka Tools>Nuget Package Manager>Manage Nuget Package For Solution
• Buat tampilan di VB.Net seperti gambar dibawah ini. atau code fmMahasiswa.Designer.vb dibawah ini :

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()>
Partial Class fmMahasiswa
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()>
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()>
    Private Sub InitializeComponent()
        Me.DataGridView1 = New System.Windows.Forms.DataGridView()
        Me.btnSimpan = New System.Windows.Forms.Button()
        Me.btnEdit = New System.Windows.Forms.Button()
        Me.btnHapus = New System.Windows.Forms.Button()
        Me.btnBatal = New System.Windows.Forms.Button()
        Me.Label1 = New System.Windows.Forms.Label()
        Me.txtNim = New System.Windows.Forms.TextBox()
        Me.txtNama = New System.Windows.Forms.TextBox()
        Me.Label2 = New System.Windows.Forms.Label()
        Me.txtKelas = New System.Windows.Forms.TextBox()
        Me.Label3 = New System.Windows.Forms.Label()
        Me.txtAlamat = New System.Windows.Forms.TextBox()
        Me.Label4 = New System.Windows.Forms.Label()
        Me.btnTambah = New System.Windows.Forms.Button()
        Me.txtCari = New System.Windows.Forms.TextBox()
        Me.Label5 = New System.Windows.Forms.Label()
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGridView1
        '
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.Location = New System.Drawing.Point(12, 215)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.RowTemplate.Height = 25
        Me.DataGridView1.Size = New System.Drawing.Size(776, 258)
        Me.DataGridView1.TabIndex = 0
        '
        'btnSimpan
        '
        Me.btnSimpan.Location = New System.Drawing.Point(111, 186)
        Me.btnSimpan.Name = "btnSimpan"
        Me.btnSimpan.Size = New System.Drawing.Size(75, 23)
        Me.btnSimpan.TabIndex = 1
        Me.btnSimpan.Text = "Simpan"
        Me.btnSimpan.UseVisualStyleBackColor = True
        '
        'btnEdit
        '
        Me.btnEdit.Location = New System.Drawing.Point(192, 186)
        Me.btnEdit.Name = "btnEdit"
        Me.btnEdit.Size = New System.Drawing.Size(75, 23)
        Me.btnEdit.TabIndex = 2
        Me.btnEdit.Text = "Edit"
        Me.btnEdit.UseVisualStyleBackColor = True
        '
        'btnHapus
        '
        Me.btnHapus.Location = New System.Drawing.Point(273, 186)
        Me.btnHapus.Name = "btnHapus"
        Me.btnHapus.Size = New System.Drawing.Size(75, 23)
        Me.btnHapus.TabIndex = 3
        Me.btnHapus.Text = "Hapus"
        Me.btnHapus.UseVisualStyleBackColor = True
        '
        'btnBatal
        '
        Me.btnBatal.Location = New System.Drawing.Point(354, 186)
        Me.btnBatal.Name = "btnBatal"
        Me.btnBatal.Size = New System.Drawing.Size(75, 23)
        Me.btnBatal.TabIndex = 4
        Me.btnBatal.Text = "Batal"
        Me.btnBatal.UseVisualStyleBackColor = True
        '
        'Label1
        '
        Me.Label1.AutoSize = True
        Me.Label1.Location = New System.Drawing.Point(30, 43)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(30, 15)
        Me.Label1.TabIndex = 5
        Me.Label1.Text = "NIM"
        '
        'txtNim
        '
        Me.txtNim.Location = New System.Drawing.Point(105, 43)
        Me.txtNim.Name = "txtNim"
        Me.txtNim.Size = New System.Drawing.Size(172, 23)
        Me.txtNim.TabIndex = 6
        '
        'txtNama
        '
        Me.txtNama.Location = New System.Drawing.Point(105, 72)
        Me.txtNama.Name = "txtNama"
        Me.txtNama.Size = New System.Drawing.Size(172, 23)
        Me.txtNama.TabIndex = 8
        '
        'Label2
        '
        Me.Label2.AutoSize = True
        Me.Label2.Location = New System.Drawing.Point(30, 72)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(43, 15)
        Me.Label2.TabIndex = 7
        Me.Label2.Text = "NAMA"
        '
        'txtKelas
        '
        Me.txtKelas.Location = New System.Drawing.Point(105, 101)
        Me.txtKelas.Name = "txtKelas"
        Me.txtKelas.Size = New System.Drawing.Size(172, 23)
        Me.txtKelas.TabIndex = 10
        '
        'Label3
        '
        Me.Label3.AutoSize = True
        Me.Label3.Location = New System.Drawing.Point(30, 101)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(40, 15)
        Me.Label3.TabIndex = 9
        Me.Label3.Text = "KELAS"
        '
        'txtAlamat
        '
        Me.txtAlamat.Location = New System.Drawing.Point(105, 130)
        Me.txtAlamat.Name = "txtAlamat"
        Me.txtAlamat.Size = New System.Drawing.Size(172, 23)
        Me.txtAlamat.TabIndex = 12
        '
        'Label4
        '
        Me.Label4.AutoSize = True
        Me.Label4.Location = New System.Drawing.Point(30, 130)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(53, 15)
        Me.Label4.TabIndex = 11
        Me.Label4.Text = "ALAMAT"
        '
        'btnTambah
        '
        Me.btnTambah.Location = New System.Drawing.Point(30, 186)
        Me.btnTambah.Name = "btnTambah"
        Me.btnTambah.Size = New System.Drawing.Size(75, 23)
        Me.btnTambah.TabIndex = 13
        Me.btnTambah.Text = "Tambah"
        Me.btnTambah.UseVisualStyleBackColor = True
        '
        'txtCari
        '
        Me.txtCari.Location = New System.Drawing.Point(616, 43)
        Me.txtCari.Name = "txtCari"
        Me.txtCari.Size = New System.Drawing.Size(172, 23)
        Me.txtCari.TabIndex = 14
        '
        'Label5
        '
        Me.Label5.AutoSize = True
        Me.Label5.Location = New System.Drawing.Point(577, 43)
        Me.Label5.Name = "Label5"
        Me.Label5.Size = New System.Drawing.Size(33, 15)
        Me.Label5.TabIndex = 15
        Me.Label5.Text = "CARI"
        '
        'fmMahasiswa
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(7.0!, 15.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(800, 489)
        Me.Controls.Add(Me.Label5)
        Me.Controls.Add(Me.txtCari)
        Me.Controls.Add(Me.btnTambah)
        Me.Controls.Add(Me.txtAlamat)
        Me.Controls.Add(Me.Label4)
        Me.Controls.Add(Me.txtKelas)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.txtNama)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.txtNim)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.btnBatal)
        Me.Controls.Add(Me.btnHapus)
        Me.Controls.Add(Me.btnEdit)
        Me.Controls.Add(Me.btnSimpan)
        Me.Controls.Add(Me.DataGridView1)
        Me.Name = "fmMahasiswa"
        Me.Text = "Form Mahasiswa"
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub

    Friend WithEvents DataGridView1 As DataGridView
    Friend WithEvents btnSimpan As Button
    Friend WithEvents btnEdit As Button
    Friend WithEvents btnHapus As Button
    Friend WithEvents btnBatal As Button
    Friend WithEvents Label1 As Label
    Friend WithEvents txtNim As TextBox
    Friend WithEvents txtNama As TextBox
    Friend WithEvents Label2 As Label
    Friend WithEvents txtKelas As TextBox
    Friend WithEvents Label3 As Label
    Friend WithEvents txtAlamat As TextBox
    Friend WithEvents Label4 As Label
    Friend WithEvents btnTambah As Button
    Friend WithEvents txtCari As TextBox
    Friend WithEvents Label5 As Label
End Class

• Setelah design form terbuat kemudian buatlah folder “module” pada project vb.net, Kemudian buatlah modul dalam folder yang sudah kita buat tadi dan berinama Koneksi.vb. Kemudian isilah Module koneksi dengan ketikan source code di bawah ini :

Imports MySql.Data.MySqlClient

Module Koneksi
    Dim conn As New MySqlConnection("Server=localhost; user=root; password=; database=db_latihan")
    Public MySQLReader As MySqlDataReader
    Public CMD As New MySqlCommand
    Public DA As New MySqlDataAdapter

    Public Function buka() As MySqlConnection
        conn.Open()
        Return conn
    End Function
    Public Function tutup() As MySqlConnection
        conn.Close()
        Return conn
    End Function

End Module

• Setelah module koneksi terbuat klik 2 kali pada “fmMahasiswa” yang sudah kita buat tadi dan ketikan source code dibahwah ini :

Imports MySql.Data.MySqlClient


Public Class fmMahasiswa

    Dim cmd As New MySqlCommand
    Dim da As New MySqlDataAdapter
    Dim ds As New DataSet
    Dim flag As Boolean


    Private Sub fmMahasiswa_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call Tampil()
    End Sub

    Private Sub Tampil()

        Dim dt As DataTable
        Dim adapter As MySqlDataAdapter
        Dim sqlstr As String

        KontrolTombol(True)

        sqlstr = "SELECT * FROM tb_mahasiswa"
        adapter = New MySqlDataAdapter(sqlstr, Koneksi.buka)
        dt = New DataTable

        If adapter.Fill(dt) > 0 Then
            DataGridView1.DataSource = dt
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.Fill
            DataGridView1.Columns(0).HeaderText = "NIM"
            DataGridView1.Columns(1).HeaderText = "NAMA"
            DataGridView1.Columns(2).HeaderText = "KELAS"
            DataGridView1.Columns(3).HeaderText = "ALAMAT"
            Koneksi.tutup()
        Else
            DataGridView1.DataSource = Nothing
            Koneksi.tutup()
        End If
        HapusTeks()
        Me.CenterToScreen()
        GC.Collect()
    End Sub

    Private Sub HapusTeks()
        txtNim.Text = ""
        txtNama.Text = ""
        txtKelas.Text = ""
        txtAlamat.Text = ""
    End Sub

    Private Sub KontrolTombol(ByVal aktif As Boolean)
        btnTambah.Enabled = aktif
        btnSimpan.Enabled = Not aktif
        btnEdit.Enabled = aktif
        btnHapus.Enabled = aktif
        btnBatal.Enabled = Not aktif

        txtNim.Enabled = Not aktif
        txtNama.Enabled = Not aktif
        txtKelas.Enabled = Not aktif
        txtAlamat.Enabled = Not aktif
        txtNim.Select()
    End Sub

    Private Sub btnTambah_Click(sender As Object, e As EventArgs) Handles btnTambah.Click
        Call KontrolTombol(False)
        Call HapusTeks()
        flag = True
    End Sub

    Private Sub btnBatal_Click(sender As Object, e As EventArgs) Handles btnBatal.Click
        Call KontrolTombol(True)
    End Sub


    Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Dim i As Integer
        i = Me.DataGridView1.CurrentRow.Index
        With DataGridView1.Rows.Item(i)
            Me.txtNim.Text = .Cells(0).Value
            Me.txtNama.Text = .Cells(1).Value
            Me.txtKelas.Text = .Cells(2).Value
            Me.txtAlamat.Text = .Cells(3).Value
        End With
    End Sub

    Private Sub btnSimpan_Click(sender As Object, e As EventArgs) Handles btnSimpan.Click
        If flag = True Then
            Try
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "INSERT INTO tb_mahasiswa (nim, nama, kelas, alamat) VALUES ('" & txtNim.Text & "', '" & txtNama.Text & "', '" & txtKelas.Text & "', '" & txtAlamat.Text & "')"
                cmd.Connection = Koneksi.buka()
                cmd.ExecuteNonQuery()
                MsgBox("Data berhasil disimpan", MsgBoxStyle.Information, "Informasi")
            Catch ex As Exception
                MsgBox("Data gagal disimpan" + ex.Message, MsgBoxStyle.Critical)
            End Try
        Else
            Try
                Dim cmd As New MySql.Data.MySqlClient.MySqlCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "UPDATE tb_mahasiswa SET nama = '" & txtNama.Text & "' , kelas = '" & txtKelas.Text & "' , alamat = '" & txtAlamat.Text & "' WHERE nim = '" & txtNim.Text & "'"
                cmd.Connection = Koneksi.buka()
                cmd.ExecuteNonQuery()
                MySQLReader = cmd.ExecuteReader
                MsgBox("Data berhasil diubah", MsgBoxStyle.Information, "Informasi")
            Catch ex As Exception
                MsgBox("Data gagal diubah" + ex.Message, MsgBoxStyle.Critical)
            End Try
        End If
        Koneksi.tutup()
        Call Tampil()
    End Sub

    Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        If txtNim.Text <> "" Then
            flag = False
            KontrolTombol(False)
            txtNama.Select()
        Else
            MessageBox.Show("Please select value nim", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

    End Sub

    Private Sub btnHapus_Click(sender As Object, e As EventArgs) Handles btnHapus.Click
        Dim hasil As MsgBoxResult = MessageBox.Show("Apakah data ingin dihapus?", "Pesan", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
        If hasil = vbOK Then
            cmd.Connection = Koneksi.buka
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "DELETE FROM tb_mahasiswa WHERE nim = '" & txtNim.Text & "'"
            cmd.ExecuteNonQuery()
            Koneksi.tutup()
            Call Tampil()
        End If
    End Sub

    Private Sub txtCari_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtCari.KeyPress
        Dim dtable As DataTable
        Dim adapter As MySqlDataAdapter
        Dim sqlstr As String
        Dim i As Int16

        sqlstr = "SELECT * FROM tb_mahasiswa WHERE nim LIKE '%" & txtCari.Text & "%' or nama LIKE '%" & txtCari.Text & "%'"
        adapter = New MySqlDataAdapter(sqlstr, Koneksi.buka)
        dtable = New DataTable
        i = adapter.Fill(dtable)

        If i > 0 Then
            DataGridView1.DataSource = dtable
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.Fill
            DataGridView1.Columns(0).HeaderText = "NIM"
            DataGridView1.Columns(1).HeaderText = "NAMA"
            DataGridView1.Columns(2).HeaderText = "KELAS"
            DataGridView1.Columns(3).HeaderText = "ALAMAT"
        Else
            DataGridView1.DataSource = Nothing
            MsgBox("Data tidak ditemukan!", MsgBoxStyle.Information, "Informasi")
        End If
        Koneksi.tutup()
    End Sub
End Class

• Setelah mengetik source code di atas kemudian coba jalankan program dengan cara menekan tombol (F5) dan berikut hasil nya :