none
how to save all data from datagridview to access database vb net

    Question

  • please help me how to save all data from datagridview to access database vb net ??

    thanks

    Friday, July 07, 2017 8:02 PM

Answers

  • Hi khabib,

    If you use connection and DataAdapter to fill DataGirdView, and then make some change on the dataGridview, you want to update this change and save into the Access DataBase, you can use OleDbCommandBuilder, please  refer to the code below.

    Public adapter As OleDbDataAdapter
        Public dt As DataTable
        Private Sub InsertAccess_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            loaddate()
        End Sub
        Private Sub loaddate()
            dt = New DataTable()
            Dim conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\InsertDate.accdb; Persist Security Info=False;")
            Dim selectSql = "select * from Table1 "
            conn.Open()
            adapter = New OleDbDataAdapter(selectSql, conn)
            adapter.Fill(dt)
            DataGridView1.DataSource = dt
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
                Dim scb = New OleDbCommandBuilder(adapter)
                adapter.Update(dt)
                MessageBox.Show("OK!")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

    Best Regadrs,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by khabib mubarak Saturday, September 02, 2017 4:05 AM
    Monday, July 10, 2017 7:14 AM
    Moderator

All replies

  • Hello,

    First we need to know how you populated the DataGridView such as via a connection/command and fill a Datatable or using a connection and DataAdapter or perhaps via TableAdapter. Each method has different ways to consider how saving data is done.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, July 07, 2017 9:26 PM
    Moderator
  • Hi khabib,

    If you use connection and DataAdapter to fill DataGirdView, and then make some change on the dataGridview, you want to update this change and save into the Access DataBase, you can use OleDbCommandBuilder, please  refer to the code below.

    Public adapter As OleDbDataAdapter
        Public dt As DataTable
        Private Sub InsertAccess_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            loaddate()
        End Sub
        Private Sub loaddate()
            dt = New DataTable()
            Dim conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\InsertDate.accdb; Persist Security Info=False;")
            Dim selectSql = "select * from Table1 "
            conn.Open()
            adapter = New OleDbDataAdapter(selectSql, conn)
            adapter.Fill(dt)
            DataGridView1.DataSource = dt
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
                Dim scb = New OleDbCommandBuilder(adapter)
                adapter.Update(dt)
                MessageBox.Show("OK!")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

    Best Regadrs,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by khabib mubarak Saturday, September 02, 2017 4:05 AM
    Monday, July 10, 2017 7:14 AM
    Moderator
  • thanks

    Saturday, September 02, 2017 4:06 AM
  • This will do what you asked for.

    Imports System
    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Windows.Forms
    
    Public Class Form1
        Inherits System.Windows.Forms.Form
    
        Private bindingSource1 As New BindingSource()
        Private dataAdapter As New OleDbDataAdapter()
    
        <STAThreadAttribute()> _
        Public Shared Sub Main()
            Application.Run(New Form1())
        End Sub
    
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\your_path\Northwind_2012.mdb"
            Dim selectCommand As String
            Dim connection As New OleDbConnection(connectionString)
    
            selectCommand = "Select * From Customers ORDER BY ID"
            Me.dataAdapter = New OleDbDataAdapter(selectCommand, connection)
    
            With DataGridView1
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
            End With
    
            Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)
            Dim table As New DataTable()
    
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
    
            Me.dataAdapter.Fill(table)
            Me.bindingSource1.DataSource = table
    
            Dim data As New DataSet()
    
            data.Locale = System.Globalization.CultureInfo.InvariantCulture
    
            DataGridView1.DataSource = Me.bindingSource1
            Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Aqua
            Me.DataGridView1.AutoResizeColumns( _
                DataGridViewAutoSizeColumnsMode.AllCells)
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click, btnUpdate.Click
            Dim table As New DataTable()
    
            Me.bindingSource1 = Me.DataGridView1.DataSource
            table = Me.bindingSource1.DataSource
    
            Me.dataAdapter.Update(table)
        End Sub
    
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click, btnClose.Click
            Me.Close()
        End Sub
    
        Private Sub TextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox1.TextChanged, TextBox1.Click
    
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\your_path\Northwind_2012.mdb"
            Dim selectCommand As String
            Dim connection As New OleDbConnection(connectionString)
    
            'selectCommand = "Select * From MyExcelTable where Fname = '" & TextBox1.Text & "'"
            '"SELECT * FROM Customers WHERE Address LIKE '" & strAddressSearch & "%'"
            'or ending with:
            '"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearch & "'"
    
            selectCommand = "Select * From MyExcelTable where Fname Like '" & TextBox1.Text & "%'"
            Me.dataAdapter = New OleDbDataAdapter(selectCommand, connection)
    
            With DataGridView1
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
            End With
    
            Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)
            Dim table As New DataTable()
    
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
    
            Me.dataAdapter.Fill(table)
            Me.bindingSource1.DataSource = table
    
            Dim data As New DataSet()
    
            data.Locale = System.Globalization.CultureInfo.InvariantCulture
    
            DataGridView1.DataSource = Me.bindingSource1
            Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Aqua
            Me.DataGridView1.AutoResizeColumns( _
                DataGridViewAutoSizeColumnsMode.AllCells)
    
        End Sub
    End Class


    MY BOOK


    • Edited by ryguy72 Wednesday, September 06, 2017 3:10 AM
    Wednesday, September 06, 2017 3:09 AM