VB.NET Insert, Update, Delete Multiple Tables Using MySQL Database

VB.NET tutorials for Beginners : How to Insert, Update, Delete (CRUD Operations) Multi Tables using MySQL Database in Visual Studio.

C# CRUD Operations Insert,Update,Delete with MySQL Database

VB.NET How to Export DataGridView to PDF Using DataTable MySQL Database

VB.NET Chart Example With Values From MySQL Database + Source Code

VB.NET for Beginners - How to make a simple applications CRUD (Create, Read,Update,Delete) Operations using MySQL Database and ODBC Class in Visual studio 2015? at the previews tutorials, We've been discussing about :

1. How to CRUD operations with MySQL database (Not Multi Tables)
2. How to create, Read, Update, Delete Operations using SQL Server
3. VB.NET CRUD tutorials using Ms Access
4. Create, Read, Update, Delete using MySQL database Versions 1

In this tutorial, we will create an simple CRUD application where we using two tables, so, any operations will be automatically updating two tables. You can also add more tables to the next operation.

Make a New Project (CRUD operations Multi Tables)

Open your Visual Studio 2015 and make a new project call "CRUDMultiTables" and at the Form1.vb please design look like this one :

Make a Module Connections (ModuleConnection.Vb)

leave Form1.vb just yet and make a new module, rename it with "ModuleConnection.vb". we will create a connection to MySQL database using ODBC class. just write this source code to our Module.

Source Code ModuleConnections

Imports System.Data.Odbc ' Import ODBC class
Module ModuleConnections
    ' we will create a connections from MySQL database using ODBC class
    ' you must have a database before (MySQL Database)
    Public connection As OdbcConnection
    Public Sub connect()
        Try
            connection = New OdbcConnection("DSN=java_db;MultipleActiveResultSets=True")
            If connection.State = ConnectionState.Closed Then
                connection.Open() ' Open connections
            End If
        Catch ex As Exception
            MsgBox("Connection Failed")
        End Try
    End Sub
End Module

Back to the Form1.Vb and import ODBC Class in Header programms, Imports System.Data.Odbc
Then declare the NewData to Boolean Type, write it above our class in Form1.vb Dim NewData As Boolean

Source Code Functions Save, Edite, Delete Data

' create functions for save or update and delete data
    Private Sub RUNSql(ByVal sql As String)
        Dim cmd As New OdbcCommand
        connect() ' open our connection
        Try
            cmd.Connection = connection
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            connection.Close()
            MsgBox("Data Hasbeen Saved !")
        Catch ex As Exception
            MsgBox("Error when saving data")
        End Try
    End Sub

Source Code Save Button (Button 1)

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        NewData = True
        clearText()
    End Sub

Source Code Save Button (Button 2)

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim SaveData, SaveUsers As String
        Dim message As String
        If NewData Then
            message = MsgBox("Are you sure to add New data into database? ", vbYesNo + vbInformation, "Informations")
            If message = vbNo Then
                Exit Sub
            End If
            ' insert new data statement into biodata tables
            SaveData = "INSERT INTO biodata(id,nama,nis,kelas,alamat) VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "')"

            ' insert new data statement into users tables
            SaveUsers = "INSERT INTO users(id_user,password,level) VALUES('" & TextBox1.Text & "',MD5('" & TextBox6.Text & "'),'" & ComboBox1.Text & "')"
        Else
            message = MsgBox("Are you sure to Update this Data into Database ", vbYesNo + vbInformation, "Informations")
            If message = vbNo Then
                Exit Sub
            End If

            ' to Update data into Biodata Tables
            SaveData = "update biodata SET nama='" & TextBox2.Text & "',nis='" & TextBox3.Text & "',kelas='" & TextBox4.Text & "',alamat='" & TextBox5.Text & "' where id='" & TextBox1.Text & "'"
            ' to Update data into users Tables
            SaveUsers = "update users SET password='" & TextBox6.Text & "',level='" & ComboBox1.Text & "' where id_user='" & TextBox1.Text & "'"

        End If
        RUNSql(SaveData) ' Insert or Update proccess into Biodata Tables
        RUNSql(SaveUsers) ' Insert or Update proccess into Users Tables
        DataGridView1.Refresh() ' refresh GridView biodata
        DataGridView2.Refresh() ' refresh GridView users
        DataGridView3.Refresh() ' refresh GridView Inner Join
        LoadDataFromBiodata() 'Fill data from Biodata Tables Into DataGridView1
        LoadDataFromUsers() 'Fill data from users Tables Into DataGridView2
        LoadDataFromJoin() ' Fill data from Biodata Tables and Users tables Into DataGridView3
    End Sub

Source Code Delete Button (Button 3)

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim deleteBiodata, DeleteUsers 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 data from Biodata Tables
        deleteBiodata = "Delete from biodata where id='" & TextBox1.Text & "'"

        ' delete data from users tables
        DeleteUsers = "delete from users where id_user='" & TextBox1.Text & "'"
        RUNSql(deleteBiodata) ' ' delete functions for biodata tables
        RUNSql(DeleteUsers) ' delete functions for users tables
        LoadDataFromBiodata() 'Fill data from Biodata Tables Into DataGridView1
        LoadDataFromUsers() 'Fill data from users Tables Into DataGridView2
        LoadDataFromJoin() ' Fill data from Biodata Tables and Users tables Into DataGridView3
        clearText()
    End Sub

Source Code Sub Clear TextBox

    Sub clearText()
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        ComboBox1.Text = "1"
        TextBox1.Focus()
    End Sub

Source Code Sub Send To TextBox

    Sub senToTextBox(ByVal x As Integer)
        'its for get value from datagridview into textbox        
        Try
            TextBox1.Text = DataGridView3.Rows(x).Cells(0).Value
            TextBox2.Text = DataGridView3.Rows(x).Cells(1).Value
            TextBox3.Text = DataGridView3.Rows(x).Cells(2).Value
            TextBox4.Text = DataGridView3.Rows(x).Cells(3).Value
            TextBox5.Text = DataGridView3.Rows(x).Cells(4).Value
            TextBox6.Text = DataGridView3.Rows(x).Cells(5).Value
            ComboBox1.Text = DataGridView3.Rows(x).Cells(6).Value
        Catch ex As Exception
        End Try
    End Sub

Source Code DataGridView 3 Event Click

    Private Sub DataGridView3_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView3.CellClick
        senToTextBox(e.RowIndex) ' send gridview values to textbox
        NewData = False
    End Sub

Source Code Load Data From Biodata Tables

    'functions to load fill data from Biodata Table into DataGridView1
    Sub LoadDataFromBiodata()
        connect() ' Open Connections
        Dim da As OdbcDataAdapter
        Dim dt As DataTable ' using datatable to fill data from database
        da = New OdbcDataAdapter("SELECT * FROM biodata", connection)
        dt = New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt ' set datasource from biodata tables into gridview1
        connection.Close()
        da.Dispose()
    End Sub

Load Data From Users Tables

    'functions to load fill data from Users Table into DataGridView2
    Sub LoadDataFromUsers()
        connect() ' Open Connections
        Dim da As OdbcDataAdapter
        Dim dt As DataTable ' using datatable to fill data from database
        da = New OdbcDataAdapter("SELECT * FROM users", connection)
        dt = New DataTable
        da.Fill(dt)
        DataGridView2.DataSource = dt ' set datasource from users tables into gridview2
        connection.Close()
        da.Dispose()
    End Sub

Source Code From Biodata Tables and Users Tables (Inner Join)

    'functions to load fill data from biodata tables and Users Table (Inner join) into DataGridView3
    Sub LoadDataFromJoin()
        connect() ' Open Connections
        Dim da As OdbcDataAdapter
        Dim dt As DataTable ' using datatable to fill data from database
        da = New OdbcDataAdapter("SELECT b.*,u.password,u.level FROM biodata as b INNER JOIN users as u ON (b.id=u.id_user)", connection)
        dt = New DataTable
        da.Fill(dt)
        DataGridView3.DataSource = dt ' set datasource from biodata tables and users tables into gridview3
        connection.Close()
        da.Dispose()
    End Sub

Source Code Form_Load

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NewData = True
        LoadDataFromBiodata() ' load data from Biodata Tables
        LoadDataFromUsers() ' load data from users tables
        LoadDataFromJoin() ' load data from Inner Join Query
        ComboBox1.Items.Add(1) ' add value into ComboBox1
        ComboBox1.Items.Add(2)
        ComboBox1.Items.Add(3)
    End Sub

Source Code Button Exit

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

You have done with this project, If still confused, just watch this video tutorial

Video Tutorial How to CRUD Operations

1. Download Full Source code VB.NET CRUD Operations Multiple tables using MySQL Database 
2. Download Database Examples.

Like, Share and Follow us if this tutorial can help you. thanks before.