Dataadapter with DataGridView - OLEDB

The OleDbDataAdapter serves as a crucial bridge between the DataSet and the OLEDB Data Source, facilitating communication through the OleDbConnection object. While the OleDbConnection object lacks knowledge about the retrieved data, similarly, the DataSet has no awareness of the Data Source it originates from. Hence, it is the responsibility of the OleDbDataAdapter to manage this communication between the two objects. This task is accomplished with the assistance of the TableMapping Collection.

The OleDbDataAdapter's InsertCommand, UpdateCommand, and DeleteCommand properties are instrumental in updating the database by executing data modifications on a DataSet object. To illustrate this process, the following source code demonstrates how to update a DataSet via OleDbDataAdapter using a DataGridView. To execute this code, create a new VB.NET project and add two buttons and a DataGridView control to the default Form1. Then, copy and paste the provided source code.

Full Source VB.NET
Imports System.Data.OleDb Public Class Form1 Dim connetionString As String Dim connection As OleDbConnection Dim oledbAdapter As OleDbDataAdapter Dim oledbCmdBuilder As OleDbCommandBuilder Dim ds As New DataSet Dim changes As DataSet Dim i As Integer Dim sql As String Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;" connection = New OleDbConnection(connetionString) Sql = "select * from tblUsers" Try connection.Open() oledbAdapter = New OleDbDataAdapter(Sql, connection) oledbAdapter.Fill(ds) DataGridView1.Data Source= ds.Tables(0) Catch ex As Exception MsgBox(ex.ToString) End Try End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try oledbCmdBuilder = New OleDbCommandBuilder(oledbAdapter) changes = ds.GetChanges() If changes IsNot Nothing Then oledbAdapter.Update(ds.Tables(0)) End If ds.AcceptChanges() MsgBox("Save changes") Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class

An OleDbCommandBuilder is created to automatically generate the necessary InsertCommand, UpdateCommand, and DeleteCommand objects. The QuotePrefix and QuoteSuffix properties are set to handle any special characters in the table or column names.

The DataSet is cleared, and the Fill method of the OleDbDataAdapter populates the DataSet with data from the table.

The DataGridView control's DataSource property is then set to the DataTable within the DataSet.

The btnSave_Click event handler handles the save button's click event. It establishes a connection to the database, creates a new OleDbDataAdapter, and assigns the same SELECT statement to it.

The OleDbCommandBuilder is instantiated with the necessary settings. The data modifications made within the DataSet are then saved to the database using the Update method of the OleDbDataAdapter.






Related Topics
  1. Dataadapter with dataset - sql sever
  2. Dataadapter with dataset - OLEDB Data Source
  3. Dataadapter SelectCommand - Sql Server
  4. Dataadapter SelectCommand - OLEDB Data Source
  1. Dataadapter InsertCommand - Sql Server
  2. Dataadapter InsertCommand - OLEDB Data Source
  3. Dataadapter UpdateCommand - Sql Server
  4. Dataadapter UpdateCommand - OLEDB Data Source
  5. Dataadapter DeleteCommand - Sql Server
  6. Dataadapter DeleteCommand - OLEDB Data Source
  7. Dataadapter with CommandBuilder - Sql Server
  8. Dataadapter with CommandBuilder - OLEDB
  9. Dataadapter with DataGridView - Sql Server

Related Links
  1. VB.NET DataAdapter.Fill
  2. Display Selected range of records in VB.NET