Updating an Access Database via a DataGridView Using OLEDB in VB.NET

Question

I have linked an Access database to my program. It populates the DataGridView as it is intended to so that part of the program works. However, I'm trying to get the DataGridView to update the Access database file with any changes that are made to it but after countless attempts at trying to fix my code or looking for an alternative solution, I am stumped.

Can anyone see anything wrong or something I've missed out that would cause the code not to function as desired? Thank you in advance.

Imports System.Data.OleDb

Public Class frmDatabase

Dim con As New OleDbConnection
Dim ds As New DataSet
Dim dt As New DataTable
Dim da As New OleDbDataAdapter


Private Sub frmDatabase_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Joe\Documents\Visual Studio 2012\Projects\school database viewer\school database viewer\dbSchoolDatabase.mdb"
    con.Open()
    ds.Tables.Add(dt)
    da = New OleDbDataAdapter("Select * from tableStudentDetails", con)
    da.Fill(dt)
    dgvStudentDetails.DataSource = dt.DefaultView
    con.Close()

End Sub

Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click

    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Joe\Documents\Visual Studio 2012\Projects\school database viewer\school database viewer\dbSchoolDatabase.mdb"
    con.Open()
    ds.Tables.Add(dt)
    da = New OleDbDataAdapter("Select * from tableStudentDetails", con)
    da.Update(dt)
    con.Close()

End Sub
End Class

Community · Accepted Answer · 2017-05-23 11:59:14Z

1

Just add an OleDbCommandBuilder to your code

Private Sub frmDatabase_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Joe\Documents\Visual Studio 2012\Projects\school database viewer\school database viewer\dbSchoolDatabase.mdb"
    con.Open()
    ds.Tables.Add(dt)
    da = New OleDbDataAdapter("Select * from tableStudentDetails", con)
    Dim cb = new OleDbCommandBuilder(da)
    cb.QuotePrefix = "[" 
    cb.QuoteSuffix = "]"
    da.Fill(dt)
    dgvStudentDetails.DataSource = dt.DefaultView
    con.Close()

End Sub

Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click

    da.Update(dt)

End Sub

This class is required because, by itself the OleDbDataAdapter cannot create the commands for DELETE/UPDATE/INSERT required to update new/deleted or changed row in your grid. Also keep in mind that OleDbCommandBuilder cannot build the required commands if the SELECT command don't return the primary key of your table. In that case you need to manually build yourself the commands.

Note also, as pointed by @gordthompson in its comment below, that a precautionary step to take with the OleDbCommandBuilder is to add the special characters that the CommandBuilder will use around your field names and tables names to avoid conflict with reserved keywords if any is present in your table

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Mar 11, 2015 at 11:50

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Gord Thompson Over a year ago

Especially for Access databases (with the large number of reserved words in Access SQL) OleDbCommandBuilder examples should always include cb.QuotePrefix = "[" and cb.QuoteSuffix = "]".

Steve Over a year ago

Agree, adding your comment to the answer

CuriousLekgolo Over a year ago

Just added it to the code and it worked and ah I see, never can be too safe 'eh? Thank you both so much!

CuriousLekgolo Over a year ago

Update: If I wanted create a new entry in my database via the DataGridView, how would I go about doing this?

Steve Over a year ago

Just add rows to your grid. However it is better to post a new question so you will get a renewed attention on your question

|

Collectives™ on Stack Overflow

Updating an Access Database via a DataGridView Using OLEDB in VB.NET

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related