none
How to Update Access Database from DataGrid View in Visual Basic

    Question

  • I need help with updating my code to it can update the Microsoft Access Database. I think I got the code right but when I went into Access there was no data there any thoughts on that? If it's in the file then where can I see the database in visual Basic?

    Imports System.Data.OleDb

    Public Class Form1

        Dim provider As String
        Dim datafile As String
        Dim connString As String
        Dim con As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source=G:\Music Session Database\Music Database.accdb")
        Dim ds As New DataSet

        Dim dt As New DataTable

        Dim da As New OleDbDataAdapter

        Private Sub btnexit_Click(sender As Object, e As EventArgs) Handles btnexit.Click
            Me.Close()
        End Sub



        Private Sub btnsubmit_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles btnsubmit1.Click

            Me.Music_DatabaseTableAdapter.Insert(Me.songTitle.Text, Me.songArtist.Text, Me.songAlbum.Text, Me.yearReleased.Text)
            Me.Music_DatabaseTableAdapter.Fill(Me.Music_DatabaseDataSet.Music_Database)


            con.Open()
            MsgBox("Record Added")
            con.Close()
            songTitle.Text = ""
            songArtist.Text = ""
            songAlbum.Text = ""
            yearReleased.Text = ""


        End Sub


        Private Sub btnsumbit2_Click(sender As Object, e As EventArgs) Handles btnsumbit2.Click

            Me.Play_SessionTableAdapter.Insert(Me.songTitle.Text, Me.songArtist.Text, Me.songAlbum.Text, Me.yearReleased.Text, Me.datePlayed.Text, Me.timePlayed.Text, Me.genre.Text)
            Me.Play_SessionTableAdapter.Fill(Me.Music_DatabaseDataSet.Play_Session)


            con.Open()
            MsgBox("Record Added")
            con.Close()
            songTitle.Text = ""
            songArtist.Text = ""
            songAlbum.Text = ""
            yearReleased.Text = ""
            datePlayed.Text = ""
            timePlayed.Text = ""
            genre.Text = ""



        End Sub

        Private Sub btnsubmit3_Click(sender As Object, e As EventArgs) Handles btnsubmit3.Click
          

            Me.Song_Artist_InformationTableAdapter.Insert(Me.songArtist.Text, Me.genre.Text, Me.origin.Text, Me.artistInformation.Text)
            Me.Song_Artist_InformationTableAdapter.Fill(Me.Music_DatabaseDataSet.Song_Artist_Information)

            con.Open()
            MsgBox("Record Added")
            con.Close()
            songArtist.Text = ""
            genre.Text = ""
            origin.Text = ""
            artistInformation.Text = ""


        End Sub

        Private Sub btnclear_Click(sender As Object, e As EventArgs) Handles btnclear.Click
            songTitle.Clear()
            songArtist.Clear()
            songAlbum.Clear()
            yearReleased.Clear()
            datePlayed.Clear()
            timePlayed.Clear()
            genre.Clear()
            artistInformation.Clear()
        End Sub


        Private Sub FillByToolStripButton_Click(sender As Object, e As EventArgs)
            Try

            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try

        End Sub

        Private Sub Music_DatabaseBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles Music_DatabaseBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.Music_DatabaseBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.Music_DatabaseDataSet)

        End Sub

        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'Music_DatabaseDataSet.Song_Artist_Information' table. You can move, or remove it, as needed.
            Me.Song_Artist_InformationTableAdapter.Fill(Me.Music_DatabaseDataSet.Song_Artist_Information)
            'TODO: This line of code loads data into the 'Music_DatabaseDataSet.Play_Session' table. You can move, or remove it, as needed.
            Me.Play_SessionTableAdapter.Fill(Me.Music_DatabaseDataSet.Play_Session)
            'TODO: This line of code loads data into the 'Music_DatabaseDataSet.Music_Database' table. You can move, or remove it, as needed.
            Me.Music_DatabaseTableAdapter.Fill(Me.Music_DatabaseDataSet.Music_Database)


            

     

     
        End Sub

       


        Private Sub btnupdate1_Click(sender As Object, e As EventArgs) Handles btnupdate1.Click
            Me.Validate()
            con.Open()
            ds.Tables.Add(dt)
            da = New OleDbDataAdapter("Select * from [Music Database]", con)
            Dim cb = New OleDbCommandBuilder(da)
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"
            da.Fill(dt)
            Music_DatabaseDataGridView.DataSource = dt.DefaultView
            da.Update(dt)
        End Sub

        Private Sub btnupdate2_Click(sender As Object, e As EventArgs) Handles btnupdate2.Click
            Me.Validate()
            con.Open()
            ds.Tables.Add(dt)
            da = New OleDbDataAdapter("Select * from [Play Session]", con)
            Dim cb = New OleDbCommandBuilder(da)
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"
            da.Fill(dt)
            Play_SessionDataGridView.DataSource = dt.DefaultView

            da.Update(dt)


        End Sub

        Private Sub btnupdate3_Click(sender As Object, e As EventArgs) Handles btnupdate3.Click
            Me.Validate()
            con.Open()
            ds.Tables.Add(dt)
            da = New OleDbDataAdapter("Select * from [Song Artist Information]", con)
            Dim cb = New OleDbCommandBuilder(da)
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"
            da.Fill(dt)
            Song_Artist_InformationDataGridView.DataSource = dt.DefaultView


            da.Update(dt)
        End Sub
    End Class


    Wyvern

    Friday, April 21, 2017 7:25 PM

All replies

  • Under the database properties change Copy to output Directory as follows from Copy Always to Copy if Newer now try the update.


    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, April 21, 2017 7:44 PM
    Moderator
  • When I opened up access file I still don't see the data and I hit refresh as well.

    Wyvern

    Friday, April 21, 2017 8:01 PM
  • Hi WyvernFrog,

    According to your description, I find you have three DataGridView Controls to display three tables data, you just refer to the code below to display.

    Private Sub loaddate()
            Dim dt As New DataTable()
            Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\Database2.accdb; Persist Security Info=False;"
            Using conn As New OleDbConnection(strConn)
                Dim selectSql = "select * from OrderTable"
                Using com As New OleDbCommand(selectSql, conn)
                    Try
                        conn.Open()
                        Dim adapter As New OleDbDataAdapter(com)
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        conn.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString())
                    End Try
                End Using
            End Using
    End Sub 
    


    But if you want to modify the data in the DataGridView and update these data into the table, I suggest you to use OleDbCommandBuilder class to achieve it.

    Imports System.Data.OleDb
    
    Public Class Form1
        Public dt As DataTable
        Public da As OleDbDataAdapter
        Public Sub Startload()
            Dim conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\InsertDate.accdb; Persist Security Info=False;")
            da = New OleDbDataAdapter("select * from Table1", conn)
            dt = New DataTable
            da.Fill(dt) '将查到的数据传到DataTable中
            DataGridView1.DataSource = dt
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim SCB = New OleDbCommandBuilder(da)
            da.Update(dt)
            MsgBox("Update success")
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            '删除选中行
            DataGridView1.Rows.RemoveAt(DataGridView1.CurrentCell.RowIndex)
            '数据库中进行删除
            Dim SCB = New OleDbCommandBuilder(da)
            da.Update(dt)
            MsgBox("Delete success")
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Startload()
        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.

    Monday, April 24, 2017 5:42 AM
    Moderator