如何使用 Visual Basic 更新/修改 Access 数据库中的现有行

Question

我在使用 VB 更新 Access 数据库中的现有行时遇到困难。我希望能够对访问表中已填充的现有行中的字段进行更改。

我的代码与在表底部而不是上面添加新记录有关。

Public Class Form1
    Dim objConnection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = StudentDatabase.accdb")
    Dim objStudentDA As New OleDb.OleDbDataAdapter("Select * FROM Student", objConnection)
    Dim objStudentCB As New OleDb.OleDbCommandBuilder(objStudentDA)
    Dim objDs As New DataSet()
 Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        If txtStudentNum.Text <> "" And txtSurname.Text <> "" And txtAttendance.Text <> "" And txtCA1.Text <> "" And txtCA2.Text <> "" And txtFinalExamResult.Text <> "" Then

            Dim objRow3 = objDs.Tables("Student").Rows.Find(txtUpdateStudentID.Text.ToString)
            'Editing each field value based on textboxes
            objRow3.Item("FName") = txtUpdateFName.Text
            objRow3.Item("SName") = txtUpdateSName.Text
            objRow3.Item("Attendance") = txtUpdateAttendance.Text
            objRow3.Item("CA1") = txtUpdateCA1.Text
            objRow3.Item("CA2") = txtUpdateCA2.Text
            objRow3.Item("FinalExam") = txtUpdateFinalExam.Text
            objRow3.Item("OverallResult") = txtUpdateOverallGrade.Text

**'ERROR HERE STATING THIS ROW ALREADY BELONGS TO A TABLE**
            objDs.Tables("Student").Rows.Add(objRow3)
            objStudentDA.Update(objDs, "Student")
            MsgBox("Record has been added to the IS2215 Database!")
            Retrieve()
        Else
            MsgBox("Error: You must not leave any fields blank!")
        End If
    End Sub
Public Sub Retrieve()
        objDs.Clear()
        objStudentDA.FillSchema(objDs, SchemaType.Source, "Student")
        objStudentDA.Fill(objDs, "Student")
        cmbStudentFind.Items.Clear()
        Dim i As Integer, strCurrentID As String
        For i = 1 To objDs.Tables("Student").Rows.Count
            strCurrentID = objDs.Tables("Student").Rows(i - 1).Item("ID")
            cmbStudentFind.Items.Add(strCurrentID)
            cmbUpdateStudentID.Items.Add(strCurrentID)
        Next
        cmbStudentFind.SelectedIndex = 0
        cmbUpdateStudentID.SelectedIndex = 0
        FillUpdateDetails()

    End Sub
Public Sub FillUpdateDetails()
        Dim objRow2 As DataRow
        objRow2 = objDs.Tables("Student").Rows.Find(cmbUpdateStudentID.SelectedItem.ToString)
        txtUpdateStudentID.Text = objRow2.Item("ID")
        txtUpdateFName.Text = objRow2.Item("FName")
        txtUpdateSName.Text = objRow2.Item("SName")
        txtUpdateAttendance.Text = objRow2.Item("Attendance")
        txtUpdateCA1.Text = objRow2.Item("CA1")
        txtUpdateCA2.Text = objRow2.Item("CA2")
        txtUpdateFinalExam.Text = objRow2.Item("FinalExam")
        txtUpdateOverallGrade.Text = objRow2.Item("OverallResult")

    End Sub
End Class

Answer 1

您需要对数据库执行 CRUD。 尝试对 Access 执行这些操作时会遇到很多问题，因为它往往会在访问表时锁定表。

我建议您构建一个 CRUD 框架，以根据需要将插入、更新和/或删除操作"机枪"到 Access 中。

除此之外，在试图让它工作的过程中，你会脱落很多头发。

Answer 2

要进行插入，您可以坚持使用当前的文本框设置。 对于进行更新，我认为DataGridView将是一个更好的方法。

这样的东西应该适合你。

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

或者，您可以坚持使用文本框概念，并稍微更改设置。

str = "Journals SET JournalTitle=?, JournalText=? WHERE JournalDate=?"
cmd = New OleDbCommand(str, myConnection) 
cmd.Parameters.AddWithValue("@jounalTitle", MyJournalTitle )
cmd.Parameters.AddWithValue("@journalText", MyJournalText)
cmd.Parameters.AddWithValue("@journalDate", DatePicked)
cmd.ExecuteNonQuery()

这种方法比你现在的做法要安全得多。 这里有更多细节。

如何更新 MS 访问数据库 (vb.net(