参数化SQL UPDATE问题...... [英] Parameterized SQL UPDATE issues...

问题描述

大家好：


我遇到了其中一个我无法解决的问题，无论如何！在我的代码中，我有一个SQL UPDATE，如：

Hi all:

I''ve got one of those problems that I just can''t get around, no matter what! In my code, I''ve got a SQL UPDATE like:

展开 | 选择 | Wrap | 行号

推荐答案

你好。 ERN字段的参数似乎是一个字符串，但在WHERE子句中（上面第一个代码块中的第13行），您不是在引号内引用它。它将被解释为好像它是一个数字，除非你把它放在引号中 - 你在其他WHERE中做了它工作正常（最后一个代码发布）！


我不确定您正在使用哪个后端应用程序（所有@s都参与SQL），我也不确定您使用的是Access VBA而不是VB，比如构建字符串。假设您正在Access VBA或VB中构建SQL字符串，请尝试使用

Hi. The parameter for the ERN field appears to be a string, but in your WHERE clause (line 13 in first code block above) you are not referring to it within quotes. It will be interpreted as if it were a number unless you put it in quotes - which you did do in the other WHERE that works fine (last code posted)!

I am not sure which back-end application you are using (with all the @s involved in the SQL), nor do I know for certain that you are using Access VBA and not VB, say, to build the string. Assuming you are building the SQL string in Access VBA or VB please try replacing your WHERE line with

展开 | 选择 | Wrap < span class =codeDivider> | 行号

嗨。 ERN字段的参数似乎是一个字符串，但在WHERE子句中（上面第一个代码块中的第13行），您不是在引号内引用它。它将被解释为好像它是一个数字，除非你把它放在引号中 - 你在其他WHERE中做了它工作正常（最后一个代码发布）！


我不确定您正在使用哪个后端应用程序（所有@s都参与SQL），我也不确定您使用的是Access VBA而不是VB，比如构建字符串。假设您正在Access VBA或VB中构建SQL字符串，请尝试使用

Hi. The parameter for the ERN field appears to be a string, but in your WHERE clause (line 13 in first code block above) you are not referring to it within quotes. It will be interpreted as if it were a number unless you put it in quotes - which you did do in the other WHERE that works fine (last code posted)!

I am not sure which back-end application you are using (with all the @s involved in the SQL), nor do I know for certain that you are using Access VBA and not VB, say, to build the string. Assuming you are building the SQL string in Access VBA or VB please try replacing your WHERE line with

展开 | 选择 | Wrap < span class =codeDivider> | 行号

嗨。无论参数化如何，您仍然需要在SQL中用引号分隔字符串。为什么？因为否则字符串将在参数替换后被解释为这样（使用虚拟值进行说明）


WHERE fldERN = ABCDEF12345


而不是


WHERE fldERN =''ABCDEF12345''


这又将设置SQL解释器寻找名为ABCDEF12345的字段或其他找到它的价值 - 而且它不会成功。


它在单引号中的另一个WHERE子句中为你工作但没有参数的事实是一个确定的错误信号。


至于编译错误，我无法猜到那个，但我相信如果你能解决这个问题，你将解决手头的问题。


-Stewart

Hi. Regardless of parameterisation you will still need to delimit a string within the SQL in quotes. Why? Because otherwise the string would be interpreted like this after parameter substitution (using a dummy value for illustration)

WHERE fldERN = ABCDEF12345

instead of

WHERE fldERN = ''ABCDEF12345''

and this in turn will set the SQL interpreter looking for a field named ABCDEF12345 or whatever to find its value - and it will not succeed.

The fact that it worked for you in the other WHERE clause within the single quotes but without the parameter is a sure sign of what is wrong.

As for the compile error I cannot guess at that one, but I am sure that if you can resolve this you will resolve the issue at hand.

-Stewart

这篇关于参数化SQL UPDATE问题......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！