none
Insert Data from DataGridView into MS Access

    Question

  • I'm almost there, but I still need help inserting the data from datagridview to ms access database. I keep hitting the button to information but there's no update on the access file itself. Can anyone help with this please? The code you need to address will be in bold.

    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
            Dim songTitle As String
            Dim songArtist As String
            Dim songAlbum As String
            Dim yearReleased As String


            Dim str As String
            str = "INSERT INTO Music Database([Song Title], [Song Artist], [Song Album], [Year Released]) Values (@Song_Title, @Song_Artist, @Song_Album, @Year_Released)"
            Dim cmd As OleDbCommand = New OleDbCommand(str, con)
            cmd.Parameters.AddWithValue("@Song_Title", songTitle)
            cmd.Parameters.AddWithValue("@Song_Artist", songArtist)
            cmd.Parameters.AddWithValue("@Song_Album", songAlbum)
            cmd.Parameters.AddWithValue("@Year_Released", yearReleased)


            Me.Validate()
            con.Open()
            ds.Tables.Add(dt)
            con.Close()
            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)
            con.Close()
            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)
            con.Close()
            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

    Saturday, April 22, 2017 5:19 PM

All replies

  • You do it very difficult while it is so easy. 

    Look at this sample on our website. 

    http://www.vb-tips.com/CommandBuilderDataGridview.ASPX


    Be aware that the dataadapter update is for SQL inserts, updates and deletes


    Success
    Cor

    Saturday, April 22, 2017 5:43 PM
  • The bold code is three separate buttons for updating three different tables.

    Wyvern

    Saturday, April 22, 2017 5:46 PM
  • Then take a look at the Tableadaptermanager, you get than all your tree tables in one dataset. 

    The best is to create the relations then in Access.

    All select, update, insert and delete commands are generated and you don't have to use the commandbuilder.

    Keep in mind not to use with that non strongly typed code anymore (just set constantly a dot and take the best method or property)

    https://msdn.microsoft.com/en-us/library/bb384426.aspx 


    Success
    Cor

    Saturday, April 22, 2017 7:03 PM
  • Hi WyvernFrog,

    I agree with Cor’s opinion, it is very easy to insert data into Access Database, please refer to the code sample below. I suggest you to use OleDbCommandBuilder class to insert data into Access.

    I put one DataGridView and Button control in the Form.

    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.

    Monday, April 24, 2017 3:06 AM
    Moderator