Deleting Multiple Data in MySQL Database Using Visual Basic 2008

Submitted by janobe on Wednesday, December 25, 2013 - 20:12.

In my last tutorial I teach you how to save multiple data in MySQL database. This time, I will teach you how to delete multiple data with a selector which is a CheckBox. With this, you can choose whatever data you want to delete and it is very useful. Now lets begin: I already created the MySQL Database in my first tutorial. Just click here. 1. Open the Visual Basic 2008. 2. Create a Project. 3. Set your Form just like this. Firs FOrm

Double click the Form and do this code above the Form_load. This code is for setting up the connection of MySQL Database and declaring all the variables and classes that you needed.

'set your imports
Imports MySql.Data.MySqlClient
Public Class Form1
    'set up your connection of MySQL database
    Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;database=test")
    'a set of COMMANDS in MySQL
    Dim cmd As New MySqlCommand
    'a bridge between the database and the datatable for saving and retrieving data
    Dim da As New MySqlDataAdapter
    'a specfic table in the database
    Dim dt As New DataTable
    'variable string for storing value
    Dim sql As String
    'variable integer for storing value
    Dim result As Integer
End Class

After that, create a Sub procedure for retrieving data in MySQL Database. So that, you can call the SubName and put it in different places that you want.

'a Sub Procedure for retrieving data
    Public Sub listrecords()
        Try
            'opening the connection
            con.Open()
            'store your select query to a variable(sql)
            sql = "SELECT * FROM member"
            'set a new specific table in the database
            dt = New DataTable
            'Set your MySQL COMMANDS
            With cmd
                .Connection = con
                .CommandText = sql
            End With
            da = New MySqlDataAdapter(sql, con)
            'set the MySqlDataAdapter to add or refresh rows in a specified range in the dataset 
            'to match those in the data source the data Table name.
            da.Fill(dt)
            'to get or set the data source to diplay in the DataGridView
            DataGridView1.DataSource = dt
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Releases all resources used by the MySqlDataAdapter
        da.Dispose()
        'close the connection
        con.Close()
    End Sub

Under the Form_Load, do the following codes for adding the CheckBox column and call the SubName for retrieving the data in MySQL Database.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'set a variable as a checkbox column in the DataGridView
        Dim chkbox As New DataGridViewCheckBoxColumn
        'set the width of the column in the DataGridView
        With chkbox
            .Width = 30
        End With
 
        With DataGridView1
            'Adding the checkbox column in the DataGridView
            .Columns.Add(chkbox)
            'set the rows header to invisible
            .RowHeadersVisible = False
        End With
        'Put a Sub procedure name for Listing Records on the first load
        listrecords()
    End Sub

Go back to the Design Views, double click the Delete Button. And under the Button1_Click, do the following codes to delete the records in MySQL Database.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            'opening the connection
            con.Open()
            'DataGridViewRow represents a row in the DataGridView Control
            'Rows is to get the collection of rows in the DataGridView control
            'the syntax is , getting the rows one by one
            For Each row As DataGridViewRow In DataGridView1.Rows
                'Cells is to get the collection of cell that populate the row
                'FormattedValue is to get the value of the cell as formtted for display
                'the condition is, if the checkbox is checked then it delete the data in the database
                If row.Cells(0).FormattedValue = True Then
                    'store your delete query to a variable(sql)
                    sql = "DELETE FROM member WHERE id = '" _
                                & CStr(row.Cells(1).FormattedValue) & "'"
                    'Set your MySQL COMMANDS
                    With cmd
                        .Connection = con
                        .CommandText = sql
                    End With
                    'Execute the Data
                    result = cmd.ExecuteNonQuery
                End If
            Next
            'the condition is, if the result is equals to zero
            'then the message will appear and says "No Deleted Record."
            'and if not the message will appear and says "The Record(s) has been deleted."
            If result = 0 Then
                MsgBox("No Deleted Record.")
            Else
                MsgBox("The Record(s) has been deleted.")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Close the connection
        con.Close()
        'call the SubName for retrieving data to resfresh the list of Records in the DataGridView
        listrecords()
    End Sub

Reminders: Read all the comments that I put , so that you will understand well what is happening and what are the functionalities in every code that I made. You can download the complete Source Code and run it on your computer.

Add new comment

194 views