Update Access database via OleDB from DataGridView

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

Answer 1

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

Answer 2

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 [e-mail]

---

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.

Answer 3

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.

Answer 4

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?

Answer 5

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.

Answer 6

"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 [e-mail]

---

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.

Answer 7

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

Answer 8

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