Saving Multiple Data in just a click Using DataGridView

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”.
  2. `FullName` text NOT NULL,
  3. `Address` text NOT NULL,
  4. PRIMARY KEY (`id`)
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.
  1. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2. 'set and add columns in the datagridview for the first load.
  3. 'the name and add represent the two textbox column for iputing data.
  4. Dim name As New DataGridViewTextBoxColumn
  5. Dim add As New DataGridViewTextBoxColumn
  6. 'set the header name of two columns
  7. With name
  8. .Name = "FullName"
  9. End With
  10. With add
  11. .Name = "Address"
  12. End With
  14. 'add the two columns in the datagridview
  15. With DataGridView1
  16. .Columns.Add(name)
  17. .Columns.Add(add)
  18. 'set your datagridview for filling the size of the column.
  19. .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
  20. End With
  22. 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.
  1. 'Set your imports
  2. Imports MySql.Data.MySqlClient
  3. Public Class Form1
  4. 'Set up your connection to MySQL database
  5. Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;database=test")
  6. 'A set of COMMADS of MySQL
  7. Dim cmd As New MySqlCommand
  8. 'A bridge between the database and the data table for saving and retrieving data
  9. Dim da As New MySqlDataAdapter
  10. 'A specific table in the database
  11. Dim dt As New DataTable
  12. 'Variable string for storing value
  13. Dim SQL As String
  14. 'Variable integer for storing value
  15. Dim result As Integer
  16. 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.
  1. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  2. Try
  3. 'oppening the connection
  4. con.Open()
  5. 'DataGridViewRow represents a row in the DataGridView Control
  6. 'Rows is to get the collection of rows in the DataGridView control
  7. 'the syntax is , getting the rows one by one
  8. For Each row As DataGridViewRow In DataGridView1.Rows
  9. 'Cells is to get the collection of cell that populate the row
  10. 'FormattedValue is to get the value of the cell as formtted for display
  11. 'the condition is, if the cell is not empty then it save to the database
  12. If row.Cells(0).FormattedValue <> "" Or row.Cells(1).FormattedValue <> "" Then
  13. 'store your query to a variable(sql)
  14. sql = "INSERT INTO member (FullName,Address) VALUES ('" _
  15. & CStr(row.Cells(0).FormattedValue) & "','" _
  16. & CStr(row.Cells(1).FormattedValue) & "')"
  17. 'Set your MySQL COMMANDS
  18. With cmd
  19. .Connection = con
  20. .CommandText = sql
  21. End With
  22. 'Execute the Data
  23. result = cmd.ExecuteNonQuery
  24. End If
  25. Next
  26. 'the condition is, if the result is equal to zero
  27. 'then the message will appear and says "No saved Record."
  28. 'and if not the message will appear and says "All Records Saved."
  29. If result = 0 Then
  30. MsgBox("No saved Record.")
  31. Else
  32. MsgBox("All Records Saved.")
  33. End If
  34. Catch ex As Exception
  35. MsgBox(ex.Message)
  36. End Try
  37. 'Close the connection
  38. con.Close()
  39. End Sub
Download the complete Source Code and run it on your computer. MERRY CHRISTMAS!!!!!!


Submitted bychanaka2017 (not verified)on Thu, 01/05/2017 - 19:14

my friend i want to know how save all record in datagridview to access database table using one button click ? can you help me.

Add new comment