datagridview
my friend i want to know how save all record in datagridview to access database table using one button click ?
can you help me.
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
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
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 Else End If Catch ex As Exception End Try 'Close the connection End Sub