VB.net 访问更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17925364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VB.net Access Update Query
提问by Justin E.
VB.net access update query is giving a Syntax Error in Update Query Error. My query is as follows:
VB.net 访问更新查询在更新查询错误中给出语法错误。我的查询如下：
query = "UPDATE users SET username='" & newUsername & "', password='" & newPassword & "', department='" & newDepartment & "', display_name='" & newDisplayName & "', email='" & newEmail & "', extension='" & newExtension & "', access_level='" & newAccessLevel & "' WHERE id=" & usrID
None of the above variables have any symbols at all. What am I doing wrong?
上述变量都没有任何符号。我究竟做错了什么？
::UPDATE::
：：更新：：
UPDATE users SET username='alison', password='farm1234',department='1',display_name='Alison *****', email='production@**********.com', extension='1012',access_level='50' WHERE id=1
This is what the query runs as.
这就是查询运行的方式。
回答by Steve
The error is caused by the usage of the reserved keyword PASSWORD without enclosing it in square brackets.
该错误是由使用保留关键字 PASSWORD 而不将其括在方括号中引起的。
Said that, you never use string concatenation to build sql commands, but always a parameterized query to avoid Sql Injection problems but also syntax error in parsing text values (containing single quotes) or decimal values with their decimal separators or dates values.
就是说，您从不使用字符串连接来构建 sql 命令，而是始终使用参数化查询来避免 Sql 注入问题，而且在解析文本值（包含单引号）或十进制值及其小数分隔符或日期值时也会出现语法错误。
So, a possible approach to your task could be
因此，您的任务的一种可能方法可能是
query = "UPDATE users SET username=?, [password]=?, department=?, " & _
"display_name=?, email=?, extension=?, access_level=?" & _
" WHERE id=?"
Using cmd = new OleDbCommand(query, connection)
cmd.Parameters.AddWithValue("@p1", newUsername)
cmd.Parameters.AddWithValue("@p2", newPassword)
cmd.Parameters.AddWithValue("@p3", newDepartment)
cmd.Parameters.AddWithValue("@p4", newDisplayName)
cmd.Parameters.AddWithValue("@p5", newEmail)
cmd.Parameters.AddWithValue("@p6", newExtension)
cmd.Parameters.AddWithValue("@p7", newAccessLevel)
cmd.Parameters.AddWithValue("@p8", usrID)
cmd.ExecuteNonQuery()
End Using
Keep in mind that OleDb doesn't use the parameter names to find the corresponding placeholder in sql command text. Instead it uses a positional progression and thus adding the parameters to the collection should respect the order in which the parameter appears in the sql command text
请记住，OleDb 不使用参数名称在 sql 命令文本中查找相应的占位符。相反，它使用位置级数，因此将参数添加到集合中应遵循参数在 sql 命令文本中出现的顺序
回答by sk2185
ConStr()
Qry="UPDATE users SET username=@uname, [password]=@pass, department=@dept, " & _
"display_name=@dnam, email=@email, extension=@ext, access_level=@acslvl" & _
" WHERE id=@id"
cmd = new oledbcommand(Qry,Conn)
cmd.Parameters.AddWithValue("@uname",newUsername)
cmd.Parameters.AddWithValue("@pass",newPassword)
cmd.Parameters.AddWithValue("@dept",newDepartment)
cmd.Parameters.AddWithValue("@dnam",newDisplayName)
cmd.Parameters.AddWithValue("@email",newEmail)
cmd.Parameters.AddWithValue("@ext",newExtension)
cmd.Parameters.AddWithValue("@acslvl",newAccessLevel)
cmd.Parameters.AddWithValue("@id",usrID)
cmd.ExecuteNonQuery()
