Visual basic 2019 access database operations.

Question

Suppose I added a new data to a table thru this program (access database program). I have one data grid view on the form , IT SHOW OLD DATA NOT THE ONE I JUST ADDED TO THE DATA TABLE THRU THIS FORM. In other words datagridview do not refresh or reset.I have to close the application and start again to see new data I have entered.

Accepted Answer

Hi @Sheelnath Kekre ,
I have made some changes to the code in this reference: 'Database Coding with VB.Net' .
You can refer to the following code to update your datagridview( Also see: How to: Bind data to the Windows Forms DataGridView control ).

    '...'     
    Private bindingSource1 As New BindingSource()  
  
    Private Sub GetData(ByVal selectCommand As String)  
        Dim connectionString As String = "Provider=...;"  
        da = New OleDbDataAdapter(selectCommand, connectionString)  
        Dim commandBuilder As New OleDbCommandBuilder(da)  
        da.Fill(ds, "Contacts")  
  
        bindingSource1.DataSource = ds.Tables("Contacts")  
        DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)  
    End Sub  
  
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load  
        GetData("SELECT * FROM Contact")  
        DataGridView1.DataSource = bindingSource1  
  
        '...'  
    End Sub  
    Private Sub btnReload_Click(sender As Object, e As EventArgs) Handles btnReload.Click  
        ' Reload the data from the database.'  
        GetData(da.SelectCommand.CommandText)  
        DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)  
    End Sub  
    Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click  
        ' Update the database with changes in datagridview.'  
        da.Update(CType(bindingSource1.DataSource, DataTable))  
    End Sub

Hope it could be helpful.

Best Regards,
Xingyu Zhao
*
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

The following provides direction, I don't have a complete solution yet if you understand the following you can be successful. In the end the intent is to mimic how MS-Access works when working in MS-Access grid. An alternate is to use a DataTable and subscribe to events or work with a DataAdapter.

High level which in this case data is read using a connection and command, execute a SELECT, loop through results into a List(Of T) where T might be a Customer class. The Customer class would then implement INotifyPropertyChanged Interface (see example here).

Create a BindingSource and a SortableBindingList which provides sorting which is lost in a DataGridView when using List(Of T)..
Read data into the SortableBindingList
Set the DataSource of the BindingSource to the SortableBindingList
Set the DataGridView.DataSource to the BindingSource
Subscribe to ListChanged event of the SortableBindingList
Create a data class with methods for each data operation add, update, delete

In ListChanged event, check for what triggered the event via ListChangedType. Depending on the change type pass the current row via someBindingList.Item(e,NewRowIndex) for instance for an add operation which for an add would return the new primary key (super simple example)

Another approach is using a TableAdapter, I don't have a MS-Access code sample but have one for SQL-Server where the main difference is how new records are handled e.g. for SQL-Server perform a save and new records will have the new primary key while with MS-Access one must reload the data which can be done without restarting the app but doing so means one must (if it's important) remember the last position in the DataGridView and restore the position. Example.

Share via

Visual basic 2019 access database operations.

1 additional answer

Your answer