问从VB 2015更新MS Access数据库

EN

Dim da As New OleDb.OleDbDataAdapter(strSQL, conn)
Dim cb As New OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

Five Label – TextBox combinations, arranged in a vertical column
Label1 Text = “ID” – TextBox Name = “txtID”
Label2 Text = “Name” – TextBox Name = “txtName”
Label3 Text = “NumberOne” – TextBox Name = “txtNumberOne”
Label4 Text = “NumberTwo” – TextBox Name = “txtNumberTwo”
Label5 Text = “da’s NumberTwo” – TextBox Name = “txtDaNumberTwo”

(Label4 – TextBox refers to the value in the DataSet)
(Label5 – TextBox refers to the value in the database)

Four buttons, side-by-side
btnFirst – Text = “<<”
btnPrevious – Text = “<”
btnNext – Text = “>”
btnLast – Text = “>>”

Two buttons, one above the other
btnExecute – Text = “Execute”
btnSave – Text = “Save”

One Label – TextBox combination
Label6 Text = “Error” – TextBox Name = “txtError”

'**********
' DbTest.vb
' Version 0.00
' MDJ 2015/11/11
'**********

Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
' This is the connection to the local MS Access database.
' The database is needed in both the Debug and Release folders.
Dim conn As New OleDb.OleDbConnection(connectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=TestDB.accdb;Persist Security Info=False;")
Dim strSQL As String = "SELECT * FROM TestTable"
' The DataAdapter is the bridge between the database and the DataSet. 
Dim da As New OleDb.OleDbDataAdapter(strSQL, conn)
' MUST INCLUDE:
' OleDbCommandBuilder statement is required for Insert, Update, and Delete
' as are the cb.Quote... statements below.
Dim cb As New OleDbCommandBuilder(da)
' The DataSet is the internal working copy of the portion of the database which is being processed.
' More than one dataset may be open at any given time.
Dim ds As New DataSet()
Dim ds2 As New DataSet()
Dim intCurrentIndex As Integer
Dim dSaved As Boolean = False
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    cb.QuotePrefix = "["
    cb.QuoteSuffix = "]"
    dSaved = False
    txtError.Text = "No Exceptions"
    Try
        conn.Open()
    Catch ex As OleDb.OleDbException
        txtError.Text = "OleDbException"
        GoTo errExit
    Catch ex As DataException
        txtError.Text = "DataException"
        GoTo errExit
    Catch ex As Exception
        txtError.Text = "Other Exception"
        GoTo errExit
    End Try
    txtError.Text = "Error: ds Is Empty"
    ' Fill dataset from database
    da.Fill(ds)
    'Check if the Table is empty
    If ds.Tables(0).Rows.Count > 0 Then
        txtError.Text = "No Error"
    End If
errExit:
    conn.Close()
End Sub

Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
    'Since 0 is the first row
    intCurrentIndex = 0
    txtID.Text = ds.Tables(0).Rows(intCurrentIndex).Item("ID").ToString()
    txtName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Name").ToString()
    txtNumberOne.Text = ds.Tables(0).Rows(intCurrentIndex).Item("NumberOne").ToString()
    txtNumberTwo.Text = ds.Tables(0).Rows(intCurrentIndex).Item("NumberTwo").ToString()
    If dSaved = True Then
        txtDaNumberTwo.Text = ds2.Tables(0).Rows(intCurrentIndex).Item("NumberTwo").ToString()
    End If
End Sub

Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
    'We move back only if we're not at the first row.
    If intCurrentIndex > 0 Then
        'Subtract one from the current index.
        intCurrentIndex = intCurrentIndex - 1
        txtID.Text = ds.Tables(0).Rows(intCurrentIndex).Item("ID").ToString()
        txtName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Name").ToString()
        txtNumberOne.Text = ds.Tables(0).Rows(intCurrentIndex).Item("NumberOne").ToString()
        txtNumberTwo.Text = ds.Tables(0).Rows(intCurrentIndex).Item("NumberTwo").ToString()
        If dSaved = True Then
            txtDaNumberTwo.Text = ds2.Tables(0).Rows(intCurrentIndex).Item("NumberTwo").ToString()
        End If
    Else
        MessageBox.Show("You're already at the first record.")
    End If
End Sub

Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
    'ds.Tables(0).Rows.Count - 1 is the index for the last row
    'We move forward only if we're not at the last row.
    If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then
        'Add one to the current index.
        intCurrentIndex = intCurrentIndex + 1
        txtID.Text = ds.Tables(0).Rows(intCurrentIndex).Item("ID").ToString()
        txtName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Name").ToString()
        txtNumberOne.Text = ds.Tables(0).Rows(intCurrentIndex).Item("NumberOne").ToString()
        txtNumberTwo.Text = ds.Tables(0).Rows(intCurrentIndex).Item("NumberTwo").ToString()
        If dSaved = True Then
            txtDaNumberTwo.Text = ds2.Tables(0).Rows(intCurrentIndex).Item("NumberTwo").ToString()
        End If
    Else
        MessageBox.Show("You're already at the last record.")
    End If
End Sub

Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
    'ds.Tables(0).Rows.Count - 1 is the index for the last row
    intCurrentIndex = ds.Tables(0).Rows.Count - 1
    txtID.Text = ds.Tables(0).Rows(intCurrentIndex).Item("ID").ToString()
    txtName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Name").ToString()
    txtNumberOne.Text = ds.Tables(0).Rows(intCurrentIndex).Item("NumberOne").ToString()
    txtNumberTwo.Text = ds.Tables(0).Rows(intCurrentIndex).Item("NumberTwo").ToString()
    If dSaved = True Then
        txtDaNumberTwo.Text = ds2.Tables(0).Rows(intCurrentIndex).Item("NumberTwo").ToString()
    End If
End Sub

Private Sub btnExecute_Click(sender As Object, e As EventArgs) Handles btnExecute.Click
    Dim n1 As Integer
    Dim n2 As Integer
    'ds.Tables(0).Rows.Count - 1 is the index for the last row
    Dim intLastCount As Integer
    intLastCount = ds.Tables(0).Rows.Count - 1
    ' Process each record
    For intCurrentIndex = 0 To intLastCount
        n1 = ds.Tables(0).Rows(intCurrentIndex).Item("NumberOne")
        n2 = n1 * n1
        ds.Tables(0).Rows(intCurrentIndex).Item("NumberTwo") = n2
    Next
    ' MUST NOT DO THIS BEFORE UPDATING - IT LOSES ALL CHANGES
    'ds.AcceptChanges()
End Sub

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    txtError.Text = "No Exceptions"
    Try
        conn.Open()
    Catch ex As OleDb.OleDbException
        txtError.Text = "OleDbException"
        GoTo errExit
    Catch ex As DataException
        txtError.Text = "DataException"
        GoTo errExit
    Catch ex As Exception
        txtError.Text = "Other Exception"
        GoTo errExit
    End Try

    ' Save dataset to database
    Try
        da.Update(ds)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

    ' Fill second dataset from database
    txtError.Text = "Error: ds2 Is Empty"
    da.Fill(ds2)
    'Check if the Table is empty
    If ds2.Tables(0).Rows.Count > 0 Then
        dSaved = True
        txtError.Text = "ds2: No Error"
    End If
errExit:
    conn.Close()
End Sub
End Class