Visual Studio - multiple Table to multiple DataGridView with MS Access

Question

how can i populate multiple table into multiple DataGridview? for example i have 3 tables which is table1, table2 and table3, and in my form i have 3 dataGridView with name of dataGridView1, dataGridView2 and dataGridView3. i found this solution Updating an Access Database via a DataGridView Using OLEDB in VB.NET as a result of my code.

function loadDatabaseDataToGridView

Dim msAccessFilePath As String
Dim con As New OleDbConnection
Dim dataTable, dataTable2, dataTable3, dataTable4 As New DataTable
Dim olebDataAdapter As New OleDbDataAdapter
Dim da As New OleDbDataAdapter
Dim dataSet As New DataSet

Private Sub loadDatabaseDataToGridView()
        Try
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & msAccessFilePath
            dataSet.Tables.Add(dataTable)

            olebDataAdapter = New OleDbDataAdapter("Select * from table1", con)
            olebDataAdapter.Fill(dataTable)
            olebDataAdapter = New OleDbDataAdapter("Select * from table2", con)
            olebDataAdapter.Fill(dataTable2)
            olebDataAdapter = New OleDbDataAdapter("Select * from table3", con)
            olebDataAdapter.Fill(dataTable3)

            DataGridView1.DataSource = dataTable.DefaultView
            DataGridView2.DataSource = dataTable2.DefaultView
            DataGridView3.DataSource = dataTable3.DefaultView

            Dim cb = New OleDbCommandBuilder(olebDataAdapter)
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"
            MessageBox.Show("Successfull")

        Catch ex As Exception
            MessageBox.Show("Failed")
            con.Close()
        End Try
        con.Close()
    End Sub

function SaveChanges

'Perform this on Button Save is Click
Private Sub saveChanges()
   olebDataAdapter.Update(dataSet)
End Sub

This code is working as excpected, it is populating the data from MS Access file but when i clicked the button save to save the changes on edited data, an error has occurred.

THE ERROR:

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

anyone does know how to properly implement of what i am trying to achieve? Thank You Very Much!!!

Creating a new instance of the OleDbDataAdapter for each table will result in an Update command that works only on the last table. Could you add what is the error message received? — Steve
– Steve, Commented Jan 31, 2016 at 10:24
Uhm this errors seems to be something related to your file being in use. Do you have access opened in the design view of the table involved? — Steve
– Steve, Commented Jan 31, 2016 at 10:48

John0987 · Accepted Answer · 2016-01-31 14:17:45Z

0

Each DataAdapter object only has a single UpdateCommand, so you'll need one for each data table. If it runs the update command and no rows are updated (because the update command is actually for a table that you haven't changed) then you'll get the Concurrency Violatation.

It actually gets more complicated than this if your tables are related as the order that inserts, updates and deletions occur can be critical.

If you create a Strongly Typed Dataset it generates designer code that includes a TableAdapterManager class and all of the required data adapters. It might be worth playing around with this just so that you can see how the generated code works.

Just a note, the 'Concurrency violation' exception is actually there so that you can design an update command that will only succeed if the contents of the record in the database match the data held locally (to prevent you from overwriting another user's changes). Search for Optimistic Locking for more details.

answered Jan 31, 2016 at 14:17

John0987

735 bronze badges

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

4 Comments

bernzkie Over a year ago

Thank you! do you have any specific code, example for this? i'm so confused of doing this.

bernzkie Over a year ago

what i am actually doing here is, browse for .mdb(MS Access) file and load the data of 3 table into 3 dataGridView. Thanks.

John0987 Over a year ago

Private odaList as New List(of OleDbDataAdapter)

John0987 Over a year ago

Pressed return too soon, anyway: Use Private odaList as New List(of OleDbDataAdapter) to store the data adaptors. After you create a new OleDbDataAdapter object you just add it to the list. You also need to add each data table to your dataset. Finally in your SaveChanges method you loop through the list and call Update on each OleDbDataAdapter object. Alternatively, you add use Add->New Item (for the project), select Dataset, then drag a TableAdapter into the new dataset from the toolbox and follow the wizard. This will generate all of the code you need for database access automatically.

Collectives™ on Stack Overflow

Visual Studio - multiple Table to multiple DataGridView with MS Access

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related