Tutorial VB.NET : CRUD (Create, Update, Delete) Vb.Net Database MySQL

Aplikasi CRUD (Create, Update, Delete) Vb.Net Database MySQL

Tutorial Sector Code kali ini membuat aplikasi CRUD sederhana menggunakan database MySQL di Visual Basic Net 2010, sebelumnya kita harus membuat database di PhpMyadmin terlebih dahulu, baca Tutorial Lengkap Cara Membuat Database MySQL di PhpMyAdmin. dan untuk tutorial ini penulis membuat database seperti gambar berikut :

Tutorial VB.NET : CRUD (Create, Update, Delete) Vb.Net Database MySQL

Buatlah Project baru dengan Nama CRUD, buat Form baru dengan nama FrmUtama.vb, buat design FrmUtama seperti gambar berikut :

Buatlah Module koneksi ke database yang sudah anda buat, baca Tutorial Vb.Net : Koneksi Database MySQL, Buatlah Module baru dengan nama MdlKoneksi, dan pastekan Code berikut :

Imports System.Data
Imports System.Data.Odbc
Imports System.Data.SqlClient
Imports MySql.Data.MySqlClient
Imports System.Data.OleDb
Module MdlKoneksi
    Public konek As OdbcConnection
    Public DA As OdbcDataAdapter
    Public DR As OdbcDataReader
    Public DS As DataSet
    Public CMD As OdbcCommand
    Sub bukaDB()
        Try
            konek = New OdbcConnection("DSN=crud;MultipleActiveResultSets=True")
            If konek.State = ConnectionState.Closed Then
                konek.Open()
            End If
        Catch ex As Exception
            MsgBox("konek Kedatabase Bermasalah, Periksa koneksi Jaringan Anda")
        End Try
    End Sub
End Module

Kode untuk tombol Tambah

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DATABARU = True
        bersih()
    End Sub

Code Untuk Tombol Simpan

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim simpan As String
        Dim vTanggalLahir As String
        Dim vTgl As String
        Dim vBln As String
        Dim vThn As String
        Dim pesan As Integer
        vTgl = DateTimePicker1.Value.Day
        vBln = DateTimePicker1.Value.Month
        vThn = DateTimePicker1.Value.Year
        vTanggalLahir = vThn & "-" & vBln & "-" & vTgl
        If TextBox2.Text = "" Or TextBox3.Text = "" Then Exit Sub
        If DATABARU Then
            pesan = MsgBox("Apakah Anda Yakin Data Akan ditambahkan ke Database ?", vbYesNo + vbInformation, "Perhatian")
            If pesan = vbYesNo Then
                Exit Sub
            End If
            simpan = "INSERT INTO biodata(nama,nis,nisn,jeniskelamin,tempatlahir,tanggallahir,agama,alamat,sekolahasal) VALUES ('" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "'," _
                + " '" & TextBox5.Text & "','" & TextBox6.Text & "','" & vTanggalLahir & "','" & TextBox7.Text & "','" & TextBox8.Text & "','" & TextBox9.Text & "') "
        Else
            pesan = MsgBox("Anda yakin data ini akan di update ?", vbYesNo + vbInformation, "Perhatian")
            If pesan = vbYesNo Then
                Exit Sub
            End If
            simpan = "UPDATE biodata SET " _
                + "nama = '" & TextBox2.Text & "'," _
                + "nis ='" & TextBox3.Text & "'," _
                + "nisn ='" & TextBox4.Text & "'," _
                + "jeniskelamin ='" & TextBox5.Text & "'," _
                + "tempatlahir ='" & TextBox6.Text & "'," _
                + "tanggallahir ='" & vTanggalLahir & "'," _
                + "agama ='" & TextBox7.Text & "'," _
                + "alamat ='" & TextBox8.Text & "'," _
                + "sekolahasal ='" & TextBox9.Text & "'," _
                + "alamatsekolahasal ='" & TextBox9.Text & "'  WHERE idsiswa = '" & TextBox1.Text & "' "
        End If
        jalankansql(simpan)
        Me.Cursor = Cursors.WaitCursor
        DataGridView1.Refresh()
        isigrid()
        Me.Cursor = Cursors.Default
    End Sub

Code Untuk Tombol Hapus

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim hapussql As String
        Dim pesan As Integer
        pesan = MsgBox("Apakah anda yakin akan menghapus Data pada server .. " + TextBox2.Text, vbExclamation + vbYesNo, "perhatian")
        If pesan = vbNo Then Exit Sub
        hapussql = "DELETE FROM biodata WHERE idsiswa='" & TextBox1.Text & "'"
        jalankansql(hapussql)
        Me.Cursor = Cursors.WaitCursor
        DataGridView1.Refresh()
        isigrid()
        Me.Cursor = Cursors.Default
    End Sub

Code untuk Tombol Keluar

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Me.Close()
    End Sub

Code Functions Simpan

    Private Sub jalankansql(ByVal sQl As String)
        Dim objcmd As New System.Data.Odbc.OdbcCommand
        Call bukaDB()
        Try
            objcmd.Connection = konek
            objcmd.CommandType = CommandType.Text
            objcmd.CommandText = sQl
            objcmd.ExecuteNonQuery()
            objcmd.Dispose()
            MsgBox("Data Sudah Disimpan", vbInformation)
        Catch ex As Exception
            MsgBox("Tidak Bisa Menyimpan data ke Server" & ex.Message)
        End Try
    End Sub

Code Sub Isidata Ke GridView

    Sub isigrid()
        bukaDB()
        DA = New Odbc.OdbcDataAdapter("SELECT * FROM biodata", konek)
        DS = New DataSet
        DS.Clear()
        DA.Fill(DS, "biodata")
        DataGridView1.DataSource = (DS.Tables("biodata"))
        DataGridView1.Enabled = True
    End Sub

Code Isi Data Dari Gridview Ke TextBox

 Private Sub isiTextBox(ByVal x As Integer)
        Try
            TextBox1.Text = DataGridView1.Rows(x).Cells(0).Value
            TextBox2.Text = DataGridView1.Rows(x).Cells(1).Value
            TextBox3.Text = DataGridView1.Rows(x).Cells(2).Value
            TextBox4.Text = DataGridView1.Rows(x).Cells(3).Value
            TextBox5.Text = DataGridView1.Rows(x).Cells(4).Value
            TextBox6.Text = DataGridView1.Rows(x).Cells(5).Value
            DateTimePicker1.Value = IIf(IsDBNull(DataGridView1.Rows(x).Cells(6).Value), Date.Now,
                                        DataGridView1.Rows(x).Cells(6).Value)
            TextBox7.Text = DataGridView1.Rows(x).Cells(7).Value
            TextBox8.Text = DataGridView1.Rows(x).Cells(8).Value
            TextBox9.Text = DataGridView1.Rows(x).Cells(9).Value
        Catch ex As Exception
        End Try
    End Sub
    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        isiTextBox(e.RowIndex)
        DATABARU = False
    End Sub

Code untuk Frm Load

    Dim DATABARU As Boolean
    Private Sub FrmUtama_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DATABARU = False
        isigrid()
    End Sub

Jika semuanya sudah selesai, silahkan coba aplikasi CRUD anda, gini penampakannya :

Video tutorial CRUD Applikasi Vb.Net bisa anda lihat di sini :

Video Tutorial : CRUD (Create, Update, Delete) Vb.Net
[Video] Tutorial CRUD Vb.NET menggunakan Database SQL Server
atau selengkapnya di halaman video tutorial

[Tutorial Link UPDATED on 21 Mei 2016] How to Create CRUD Operations with MySQL Database and Metro Framework UI Theme Version 2, download full source code English version. Aplikasi CRUD rilis versi 2 bisa kamu temukan pada link diatas, dilengkapi dengan source code yang bisa di download dan video tutorial. semoga bermanfaat.

Subscribe Channel Youtube kami untuk mendapatkan Tutorial terbaru :
GPlus : https://plus.google.com/+HarisonMatondang
Youtube : http://www.youtube.com/c/HarisonMatondang
Untuk menambah Functions Search Data dan membuat laporannya silahkan baca pada tutorial Sector Code Sebelumnya :
Tutorial Vb.Net : Fungsi Search / Cari Data di DataGridView dan Membuat Laporan Biodata dengan Crystal Report Vb.Net,

[Download Link UPDATED on 21 Mei 2016] Anda juga bisa mendownload aplikasi Crud Sederhana ini, silahkan Download Aplikasi Crud Vb.Net

Jangan lupa di Like dan Share jika bermanfaat, Terima kasih ;)