Transferring Multiple Data from DataGridView Rows to mySQL Database

2
171

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:

  1. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  2. If TextBox1.Text = "" Then
  3. MsgBox("Input first name!")
  4. ElseIf TextBox2.Text = "" Then
  5. MsgBox("Input middle name!")
  6. ElseIf TextBox3.Text = "" Then
  7. MsgBox("Input last name!")
  8. ElseIf TextBox4.Text = "" Then
  9. MsgBox("Input contact number!")
  10. Else
  11. DataGridView1.Rows.Add(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text)
  12. TextBox1.Text = ""
  13. TextBox2.Text = ""
  14. TextBox3.Text = ""
  15. TextBox4.Text = ""
  16. End If
  17. End Sub

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.

  1. CREATE TABLE IF NOT EXISTS <code>dgv</code> (
  2. <code>ID</code> int(11) AUTO_INCREMENT NOT NULL,
  3. <code>FIRSTNAME</code> VARCHAR(100) DEFAULT NULL,
  4. <code>MIDDLENAME</code> VARCHAR(100) DEFAULT NULL,
  5. <code>LASTNAME</code> VARCHAR(100) DEFAULT NULL,
  6. <code>CONTACT</code> VARCHAR(100) DEFAULT NULL,
  7. PRIMARY KEY (<code>ID</code>)
  8. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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.

  1. Imports MySql.Data.MySqlClient

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:

  1. Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
  2. If DataGridView1.Rows.Count &lt;= 0 Then
  3. MsgBox("No values!")
  4. Else
  5. Dim conn As MySqlConnection
  6. Dim cmd As MySqlCommand
  7. Dim sql As String
  8. Dim execution As Integer
  9. For Each dr As DataGridViewRow In Me.DataGridView1.Rows
  10. Try
  11. conn = New MySqlConnection
  12. conn.ConnectionString = "server=localhost; userid=root; password=; database=DGV;"
  13. conn.Open()
  14. sql = "INSERT INTO dgv (<code>FIRSTNAME</code>,<code>MIDDLENAME</code>,<code>LASTNAME</code>,<code>CONTACT</code>) VALUES (@First, @Middle, @Last, @Contact);"
  15. cmd = New MySqlCommand
  16. With cmd
  17. .Connection = conn
  18. .CommandText = sql
  19. .Parameters.Clear()
  20. .Parameters.AddWithValue("@First", dr.Cells(0).Value)
  21. .Parameters.AddWithValue("@Middle", dr.Cells(1).Value)
  22. .Parameters.AddWithValue("@Last", dr.Cells(2).Value)
  23. .Parameters.AddWithValue("@Contact", dr.Cells(3).Value)
  24. execution = .ExecuteNonQuery()
  25. End With
  26. conn.Close()
  27. Catch ex As MySqlException
  28. MsgBox(ex.Message)
  29. End Try
  30. Next
  31. DataGridView1.Rows.Clear()
  32. MsgBox("Successfully added values to the database!")
  33. End If
  34. End Sub

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:

 

 

 

 

Facebook Comments

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here