Access OLEDB 报告“语法错误"；但声明看起来是正确的 [英] Access OLEDB reports "syntax error" but the statement looks correct

问题描述

我正在 ASP.net 中编写一个简单的页面来更新 Access 表中的密码.以下是有效的 SELECT 查询的语法:

I'm writing a simple page in ASP.net to update a password in a Access table. Here's syntax for a SELECT query that works:

    Dim dbConn As OleDbConnection
    Dim dbCommand As OleDbCommand
    Dim dbReader As OleDbDataReader

    'Connect to db
    dbConn = New OleDbConnection(Application("strConnectionString"))
    dbConn.Open()

    'Get user info
    strSQL = "SELECT * FROM users WHERE Username = '" & strUsername & "';"
    dbCommand = New OleDbCommand(strSQL, dbConn)
    dbReader = dbCommand.ExecuteReader()

还有我的连接字符串:

    Application("strConnectionString") = _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & _
            "; Jet OLEDB:Database Password=" & strDBPassword & ";"

SELECT 工作正常，所以我知道我的连接正常.但是这个查询给了我一个语法错误:

The SELECT works fine, so I know my connection is OK. But this query gives me a syntax error:

strSQL = "UPDATE users SET Password = '1'"

在其他一切都相同的情况下，ASP 错误表明我的语法有错误.但是当我 response.write strSQL 行时，它给了我这个:

With everything else the same, the ASP error says there is an error with my syntax. But when I response.write the strSQL line, it gives me this:

UPDATE users SET Password = '1' 

当我将其粘贴到 Access 中的查询编辑器中时，查询会将表中的所有密码"字段更新为1"，所以我知道语法是可以的.我在没有数据读取器并使用 dbCommand.ExecuteNonQuery() 的情况下进行了尝试，结果相同.

and when I paste this into a query editor in Access, the query updates all 'Password' field in the table to '1', so I know the syntax is OK. I tried it without the datareader and using dbCommand.ExecuteNonQuery(), same result.

我已经获得了 Access 文件集的权限，因此每个人都可以完全控制，所以我认为这不是权限问题.

I've got the permissions on the Access file set so that everybody has full control, so I don't think it's a permission problem.

谁能看到我的错误?我真的被困住了.谢谢.

Can anybody see my mistake? I'm really stuck. Thanks.

推荐答案

PASSWORD 是 Access SQL 中的保留字，因此如果它是列名，则需要将其括在方括号中.

PASSWORD is a reserved word in Access SQL so you need to enclose it in square brackets if it is a column name.

strSQL = "UPDATE users SET [Password] = '1'" 

这篇关于Access OLEDB 报告“语法错误"；但声明看起来是正确的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！