locked
Update Access database via OleDB from DataGridView RRS feed

  • Question

  • I have been scouring these forums and the internet in general as well as doing a lot of reading, all to no avail.  I can not seem to successfully update the Access database from an edited DataGridView.  I am trying to use Stored Procedures that are in the Access database and work fine therein.  The DGV is filled in properly.  I have tried an ever-increasing number of variants to update the database (Private Sub BtnUpdate...)  without success.  I'd really, really appreciate some guidance here.

     

    Here is my code thus far:

    Public Class Form1

        Dim con As OleDbConnection    

        Dim cmd As OleDbCommand

        Dim da As OleDbDataAdapter

        Dim ds As DataSet

        Dim ProviderConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

        Dim TargetList As String = "C:\Users\Administrator\Documents\Visual Studio 2010\Projects\Development5\Test.mdb"

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

            'establish a connection to the database

            con = New OleDbConnection(ProviderConnectionString & TargetList)

            con.Open()

            'define the command to be used

            cmd = New OleDbCommand

            cmd.Connection = con

            cmd.CommandType = CommandType.StoredProcedure

            cmd.CommandText = "ListAllTargets"

             'create the data adapter based on the command

            da = New OleDbDataAdapter(cmd)

            'fill the data set based on the command

            ds = New DataSet

            da.Fill(ds, "AllTargets")

            'bind and load dgvTargets with the data

            dgvTargetList.DataSource = ds.Tables("AllTargets")  ' Binding to dgvtargetlist

         End Sub

     

        Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

             da.UpdateCommand = New OleDbCommand("UPDATE TargetList SET;", con)

            Validate()

            da.Update(ds.Tables("AllTargets"))

            Me.ds.AcceptChanges()

        End Sub

    End Class


    Thursday, September 2, 2010 3:39 AM

Answers

  • Hi John,

     

    Welcome to MSDN forums!

     

    Cor pointed you to the right direction. An OleDBCommandbuilder object is required, which can be used to automatically generate DeleteCommand, UpdateCommand and InsertCommand for DataAdapter object.

     

    Here is detailed walkthrough: How to update (Insert/Update/Delete) data back into MS Access database from DataGridView.

    1) New a WinForms project, drag&drop DataGridView1 and Button1 onto Form1.

    2) Add database file test.mdb to project via: Data menu -> Add New Data Source Wizard ... then you can use ralative path to this database file in code

    3) Select/click your database file test.mdb in Solution Explorer -> Properties Pane -> change the "copy to ouput directory" to "copy if newer"

    4) Code sample

    Imports System.Data.OleDb

    Public Class Form1

        Dim myDA As OleDbDataAdapter

        Dim myDataSet As DataSet

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\test.mdb" ' Use relative path to database file

            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)

            con.Open()

            myDA = New OleDbDataAdapter(cmd)

            'Here one CommandBuilder object is required.

            'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  

            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)

            myDataSet = New DataSet()

            myDA.Fill(myDataSet, "MyTable")

            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView

            con.Close()

            con = Nothing

        End Sub

     

        ' Save data back into database  

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

            Me.Validate()

            Me.myDA.Update(Me.myDataSet.Tables("MyTable"))

            Me.myDataSet.AcceptChanges()

        End Sub

    End Class

    Best regards,

    Martin Xie

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by JohnZonie Thursday, September 2, 2010 7:04 PM
    Thursday, September 2, 2010 9:54 AM

All replies

  • John,

    Forget that you update a database from a datagridview, a DataGridView is an User Interface to a datacontainer .

    However, what you miss is that you need more update commands (insert, update, delete)

    Probably you can use the OleDBCommandbuilder for that

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

            dim x as new OleDBCommandbuilder(da) 'that x is never used

            Validate()

            da.Update(ds.Tables("AllTargets"))

            'the acceptchanges is build in the DataAdapter

        End Sub

     

     


    Success
    Cor
    Thursday, September 2, 2010 8:11 AM
  • Hi John,

     

    Welcome to MSDN forums!

     

    Cor pointed you to the right direction. An OleDBCommandbuilder object is required, which can be used to automatically generate DeleteCommand, UpdateCommand and InsertCommand for DataAdapter object.

     

    Here is detailed walkthrough: How to update (Insert/Update/Delete) data back into MS Access database from DataGridView.

    1) New a WinForms project, drag&drop DataGridView1 and Button1 onto Form1.

    2) Add database file test.mdb to project via: Data menu -> Add New Data Source Wizard ... then you can use ralative path to this database file in code

    3) Select/click your database file test.mdb in Solution Explorer -> Properties Pane -> change the "copy to ouput directory" to "copy if newer"

    4) Code sample

    Imports System.Data.OleDb

    Public Class Form1

        Dim myDA As OleDbDataAdapter

        Dim myDataSet As DataSet

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\test.mdb" ' Use relative path to database file

            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)

            con.Open()

            myDA = New OleDbDataAdapter(cmd)

            'Here one CommandBuilder object is required.

            'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  

            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)

            myDataSet = New DataSet()

            myDA.Fill(myDataSet, "MyTable")

            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView

            con.Close()

            con = Nothing

        End Sub

     

        ' Save data back into database  

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

            Me.Validate()

            Me.myDA.Update(Me.myDataSet.Tables("MyTable"))

            Me.myDataSet.AcceptChanges()

        End Sub

    End Class

    Best regards,

    Martin Xie

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by JohnZonie Thursday, September 2, 2010 7:04 PM
    Thursday, September 2, 2010 9:54 AM
  • Besides, there are other approaches to make a basic Data Access application (Next, Previous, First, Last, Update, Delete, Insert, Save) in VB.Net. Please check this FAQ for details:

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/c27538e2-44d7-4cb9-9141-ed7ee733bf80


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 2, 2010 9:59 AM
  • Thanks for the replies Cor and Martin.

    Cor,

    You make a great point about dgv being a user interface to the dataset.  That is a good clarification.

    I added the CommandBuilder you suggested.  When trying to update, I get anInvalid Operation Exception at

     

     Me.da.Update(Me.ds.Tables("AllTargets"))


    Which indicates: "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information."

    Can you (or anyone) translate that error message into what needs to be done?  Does this mean CommandBuilder doesn't work with a StoredProcedure command?

    Martin,

    I had been through your walkthroughs.  Since none of them used StoredProcedures, I didn't try them. Same question: Does CommandBuilder *require* SQL commands?

    Or is there some other explanation?

    Thursday, September 2, 2010 2:19 PM
  • Answering my own question: apparently yes.  That seems to do the trick.  With a suitable SQL command, the update of *existing* records seems to work fine.  

    However, I inserted a new record into DGV and the update failed at the update command with "Syntax error in INSERT INTO statement."  I have verified my database doesn't use any of the reserved keywords from KB892608 and KB248738.  Since this is presumably an auto-generated command, any ideas on what I need to do on this one?

     

    I renamed all columns to something like txtColumn even though there were no apparent keyword conflicts and all works as advertised.

    Thanks to all.

    Thursday, September 2, 2010 3:02 PM
  • "Syntax error in INSERT INTO statement."  I have verified my database doesn't use any of the reserved keywords

    -> Thank you for your quick feedback.

    1. Please double check whether there is any space in table column name such as "Name Column", if yes, please rename as "NameColumn".

    2. Please ensure that primary key is set for database table.

     

    Additionaaly, about error message "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information”, please check these similar cases for some ideas.

    http://support.microsoft.com/kb/316756

    http://www.devnewsgroups.net/adonet/t40269-dynamic-sql-generation-not-supported-against-selectcommand-that-does-not-return-any-base-table.aspx

     

    Best regards,

    Martin Xie

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 3, 2010 1:39 PM
  • thank you for this answer, i adapted it to my c# program and it works but i used a datatable before and since i changed to a dataset i get errors from this part of code

    private void addClick(object sender, EventArgs e)
            {         
                this.detailBindingSource.AddNew();    // Adds a new row
            }
    
            private void removeClick(object sender, EventArgs e)
            {
                if (this.detailBindingSource.Position >= 0)
                    this.detailBindingSource.RemoveCurrent(); // deletes current row            
            }
    what should i change? the answer can be in VB.NET


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Friday, July 26, 2013 5:47 PM
  • Hi Martin Xie,

    This is an old answer from you but I am trying to use your above code. I cannot make your code work. I have never used OleDbDataAdapter, OleDbCommand, or a OleDbCommandBuilder. I see where a lot of places it is strongly suggested not to use the OleDbCommandBuilder. Rather than use the OleDbCommandBuilder can I just create an OleDbCommand and insert it where you say, 'Here one CommandBuilder object is required.

    Can you give me sample code as to how to build an OleDbCommand with the sql stmnt.

    I am a novice using VS2013 express and a 2010 Access db.

    Thank You,

    Tom De Stefano

    Wednesday, April 29, 2015 11:49 PM