none
How to save the modified data by using datagridview into Access Databases ?

    Question

  • Please help me,

    I have a problem to save the modified datagridview into MS Access.

    The source code :

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

     

            Dim i As Integer

            Try

                Me.Validate()

                Me.Database3DataSet1BindingSource1.EndEdit()

                Me.Database3DataSet1.Tables(0).Rows(2).Item(3) = TextBox1.Text

                i = NamaTableAdapter.Update(Database3DataSet1)

                Database3DataSet1.AcceptChanges()

            Catch ex As Exception

                MessageBox.Show(ex.Message)

            End Try

            MessageBox.Show("no of rows updated=" & i)

        End Sub

    When we look over MS Access database, there is no changes in this database.

    Wednesday, April 26, 2017 2:16 PM

All replies

  • Hi Muljanto,

    I suggest you to use OleDbCommandBuilder class to update, please refer to the code below.

    I put one DataGridView and Button Control in the Form.

    Imports System.Data.OleDb
    
    Public Class InsertAccess
        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
    End Class

    Hope it is helpful to you.

    Best Regards,

    Cherry Bu


    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.

    Thursday, April 27, 2017 1:28 AM
    Moderator
  • When using a TableAdapter means you most likely had Visual Studio create strong typed classes for your data and added the database to the project. 

    If this is the case then the default setting for Copy to Output directory is "Copy always" meaning if you do updates, close the app the restart from a new build the database in the project folder is copies to the Bin\Debug folder overwriting your changes.

    Use the following setting (done from single clicking the database in solution explorer then select properties)

    If the above does not help then the other things is make sure you are checking the right database.

    The two above suggestions are common problems (seen this over the past ten years a lot) for developers who have not worked with TableAdapters before.

    See my MSDN article on the above


    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

    Thursday, April 27, 2017 1:59 AM
    Moderator