查询不会与变量一起运行，在粘贴变量的定义时会起作用

This is a Query in VBA (Access 2007) I have 3 strings defined:

这是VBA中的查询(Access 2007)我定义了3个字符串:

str_a = "db.col1 = 5"
str_b = " and db.col2 = 123"
str_c = " and db.col3 = 42"

Then I use these in the WHERE part of my Query:

然后我在我的Query的WHERE部分使用这些:

"WHERE '" & str_a & "' '" & str_b & "' '" & str_c & "' ;"

This fails, but If I paste in the strings like this:

这失败了,但如果我像这样粘贴字符串:

"WHERE db.col1 = 5 and db.col2 = 123 and db.col3 = 42;"

It works perfectly. I'm guessing the syntax is wrong when using multiple variables in a string.
Anyone have any hints?

它完美地运作。我猜测在字符串中使用多个变量时语法错误。有人有任何提示吗?

7 个解决方案

#1

"WHERE '" & str_a & "' '" & str_b & "' '" & str_c & "' ;"

will include single quotes within your completed WHERE clause. And the working version you showed us has none:

将在您完成的WHERE子句中包含单引号。你向我们展示的工作版本没有:

"WHERE db.col1 = 5 and db.col2 = 123 and db.col3 = 42;"

So, try constructing the WHERE clause with no single quotes:

因此,尝试构造没有单引号的WHERE子句:

"WHERE " & str_a & " " & str_b & " " & str_c & ";"

For debugging purposes, it's useful to view the final string after VBA has finished constructing it. If you're storing the string in a variable named strSQL, you can use:

出于调试目的,在VBA完成构造之后查看最终字符串很有用。如果要将字符串存储在名为strSQL的变量中,则可以使用:

Debug.Print strSQL

to display the finished string in the Immediate Window of the VB Editor. (You can get to the Immediate Window with the CTRL+g keyboard shortcut.)

在VB编辑器的立即窗口中显示已完成的字符串。 (您可以使用CTRL + g键盘快捷键进入立即窗口。)

Alternatively, you could display the finished string in a message box window:

或者,您可以在消息框窗口中显示已完成的字符串:

MsgBox strSQL

#2

You've got some extra single quotes in there.

你在那里有一些额外的单引号。

Try this:

"WHERE " & str_a & str_b  & str_c

Note: In general, you shouldn't build query strings by concatenating strings, because this leaves you vulnerable to SQL injection, and mishandles special characters. A better solution is to use prepared statements. But assuming you're operating in a very controlled environment the solution I gave should work.

注意:通常,您不应通过连接字符串来构建查询字符串,因为这会使您容易受到SQL注入,并且错误处理特殊字符。更好的解决方案是使用预准备语句。但假设您在一个非常受控制的环境中运行,我提供的解决方案应该可行。

#3

Quick tip about troubleshooting SQL that is built dynamically: echo the SQL string resulting from all the concatenation and interpolation, instead of staring at your code.

关于对动态构建的SQL进行故障排除的快速提示:回显所有连接和插值产生的SQL字符串,而不是盯着您的代码。

WHERE 'db.col1 = 5' ' and db.col2 = 123' ' and db.col3 = 42';

Nine times out of ten, the problem becomes a lot more clear.

十分之九,问题变得更加清晰。

#4

For VB6/VBA dynamic SQL, I always find it more readable to create an SQL template, and then use the Replace() function to add in the dynamic parts. Try this out:

对于VB6 / VBA动态SQL,我总是发现创建SQL模板更具可读性,然后使用Replace()函数添加动态部分。试试这个:

Dim sql As String
Dim condition1 As String
Dim condition2 As String
Dim condition3 As String

sql = "SELECT db.col1, db.col2, db.col3 FROM db WHERE <condition1> AND <condition2> AND <condition3>;"

condition1 = "db.col1 = 5"
condition2 = "db.col2 = 123"
condition3 = "db.col3 = 'ABCXYZ'"

sql = Replace(sql, "<condition1>", condition1)
sql = Replace(sql, "<condition2>", condition2)
sql = Replace(sql, "<condition3>", condition3)

However, in this case, the values in the WHERE clause would change, not the fields themselves, so you could rewrite this as:

但是,在这种情况下,WHERE子句中的值将更改,而不是字段本身,因此您可以将其重写为:

Dim sql As String

sql = "SELECT col1, col2, col3 FROM db "
sql = sql & "WHERE col1 = <condition1> AND col2 = <condition2> AND col3 = '<condition3>';"

sql = Replace(sql, "<condition1>", txtCol1.Text)
sql = Replace(sql, "<condition2>", txtCol2.Text)
sql = Replace(sql, "<condition3>", txtCol3.Text)

#5

Some comments on constructing WHERE clauses in VBA.

关于在VBA中构造WHERE子句的一些注释。

Your example is by definition going to be incorrect, because you're putting single quotes where they aren't needed. This:

根据定义,您的示例将是不正确的,因为您将单引号放在不需要它们的位置。这个:

str_a = "db.col1 = 5"
str_b = " and db.col2 = 123"
str_c = " and db.col3 = 42"
"WHERE '" & str_a & "' '" & str_b & "' '" & str_c & "' ;"

...will produce this result:

......会产生这样的结果:

WHERE 'db.col1 = 5' ' and db.col2 = 123' ' and db.col3 = 42' ;

This is obviously not going to work.

这显然不会起作用。

Take the single quotes out and it should work.

拿单引号,它应该工作。

Now, that said, I'd never do it that way. I'd never put the AND in the substrings that are used to construct the WHERE clause, because what would I do if I have a value for the second string but not for the first?

现在,那说,我永远不会这样做。我永远不会将AND放在用于构造WHERE子句的子串中,因为如果我有第二个字符串的值而不是第一个字符串的值,我会怎么做?

When you have to concatenate a number of strings with a delimiter and some can be unassigned, one thing to do is to just concatenate them all and not worry if the string before the concatenation is unassigned of not:

当你必须用分隔符连接多个字符串而一些字符串可以被取消分配时,要做的一件事就是将它们全部连接起来,如果串联之前的字符串未被赋值,则不必担心:

str_a = "db.col1 = 5"
str_b = "db.col2 = 123"
str_c = "db.col3 = 42"

To concatenate that, you'd do:

要连接它,你会做:

If Len(str_a) > 0 Then
   strWhere = strWhere & " AND " str_a
End If
If Len(str_b) > 0 Then
   strWhere = strWhere & " AND " str_b
End If
If Len(str_c) > 0 Then
   strWhere = strWhere & " AND " str_c
End If

When all three strings are assigned, that would give you:

分配所有三个字符串后,这将为您提供:

" AND db.col1 = 5 AND db.col2 = 123 AND db.col3 = 42"

Just use Mid() to chop of the first 5 characters and it will always come out correct, regardless of which of the variables have values assigned:

只需使用Mid()来切割前5个字符,无论哪个变量都赋值,它总是正确的:

strWhere = Mid(strWhere, 6)

If none of them are assigned, you'll get a zero-length string, which is what you want. If any one of them is assigned, you'll first get " AND ...", which is an erroneous leading operator, which you just chop out with the Mid() command. This works because you know that all the results before the Mid() will start with " AND " no matter what -- no needless tests for whether or not strWhere already has been assigned a value -- just stick the AND in there and chop it off at the end.

如果没有分配它们,您将获得零长度字符串,这是您想要的。如果分配了其中任何一个,您将首先得到“AND ...”,这是一个错误的前导运算符,您只需使用Mid()命令进行切断即可。这是有效的,因为你知道Mid()之前的所有结果都将以“AND”开头,无论如何 - 没有不必要的测试是否已经为strWhere分配了一个值 - 只需将AND粘在那里并将其切断在最后。

On another note, someone mentioned SQL injection. In regards to Access, there was a lengthy discussion of that which considers a lot of issues close to this thread:

另一方面,有人提到SQL注入。关于Access,有一个冗长的讨论,它考虑了这个线程附近的许多问题:

Non-Web SQL Injection

非Web SQL注入

#6

I have my favorite "addANDclause" function, with the following parameters:

我有我最喜欢的“addANDclause”函数,具有以下参数:

public addANDclause( _ 
    m_originalQuery as string, _
    m_newClause as string) _
as string

if m_originalQuery doe not contains the WHERE keyword then addANDClause() will return the original query with a " WHERE " added to it.

如果m_originalQuery不包含WHERE关键字,则addANDClause()将返回添加了“WHERE”的原始查询。

if m_orginalQuery already contains the WHERE keyword then addANDClause() will return the original query with a " AND " added to it.

如果m_orginalQuery已包含WHERE关键字,则addANDClause()将返回添加了“AND”的原始查询。

So I can add as many "AND" clauses as possible. With your example, I would write the following to create my SQL query on the fly:

所以我可以添加尽可能多的“AND”子句。在您的示例中,我将编写以下内容以动态创建我的SQL查询:

m_SQLquery = "SELECT db.* FROM db"
m_SQLquery = addANDClause(m_SQLQuery, "db.col1 = 5")
m_SQLQuery = addANDClause(m_SQLQuery, "db.col2 = 123")
m_SQLQuery = addANDClause(m_SQLQuery, "db.col3 = 42")

Of course, instead of these fixed values, such a function can pick up values available in bound or unbound form controls to build recordset filters on the fly. It is also possible to send parameters such as:

当然,代替这些固定值,这样的函数可以获取绑定或未绑定表单控件中可用的值,以便动态构建记录集过滤器。也可以发送参数,例如:

m_SQLQuery = addANDClause(m_SQLQuery, "db.text1 like 'abc*'")

#7

While dynamic SQL can be more efficient for the engine, some of the comments here seem to endorse my view that dynamic SQL can be confusing to the human reader, especially when they didn't write the code (think of the person who will inherit your code).

虽然动态SQL对于引擎来说可以更有效,但是这里的一些注释似乎支持我的观点,即动态SQL可能会让人类读者感到困惑,特别是当他们没有编写代码时(想想将继承你的人)码)。

I prefer static SQL in a PROCEDURE and make the call to the proc dynamic at runtime by choosing appropriate values; if you use SQL DDL (example below) to define the proc you can specify DEFAULT values (e.g. NULL) for the parameters so the caller can simply omit the ones that are not needed e.g. see if you can follow the logic in this proc:

我更喜欢PROCEDURE中的静态SQL,并通过选择适当的值在运行时调用proc动态;如果你使用SQL DDL(下面的例子)来定义proc,你可以为参数指定DEFAULT值(例如NULL),这样调用者可以简单地省略那些不需要的值。看看你是否可以遵循这个过程中的逻辑:

CREATE PROCEDURE MyProc
(
   arg_col1 INTEGER = NULL, 
   arg_col2 INTEGER = NULL, 
   arg_col3 INTEGER = NULL
)
AS 
SELECT col1, col2, col3
  FROM db 
 WHERE col1 = IIF(arg_col1 IS NULL, col1, arg_col1) 
       AND col2 = IIF(arg_col2 IS NULL, col2, arg_col2) 
       AND col3 = IIF(arg_col3 IS NULL, col3, arg_col3);

Sure, it may not yield the best execution plan but IMO you have to balance optimization against good design principles (and it runs really quick on my machine :)

当然,它可能不会产生最好的执行计划,但IMO你必须平衡优化与良好的设计原则(它在我的机器上运行得非常快:)

#1

"WHERE '" & str_a & "' '" & str_b & "' '" & str_c & "' ;"