vb.net 转义sql语句中的保留关键字 [英] vb.net escape reserved keywords in sql statement

问题描述

我正在尝试在 vb.net 中对 Access 数据库执行 sql 语句，我正在使用方括号 [] 转义保留字.这适用于我在程序中的所有 SELECT 语句.

I'm trying to execute an sql statement in vb.net to an Access database, I am escaping the reserverd word using square brackets []. This has worked in all my SELECT statements within the program.

保留字为level

但是下面的 SQL 语句失败了

But the SQL statement below fails saying

INSERT INTO 语句中的语法错误.

Syntax error in INSERT INTO statement.

如果我直接复制语句并在 Access 中执行它，它工作正常

If I directly copy the statement and execute this in Access it works fine

datalayer.getDataTable(String.Format("INSERT INTO users (username, password, [level]) VALUES ({0}, {1}, {2})", username, password, level))

如果我删除 [level] 并将其替换为另一个列名，则上述语句有效.

The above statement works if I remove [level] and replace this with another column name.

谢谢.

推荐答案

您没有将值括在引号中.试试这个:

You're not enclosing your values in quotation marks. Try this:

datalayer.getDataTable(String.Format(
 "INSERT INTO users (username, password, [level]) VALUES ('{0}', '{1}', '{2}')", 
 username, password, level))

然而，正如安德鲁所说，你应该真正使用参数.在 Access SQL (David W Fenton 很快就会出现并说它是Jet"SQL)中，您必须使用位置参数.您的语句将如下所示:

However, as Andrew says, you should really use parameters. In Access SQL (David W Fenton will come along shortly and say it's "Jet" SQL) you have to use positional parameters. Your statement would then look like this:

INSERT INTO users (username, password, [level]) VALUES (?, ?, ?)

您需要使用正确的值创建 OleDbParameter 对象，并创建一个带有上述文本的 OleDbCommand 以执行插入操作.

You'd need to create OleDbParameter objects with the correct values, and an OleDbCommand with the text above to execute in order to do your insert.

这篇关于vb.net 转义sql语句中的保留关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！