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

Solution

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

Comments

[Gord Thompson]

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]

Agree, adding your comment to the answer

[CuriousLekgolo]

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]

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

[Steve]

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

[bernzkie]

What if you have two DataGridView? And you want to populate the table1 to DataGridView1 and table2 to DataGridView2?

[Steve]

@bernzkie as explained above, it is better to post a new question than adding comments with a new problem to a old question/answer