在SQL中容纳撇号... [英] Accommodating an apostrophe in SQL...

问题描述

在使用MS Access中的VBA在SQL语句中容纳撇号时，我需要一些帮助.请帮忙.





我需要做些什么来防止它挂在撇号上?撇号需要在以下位置接受:

<br />"AND [JOB_FUNCTION]=''" & UCase(rstInputFile![JOB_FUNCTION]) & "''" <br />

[confused] [confused] [confused]

I need some assistance in accommodating an apostrophe in an SQL statement using VBA in MS Access. Please help.

<br />strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br /> "[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br /> "[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br /> "WHERE [COUNTRY]=''" & UCase(rstInputFile![COUNTRY]) & "'' " & _<br /> "AND [TYPE]=''" & UCase(rstInputFile![Type]) & "'' " & _<br /> "AND [BUSINESS_UNIT]=''" & UCase(rstInputFile![BUSINESS_UNIT]) & "'' " & _<br /> "AND [ALT_GROUPING]=''" & UCase(rstInputFile![ALT_GROUPING]) & "'' " & _<br /> "AND [PERSONNEL_AREA]=''" & UCase(rstInputFile![PERSONNEL_AREA]) & "'' " & _<br /> "AND [L_R_G]=''" & UCase(rstInputFile![L_R_G]) & "'' " & _<br /> "AND [REGION]=''" & UCase(rstInputFile![REGION]) & "'' " & _<br /> "AND [JOB_FUNCTION]=''" & UCase(rstInputFile![JOB_FUNCTION]) & "''"<br />

What do I need to do to prevent it from hanging up on the apostrophe? The apostrophe needs to be accepted in:

<br />"AND [JOB_FUNCTION]=''" & UCase(rstInputFile![JOB_FUNCTION]) & "''" <br />

[confused][confused][confused]

="div class =" ForumMod>在星期三进行了修改， 2008年12月3日，上午11:31

modified on Wednesday, December 3, 2008 11:31 AM

推荐答案

您需要用2个撇号替换撇号.最好的方法可能是通过一个函数(尽管我不确定VBA的确切语法)-类似于:

公共函数MakeSqlSafe(strData)as string
返回strData.Replace(' '''，''''")
结束函数

，然后将每个输入都通过该函数传递，例如

''& MakeSqlSafe(UCase(rstInputFile ！[REGION]))&"

希望这会有所帮助.

You need to replace the apostrophy with 2 apostrophies. Best way is probably through a function (although I''m not sure of the exact syntax for VBA) - something like :

Public Function MakeSqlSafe(strData) as string
Return strData.Replace("''", "''''")
end function

and then pass each one of your inputs through the function, e.g.

''" & MakeSqlSafe(UCase(rstInputFile![REGION])) & "''

Hope this helps.

尽管将单撇号替换为双撇号可以解决您的问题，但我认为您应该使用参数反而.串联文字字符串使您可以进行sql注入.此外，使用参数也可以提高性能.

Although replacing single apostrophe with double apostrophes will correct your problem, I think you should use parameters instead. Concatenating literal strings leaves you open to sql injections. Also using parameters gives a performance advantage.

这篇关于在SQL中容纳撇号...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！