Saving Multiple Data in just a click Using DataGridView

Submitted by janobe on Tuesday, December 24, 2013 - 09:21.

I know some of you are already tired of saving data one at a time. So, in this tutorial I will teach you how to save multiple data using DataGridView in Visual Basic 2008 and MySQL Database. This will help make your work fast in a short period of time.
So let’s begin: Create a table in the MySQL Database named “test”.

CREATE TABLE IF NOT EXISTS `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `FullName` text NOT NULL,
  `Address` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Open Visual Basic 2008, create a project and set a Form just like this. First Form

Double click the Form and do the following codes for adding the columns and naming the headers in the DataGridView.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'set and add columns in the datagridview for the first load.
        'the name and add represent the two textbox column for iputing data.
        Dim name As New DataGridViewTextBoxColumn
        Dim add As New DataGridViewTextBoxColumn
        'set the header name of two columns
        With name
            .Name = "FullName"
        End With
        With add
            .Name = "Address"
        End With
 
        'add the two columns in the datagridview
        With DataGridView1
            .Columns.Add(name)
            .Columns.Add(add)
            'set your datagridview for filling the size of the column.
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        End With
 
    End Sub

Now, set your connection to the MySQL Database and declare the classes and variables that you needed. Set it above the Form_Load.

'Set your imports
Imports MySql.Data.MySqlClient
Public Class Form1
    'Set up your connection to MySQL database
    Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;database=test")
    'A set of COMMADS of MySQL
    Dim cmd As New MySqlCommand
    'A bridge between the database and the data table for saving and retrieving data
    Dim da As New MySqlDataAdapter
    'A specific 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

Note : Don’t forget to add MySql.Data.dll as your reference, so that you won't encounter errors when you’re declaring the MySQL. Go back to to the Design Views, double click the button and do the following codes for saving the data in MySQL Database. And it will look just like this.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            'oppening 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 cell is not empty then it save to the database
                If row.Cells(0).FormattedValue <> "" Or row.Cells(1).FormattedValue <> "" Then
                    'store your query to a variable(sql)
                    sql = "INSERT INTO member (FullName,Address) VALUES ('" _
                                & CStr(row.Cells(0).FormattedValue) & "','" _
                                & 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 equal to zero
            'then the message will appear and says "No saved Record."
            'and if not the message will appear and says "All Records Saved."
            If result = 0 Then
                MsgBox("No saved Record.")
            Else
                MsgBox("All Records Saved.")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Close the connection
        con.Close()
    End Sub

Download the complete Source Code and run it on your computer. MERRY CHRISTMAS!!!!!!