Transferring Multiple Data from DataGridView Rows to mySQL Database

Transferring Multiple Data from DataGridView Rows to mySQL Database

In this tutorial, you will learn how to do transferring multiple data from datagridview rows to mySQL database.

Tools needed to achieve this tutorial:

1. Microsoft Visual Studio
2. mySQL Platform (XAMPP)

Step 1:

Create a new Visual Basic project then name the project as you like.

Step 2:

Drag the datagridview with specific columns, textboxes, labels and buttons in your project. (see the sample image below).

Note: Make sure to set the AllowUserToAddRows to False in the datagridview properties tab.

Step 3:

Double click the Add Detail Button and then insert this line of code:

[vbnet]

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If TextBox1.Text = "" Then
MsgBox("Input first name!")
ElseIf TextBox2.Text = "" Then
MsgBox("Input middle name!")
ElseIf TextBox3.Text = "" Then
MsgBox("Input last name!")
ElseIf TextBox4.Text = "" Then
MsgBox("Input contact number!")
Else
DataGridView1.Rows.Add(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text)
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
End If
End Sub

[/vbnet]

Step 4:

We will now create our DGV database and tables based on the text values from the datagridview. To add tables on our DGV database, insert this line of code on the SQL tab of your XAMPP.

[mysql]

CREATE TABLE IF NOT EXISTS `dgv` (
`ID` int(11) AUTO_INCREMENT NOT NULL,
`FIRSTNAME` VARCHAR(100) DEFAULT NULL,
`MIDDLENAME` VARCHAR(100) DEFAULT NULL,
`LASTNAME` VARCHAR(100) DEFAULT NULL,
`CONTACT` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[/mysql]

Step 5:

Add MySql.Data.dll to your references for us to call the mySQL functions. (see image below).

After adding the reference, insert this line of code at the top part of your Form1 Class.

[vbnet]

Imports MySql.Data.MySqlClient

[/vbnet]

Step 6:

After adding the MySql.Data.dll to your references, double click your “Add All Values to Database” and insert this line of code:

[vbnet]

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
If DataGridView1.Rows.Count <= 0 Then
MsgBox("No values!")
Else
Dim conn As MySqlConnection
Dim cmd As MySqlCommand
Dim sql As String
Dim execution As Integer
For Each dr As DataGridViewRow In Me.DataGridView1.Rows
Try
conn = New MySqlConnection
conn.ConnectionString = "server=localhost; userid=root; password=; database=DGV;"
conn.Open()
sql = "INSERT INTO dgv (`FIRSTNAME`,`MIDDLENAME`,`LASTNAME`,`CONTACT`) VALUES (@First, @Middle, @Last, @Contact);"
cmd = New MySqlCommand
With cmd
.Connection = conn
.CommandText = sql
.Parameters.Clear()
.Parameters.AddWithValue("@First", dr.Cells(0).Value)
.Parameters.AddWithValue("@Middle", dr.Cells(1).Value)
.Parameters.AddWithValue("@Last", dr.Cells(2).Value)
.Parameters.AddWithValue("@Contact", dr.Cells(3).Value)
execution = .ExecuteNonQuery()
End With
conn.Close()
Catch ex As MySqlException
MsgBox(ex.Message)
End Try
Next
DataGridView1.Rows.Clear()
MsgBox("Successfully added values to the database!")
End If
End Sub

[/vbnet]

[vbnet]

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
If DataGridView1.Rows.Count <= 0 Then
MsgBox("No values!")
Else
Dim conn As MySqlConnection
Dim cmd As MySqlCommand
Dim sql As String
Dim execution As Integer
For Each dr As DataGridViewRow In Me.DataGridView1.Rows
Try
conn = New MySqlConnection
conn.ConnectionString = "server=localhost; userid=root; password=; database=DGV;"
conn.Open()
sql = "INSERT INTO dgv (`FIRSTNAME`,`MIDDLENAME`,`LASTNAME`,`CONTACT`) VALUES (@First, @Middle, @Last, @Contact);"
cmd = New MySqlCommand
With cmd
.Connection = conn
.CommandText = sql
.Parameters.Clear()
.Parameters.AddWithValue("@First", dr.Cells(0).Value)
.Parameters.AddWithValue("@Middle", dr.Cells(1).Value)
.Parameters.AddWithValue("@Last", dr.Cells(2).Value)
.Parameters.AddWithValue("@Contact", dr.Cells(3).Value)
execution = .ExecuteNonQuery()
End With
conn.Close()
Catch ex As MySqlException
MsgBox(ex.Message)
End Try
Next
DataGridView1.Rows.Clear()
MsgBox("Successfully added values to the database!")
End If
End Sub

[/vbnet]

To test, I’ve added a couple of entries to our datagridview and then clicked the add all values to database. These are the results:

Added values to datagridview.

Added datagridview row values to database.

Added values from datagridview rows to our mySQL database.

Transferring Multiple Data from DataGridView Rows to mySQL Database is achieved!

RAR Extraction Password:

Password: luffypirates

For questions or any other concerns or thesis/capstone creation with documentation, you can contact me through the following:

E-Mail: [email protected]

Facebook: facebook.com/kirk.lavapiez

Contact No.: +639771069640

To download the sample project, click here.

Ian Hero L. Lavapiez

BSIT Graduate

System Analyst and Developer

Related topic(s) that you may like:

• Attendance Monitoring System using RFID in VB.Net and mySQL
• Complete Award Automated Recording System