我收到"UPDATE语句中的语法错误"；使用OleDB [英] I get "Syntax error in UPDATE statement" with OleDB

问题描述

我正在开发一个与连接的数据源/MS Access数据库一起使用的信息系统.这个问题有点陈词滥调，但是我似乎无法从遇到的类似问题中找到合适的解决方案.

I am developing an information system that works with a connected data source / MS Access database. The question is kinda cliche but I can't seem to find a proper solution from the similar ones I have come across.

这是我的按钮代码.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    'myConnection.ConnectionString = connString
    'myConnection.Open()
    If Me.txtConfirmPasscode.Text = Me.txtNewPasscode.Text Then
        Dim updateCmd As OleDbCommand = New OleDbCommand("UPDATE Users SET Password = @ConfPasscode WHERE [Usernames] = @UsersID", myConnection)
        Dim dr2 As OleDbDataReader = updateCmd.ExecuteReader 'SYNTEX ERROR IN UPDATE STATEMENT

        With updateCmd.Parameters
            updateCmd.Parameters.AddWithValue("@value", txtUserID.Text)
            updateCmd.Parameters.AddWithValue("@firstname", txtConfirmPasscode.Text)
        End With

        updateCmd.ExecuteNonQuery()

        Dim recFound As Boolean = False
        Dim UserName As String = ""

        While dr2.Read
            recFound = True
            UserName = dr2("Usernames").ToString
        End While

        If recFound = True Then
            MessageBox.Show("Password changed successfully for " & UserName & ".", "Password Changed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

            'updateCmd.Parameters.Add(New OleDbParameter("Password", CType(txtConfirmPasscode.Text, String)))
        Else
            myConnection.Close()
            Me.Refresh()
        End If
    Else

    End If

    Try
        myConnection.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

当我到达以下代码行时，我得到了一个很大的UPDATE语句语法错误:

I get a huge UPDATE statement syntax error when I reach these lines of code:

Dim updateCmd As OleDbCommand = New OleDbCommand("UPDATE Users SET Password = @ConfPasscode WHERE [Usernames] = @UsersID", myConnection)
        Dim dr2 As OleDbDataReader = updateCmd.ExecuteReader 'I GET THE SYNTAX ERROR IN UPDATE STATEMENT ERROR HERE!

我希望我能找到一种无需过度格式化代码即可使用的解决方案.我还想获得我的代码语法/语法的解决方案，可能会导致上述代码出现其他一些问题

I hope that I can get a solution that works without overly formatting the code. I would also like to get solutions to my code grammer / syntax that could possibly cause some other problems in the above code

推荐答案

密码是ms-access中的保留关键字.您需要在它周围加上方括号，但随后又遇到了另一个问题.您应该在执行查询之前设置参数，尽管OleDb不会按名称而是按位置识别参数，但使用占位符为匹配的名称命名不会造成损害

Password is a reserved keyword in ms-access. You need square brackets around it, but then you have another problem. You should set the parameters BEFORE executing the query, and albeit OleDb doesn't recognize parameters by name but by position, giving a matching name with your placeholders doesn't hurt

Dim updateCmd As OleDbCommand = New OleDbCommand("UPDATE Users 
     SET [Password] = @ConfPasscode 
     WHERE [Usernames] = @UsersID", myConnection)
With updateCmd.Parameters
    ' First ConfPasscode because is the first placeholder in the query
    updateCmd.Parameters.AddWithValue("@ConfPasscode ", txtConfirmPasscode.Text)
    ' Now UsersID as second parameter following the placeholder sequence
    updateCmd.Parameters.AddWithValue("@UsersID", txtUserID.Text)
End With
Dim rowUpdated = updateCmd.ExecuteNonQuery
....

针对安德鲁·莫顿(Andrew Morton)的以下评论，我要提到AddWithValue引起的问题.在这种情况下，仅使用字符串是一个性能问题，而在其他情况下(日期和小数)可能会升级为正确性问题.

In response to the comment below of Andrew Morton, I should mention to the problems caused by AddWithValue. In this context, with just strings, it is a performance problem, in other context (dates and decimals) could escalate to a correctness problem.

参考文献
我们可以停止使用AddWithValue吗?已经?
数据访问代码如何影响数据库性能

References
Can we stop to use AddWithValue already?
How data access code affects database performance

此外，如另一个答案所述，用于Update查询的正确方法是ExecuteNonQuery，但是ExecuteReader可以更新您的表，但是因为它建立了仅当您需要读取内容时才需要的基础结构，因此Update的效率较低.无论如何，仅使用ExecuteNonQuery或ExecuteReader

Also, as noted in another answer, the correct method to use for an Update query is ExecuteNonQuery, but also ExecuteReader can update your table but because it build an infrastructure required only when you have something to read is less efficient for an Update. In any case just use only ExecuteNonQuery or ExecuteReader

这篇关于我收到"UPDATE语句中的语法错误"；使用OleDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！