Visual Studio项目更新用户名密码时遇OleDb语法错误求助

核心错误原因 #

你的UPDATE语句报错是因为**User和Pass是Access数据库的保留关键字**，直接作为字段名使用会触发语法错误。另外，当前代码的更新逻辑也存在问题：WHERE条件使用新用户名，无法正确匹配要修改的原用户记录，且未验证旧密码的正确性。

具体修复步骤 #

• 用方括号[]包裹保留关键字字段名，比如[User]和[Pass]
• 修改WHERE条件，使用旧用户名和旧密码定位要更新的记录，确保只有输入正确原信息的用户才能修改
• 使用Using语句管理数据库连接和命令对象，自动释放资源，避免连接泄漏
• 更新完成后刷新DataGridView，展示最新数据

修改后的完整代码 #

Imports System.Data.OleDb

Public Class User

    Public DA As New OleDbDataAdapter
    Public DT As New DataTable
    '数据库连接字符串
    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Abo Khallad\Desktop\Visual Studio VB.Net\Test VB.Net\Login User\bin\Debug\Notes.accdb"

    Sub Fill_DGV()
        '加载数据到DataGridView
        Using dataAdapter As New OleDbDataAdapter("SELECT * FROM Login", connectionString)
            Dim dataSet As New DataSet()
            dataAdapter.Fill(dataSet)
            DataGridView1.DataSource = dataSet.Tables(0)
        End Using
    End Sub

    Private Sub FrmUser_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        TxtNewUser.Focus()
        TxtNewUser.Enabled = False
        TxtNewPass.Enabled = False
        BtnOk.Enabled = False
        Fill_DGV()
    End Sub

    Private Sub BtnOk_Click(sender As Object, e As EventArgs) Handles BtnOk.Click
        '输入验证：旧用户名或旧密码为空时提示
        If String.IsNullOrEmpty(TxtOldUser.Text) Or String.IsNullOrEmpty(TxtOldPass.Text) Then
            MsgBox("请输入原用户名和密码", MsgBoxStyle.MsgBoxRtlReading + MsgBoxStyle.MsgBoxRight + vbOKOnly, Title:="提示")
            Exit Sub
        End If

        '验证新用户名和密码是否为空
        If String.IsNullOrEmpty(TxtNewUser.Text) Or String.IsNullOrEmpty(TxtNewPass.Text) Then
            MsgBox("请输入新用户名和密码", MsgBoxStyle.MsgBoxRtlReading + MsgBoxStyle.MsgBoxRight + vbOKOnly, Title:="提示")
            Exit Sub
        End If

        '执行更新操作
        Using Conn As New OleDbConnection(connectionString)
            Dim updateSql As String = "UPDATE Login SET [User] = @NewUser, [Pass] = @NewPass WHERE [User] = @OldUser AND [Pass] = @OldPass"
            Using Cmd As New OleDbCommand(updateSql, Conn)
                '添加参数
                Cmd.Parameters.Add("@NewUser", OleDbType.VarChar).Value = TxtNewUser.Text
                Cmd.Parameters.Add("@NewPass", OleDbType.VarChar).Value = TxtNewPass.Text
                Cmd.Parameters.Add("@OldUser", OleDbType.VarChar).Value = TxtOldUser.Text
                Cmd.Parameters.Add("@OldPass", OleDbType.VarChar).Value = TxtOldPass.Text

                Try
                    Conn.Open()
                    Dim rowsAffected As Integer = Cmd.ExecuteNonQuery()
                    If rowsAffected = 0 Then
                        MsgBox("原用户名或密码错误，无法更新", MsgBoxStyle.MsgBoxRtlReading + MsgBoxStyle.MsgBoxRight + vbOKOnly, Title:="提示")
                    Else
                        MsgBox("更新成功", MsgBoxStyle.MsgBoxRtlReading + MsgBoxStyle.MsgBoxRight + vbOKOnly, Title:="提示")
                        Fill_DGV() '刷新DataGridView
                        '清空输入框
                        TxtOldUser.Clear()
                        TxtOldPass.Clear()
                        TxtNewUser.Clear()
                        TxtNewPass.Clear()
                    End If
                Catch ex As OleDbException
                    MsgBox("数据库错误：" & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.MsgBoxRtlReading + MsgBoxStyle.MsgBoxRight, Title:="错误")
                Catch ex As Exception
                    MsgBox("未知错误：" & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.MsgBoxRtlReading + MsgBoxStyle.MsgBoxRight, Title:="错误")
                End Try
            End Using
        End Using
    End Sub

End Class

额外说明 #

• 使用Using语句可以确保数据库连接和命令对象在使用后自动释放，避免资源泄漏
• 添加了错误捕获，能更清晰地提示错误信息
• 增加了新用户名和密码的非空验证，以及更新结果的判断（如果没有行被影响，说明原信息错误）

内容的提问来源于stack exchange，提问作者Khaled Ali