VB.NET Insert Update Delete with MySQL Database Metro UI Theme + Source Code

VB.NET for beginners - How to create simple CRUD application (Create,Read,Update,Delete) in vb.net and using MySQL database ODBC connection with Metro Framework UI theme design? this tutorial has been create before, Please read CRUD Tutorial with MySQL Database, but today i'll make a new version and change the default theme to a simple interface with metro framework theme.

Read : How to Change default windows form with Metro Framework Theme
How to Create MySQL Database
How to Make A connection Using ODBC Driver

we will create a simple CRUD operations step by step, so you can easy to follow how to make this simple.

Create a New Project

Just create a new project with a name "CRUDmetro", and lets design Form1.vb look like this

VB.NET Insert Update Delete with MySQL Database

Just leave the Form1.vb for a moment, we will create new module for our connections.

Source Code Module Connections

So lets create new module and rename it with "ModuleConnections.vb" and write all source code above.

Imports System.Data.Odbc ' import namespace of ODBC
Module ModuleConnections
    Public connections As OdbcConnection ' declarations for our connections
    Sub OpenConnection()
        Try
            ' you must hasbeen install MySQL ODBC driver on your computer
            ' then create connection to our database (MySQL)
            ' just see on the descriptions how to make a connections using ODBC Driver
            connections = New OdbcConnection("DSN=delta_db;MultipleActiveResultSets=True")
            If connections.State = ConnectionState.Closed Then
                connections.Open() ' open the connections
            End If
        Catch ex As Exception
            MsgBox("Connection Failed !") ' if connection is failed
        End Try
    End Sub
End Module

If you has been done with our module for connections to a database, back to Form1.vb and lets complete all the code.

With form1.vb, first please import name space of ODBC driver.

Imports System.Data.Odbc ' import namespace of ODBC

and declarations our variable for a new data or old data, so this variable will be show when we will create a new data and when we update the old data. i hope you understand, just write the variable above the class form1.vb

Dim NewData As Boolean ' declarations New data for save new data, or update the data

Source Code Sub Load Data

load data will biand all data from database to a GridViews, just write this code

    Sub LoadData()
        ' its for load all data into gridviews
        OpenConnection() ' open our connection
        Dim da As New OdbcDataAdapter("SELECT * FROM karyawan", connections)
        Dim ds As New DataSet
        ds.Clear()
        da.Fill(ds, "karyawan")
        MetroGrid1.DataSource = ds.Tables("karyawan") ' set datasource of our gridview
        da.Dispose()
        connections.Close()
    End Sub

Source Code Sub SenToTextBox

Its will change the textbox value with any value from datagridview

    Sub senToTextBox(ByVal x As Integer)
        'its for get value from datagridview into textbox        
        Try
            MetroTextBox1.Text = MetroGrid1.Rows(x).Cells(0).Value
            MetroTextBox2.Text = MetroGrid1.Rows(x).Cells(1).Value
            MetroTextBox3.Text = MetroGrid1.Rows(x).Cells(2).Value
            MetroTextBox4.Text = MetroGrid1.Rows(x).Cells(3).Value
            MetroTextBox5.Text = MetroGrid1.Rows(x).Cells(4).Value
            MetroTextBox6.Text = MetroGrid1.Rows(x).Cells(5).Value
        Catch ex As Exception
        End Try
    End Sub

Source Code Sub Clear The TextBox

    Sub ClearTextBox()
        MetroTextBox1.Text = "" ' its for clear the textbox
        MetroTextBox2.Text = ""
        MetroTextBox3.Text = ""
        MetroTextBox4.Text = ""
        MetroTextBox5.Text = ""
        MetroTextBox6.Text = ""
        MetroTextBox1.Focus()
    End Sub

Source Code Setting Width of The DataGridView Columns

    Sub SettingGrid()
        MetroGrid1.Columns(0).Width = 40 ' setting width of our column
        ' you can setting more of your datagrid columns
    End Sub

Source Code Functions To Save or Update Or Delete data

    Private Sub RunSQL(ByVal sql As String) ' create functions for save or update data
        Dim cmd As New OdbcCommand
        OpenConnection() ' open our connection
        Try
            cmd.Connection = connections
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            connections.Close()
            MsgBox("Data Hasbeen saved !", vbInformation)
        Catch ex As Exception
            MsgBox("Error when saving data !" & ex.Message)
        End Try
    End Sub

Source Code Form Load

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadData()
        SettingGrid()
        NewData = True ' its mean we will create a new data with query "INSERT"
        ' if new data = false, its mean we make a query "UPDATE"
    End Sub

Source Code New Button (Button1)

    Private Sub MetroButton1_Click(sender As Object, e As EventArgs) Handles MetroButton1.Click
        NewData = True
        ClearTextBox()
    End Sub

Source Code Save Button (Button2)

    Private Sub MetroButton2_Click(sender As Object, e As EventArgs) Handles MetroButton2.Click
        Dim savaedata As String
        Dim massege As String
        If NewData Then
            massege = MsgBox("Are you sure to add new data to database?", vbYesNo + vbInformation, "Information")
            If massege = vbNo Then
                Exit Sub
            End If
            savaedata = "INSERT INTO karyawan(id_karyawan,nama_karyawan,nik,jabatan,no_telphone,alamat) " _
            + "Values('" & MetroTextBox1.Text & "','" & MetroTextBox2.Text & "','" & MetroTextBox3.Text & "','" & MetroTextBox4.Text & "','" & MetroTextBox5.Text & "','" & MetroTextBox6.Text & "')"
        Else
            massege = MsgBox("Are you sure to UPDATE data to database?", vbYesNo + vbInformation, "Information")
            If massege = vbNo Then
                Exit Sub
            End If
            savaedata = "UPDATE karyawan SET nama_karyawan='" & MetroTextBox2.Text & "' " _
            + ",nik='" & MetroTextBox3.Text & "' " _
            + ",jabatan='" & MetroTextBox4.Text & "' " _
            + ",no_telphone='" & MetroTextBox5.Text & "' " _
            + ",alamat='" & MetroTextBox6.Text & "' where id_karyawan='" & MetroTextBox1.Text & "'"
        End If
        RunSQL(savaedata)
        MetroGrid1.Refresh()
        LoadData() ' insert data again after you update the data
    End Sub

Source Code Delete Button (Button3)

    Private Sub MetroButton3_Click(sender As Object, e As EventArgs) Handles MetroButton3.Click
        Dim delete As String
        Dim message As String
        message = MsgBox("Are you sure want to delete this data?  ", vbExclamation + vbYesNo, "warning")
        If message = vbNo Then Exit Sub
        delete = "DELETE from karyawan where id_karyawan='" & MetroTextBox1.Text & "'"
        RunSQL(delete) ' delet functions
        MetroGrid1.Refresh()
        LoadData() ' insert data again after you update the data
    End Sub

Source Code Exit Button (Button4)

    Private Sub MetroButton4_Click(sender As Object, e As EventArgs) Handles MetroButton4.Click
        Me.Close()
    End Sub

Source Code DataGridView Cell Click Event

    Private Sub MetroGrid1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles MetroGrid1.CellClick
        senToTextBox(e.RowIndex) ' send value to textbox
        NewData = False
    End Sub

So we have done with simple project CRUD Operations with MySQL database and Metro Framework UI Themes, But wait.. i'll show you all complete the Source Code Form1.vb, just view and write all complete code bellow.

Imports System.Data.Odbc
Public Class Form1
    Dim NewData As Boolean ' declarations New data for save new data, or update the data
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadData()
        SettingGrid()
        NewData = True ' its mean we will create a new data with query "INSERT"
        ' if new data = false, its mean we make a query "UPDATE"
    End Sub
    Sub LoadData()
        ' its for load all data into gridviews
        OpenConnection() ' open our connection
        Dim da As New OdbcDataAdapter("SELECT * FROM karyawan", connections)
        Dim ds As New DataSet
        ds.Clear()
        da.Fill(ds, "karyawan")
        MetroGrid1.DataSource = ds.Tables("karyawan") ' set datasource of our gridview
        da.Dispose()
        connections.Close()
    End Sub
    Sub senToTextBox(ByVal x As Integer)
        'its for get value from datagridview into textbox        
        Try
            MetroTextBox1.Text = MetroGrid1.Rows(x).Cells(0).Value
            MetroTextBox2.Text = MetroGrid1.Rows(x).Cells(1).Value
            MetroTextBox3.Text = MetroGrid1.Rows(x).Cells(2).Value
            MetroTextBox4.Text = MetroGrid1.Rows(x).Cells(3).Value
            MetroTextBox5.Text = MetroGrid1.Rows(x).Cells(4).Value
            MetroTextBox6.Text = MetroGrid1.Rows(x).Cells(5).Value
        Catch ex As Exception
        End Try
    End Sub
    Private Sub MetroGrid1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles MetroGrid1.CellClick
        senToTextBox(e.RowIndex) ' send value to textbox
        NewData = False
    End Sub
    Sub SettingGrid()
        MetroGrid1.Columns(0).Width = 40 ' setting width of our column
    End Sub
    Sub ClearTextBox()
        MetroTextBox1.Text = "" ' its for clear the textbox
        MetroTextBox2.Text = ""
        MetroTextBox3.Text = ""
        MetroTextBox4.Text = ""
        MetroTextBox5.Text = ""
        MetroTextBox6.Text = ""
        MetroTextBox1.Focus()
    End Sub
    Private Sub MetroButton1_Click(sender As Object, e As EventArgs) Handles MetroButton1.Click
        NewData = True
        ClearTextBox()
    End Sub
    Private Sub RunSQL(ByVal sql As String) ' create functions for save or update data
        Dim cmd As New OdbcCommand
        OpenConnection() ' open our connection
        Try
            cmd.Connection = connections
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            connections.Close()
            MsgBox("Data Hasbeen saved !", vbInformation)
        Catch ex As Exception
            MsgBox("Error when saving data !" & ex.Message)
        End Try
    End Sub
    Private Sub MetroButton2_Click(sender As Object, e As EventArgs) Handles MetroButton2.Click
        Dim savaedata As String
        Dim massege As String
        If NewData Then
            massege = MsgBox("Are you sure to add new data to database?", vbYesNo + vbInformation, "Information")
            If massege = vbNo Then
                Exit Sub
            End If
            savaedata = "INSERT INTO karyawan(id_karyawan,nama_karyawan,nik,jabatan,no_telphone,alamat) " _
            + "Values('" & MetroTextBox1.Text & "','" & MetroTextBox2.Text & "','" & MetroTextBox3.Text & "','" & MetroTextBox4.Text & "','" & MetroTextBox5.Text & "','" & MetroTextBox6.Text & "')"
        Else
            massege = MsgBox("Are you sure to UPDATE data to database?", vbYesNo + vbInformation, "Information")
            If massege = vbNo Then
                Exit Sub
            End If
            savaedata = "UPDATE karyawan SET nama_karyawan='" & MetroTextBox2.Text & "' " _
            + ",nik='" & MetroTextBox3.Text & "' " _
            + ",jabatan='" & MetroTextBox4.Text & "' " _
            + ",no_telphone='" & MetroTextBox5.Text & "' " _
            + ",alamat='" & MetroTextBox6.Text & "' where id_karyawan='" & MetroTextBox1.Text & "'"
        End If
        RunSQL(savaedata)
        MetroGrid1.Refresh()
        LoadData() ' insert data again after you update the data
    End Sub
    Private Sub MetroButton3_Click(sender As Object, e As EventArgs) Handles MetroButton3.Click
        Dim delete As String
        Dim message As String
        message = MsgBox("Are you sure want to delete this data?  ", vbExclamation + vbYesNo, "warning")
        If message = vbNo Then Exit Sub
        delete = "DELETE from karyawan where id_karyawan='" & MetroTextBox1.Text & "'"
        RunSQL(delete) ' delet functions
        MetroGrid1.Refresh()
        LoadData() ' insert data again after you update the data
    End Sub
    Private Sub MetroButton4_Click(sender As Object, e As EventArgs) Handles MetroButton4.Click
        Me.Close()
    End Sub
End Class

If you are still confused by the tutorial above, just follow step by step this video, i have create a video CRUD tutorial just for you, hope you enjoyed.

Video Tutorial CRUD Operations with MySQL Database (English Version)

Download Source Code Project CRUD with MySQL Database
Download MySQL Database VB.NET CRUD

Please ask any questions in the comments box below, I would be very glad if can help you and don't forget to follow us in Social media. thanks before.