Use DataSet to query and update Row

Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Data.OleDb
Imports System.Data.Common
Imports System.Data


Public Class MainClass
    
    Shared Sub Main()

        Dim dsUsers As New DataSet("Employee")

        Try
            ' Define a connection object
            Dim dbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=Employee.mdb")

            ' Create a data adapter to retrieve records from db
            Dim daUsers As New OleDbDataAdapter("SELECT ID, FirstName, LastName FROM Employee", dbConn)

            ' Define each column to map
            Dim dcmUserID As New DataColumnMapping("ID", "EmployeeID")
            Dim dcmFirstName As New DataColumnMapping("FirstName", "FirstName")
            Dim dcmLastName As New DataColumnMapping("LastName", "LastName")

            ' Define the table containing the mapped columns
            Dim dtmUsers As New DataTableMapping("Table", "Employee")
            dtmUsers.ColumnMappings.Add(dcmUserID)
            dtmUsers.ColumnMappings.Add(dcmFirstName)
            dtmUsers.ColumnMappings.Add(dcmLastName)

            ' Activate the mapping mechanism
            daUsers.TableMappings.Add(dtmUsers)

            ' Fill the dataset
            daUsers.Fill(dsUsers)

            ' Set the primary key in order to use the Find() method
            ' below.
            Dim dcaKey() As DataColumn = {dsUsers.Tables(0).Columns("EmployeeID")}
            dsUsers.Tables(0).PrimaryKey = dcaKey

            ' Declare a command builder to create SQL instructions
            ' to create and update records.
            Dim cb = New OleDbCommandBuilder(daUsers)

            ' Update an existing record in the DataSet
            Dim r As DataRow = dsUsers.Tables(0).Rows.Find(1)

            If Not r Is Nothing Then
                r("FirstName") = "Venus"
                r("LastName") = "Williams"

                ' Update the record even in the database
                daUsers.Update(dsUsers.GetChanges())

                ' Align in-memory data with the data source ones
                dsUsers.AcceptChanges()

                ' Print successfully message
                Console.WriteLine("The record has been updated successfully.")
            Else
                Console.WriteLine("No record found...")
            End If
        Catch ex As Exception
            ' Reject DataSet changes
            dsUsers.RejectChanges()

            ' An error occurred. Show the error message
            Console.WriteLine(ex.Message)
        End Try 
    End Sub
End Class

Employee.zip( 7 k)

Related examples in the same category

1.	Modify Data table: add a row and update a cell
2.	Set new cell value in DataSet
3.	Update a row in DataSet update event

Use DataSet to query and update Row : DataSet Update « Database ADO.net « VB.Net

Related examples in the same category