DataGridView is a simple way to display the data in table form. The data can be from remote database such as MS Access, SQL Server or any other sources. Its also easy to update the back end database directly from DataGridView. Any changes made to the datagridview can be updated to the data source. I will show you how.
Please refers to my previous post on how to load MS Access data table to DataGridView.
The form design
The Save and Update button will update the origin database with the new data from datagridview. For example if user add new record via the data grid view, or delete a record, it will also delete or add record to the origin database.
The flow
First, we need to fill the data grid view with the data from our source, such as MS Access database. This code will be executed during the form load.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Public strSQL As String Public MyAdapter As OleDb.OleDbDataAdapter Public Dt As New DataTable Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load Try MyModuleConnect.Connect() strSQL = "Select * from Tbl_DHCollar" MyAdapter = New OleDb.OleDbDataAdapter(strSQL, MyModuleConnect.MyConn) Dt = New DataTable() MyAdapter.Fill(Dt) Me.DataGridView1.DataSource = Dt Catch ex As Exception MsgBox(ex.Message) End Try End Sub |
The code above will load the table Tbl_DHCollar to DataGridView 1 during the form load. And make a note that I was created a module called MyModuleConnect to define the OleDb connection details. So I don’t have to redefine the OleDb connection again here.
Output:
OK now data is loaded to DataGridView. Now we can add code to the Save and Update button. The following code will perform update to the database on every changes made by user to the table above.
|
1 2 3 4 5 6 7 8 9 10 |
Private Sub btn_Save_Click(sender As Object, e As EventArgs) Handles btn_Save.Click 'Save changes to datagridview Try MyAdapter.UpdateCommand = New OleDb.OleDbCommandBuilder(MyAdapter).GetUpdateCommand() MyAdapter.Update(Dt) MsgBox("Database updated", MsgBoxStyle.Information) Catch ex As Exception MsgBox(ex.Message) End Try End Sub |
When the Save and Update button is clicked, the database will be updated.
