Excel Vba - 在 sql 字符串中使用撇号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11796973/
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
Excel Vba - Using an apostrophe in an sql string
提问by Reznor
I'm using excel to input data into an Access database and some of my data strings contain an apostrophe for measurements.
我正在使用 excel 将数据输入到 Access 数据库中，并且我的一些数据字符串包含用于测量的撇号。
This is my SQL input string
这是我的 SQL 输入字符串
stSQL = "INSERT INTO Products (ProductName, ProductDescription, ProductUnit, SupplierID) " & _
"Values ('" & cboxItemNum & "', '" & txtDescription & "', '" & txtUnit & "', " & linkPID & ")"
cn.Execute (stSQL)
My string is as follows:
我的字符串如下：
Aliplast 4E White. 30" X 80' X 1/4" Soft.
Aliplast 4E 白色。30" X 80' X 1/4" 软。
In this string the ' after the 80 is causing the error and I'm not sure how to get around this. I can't just tell the user not to enter an apostrophe. How can I get around this?
在这个字符串中，80 之后的 ' 导致了错误，我不知道如何解决这个问题。我不能只是告诉用户不要输入撇号。我怎样才能解决这个问题？
Thanks
谢谢
回答by Fionnuala
You can correct this either by using parameters (recommended) or by using Replace.
您可以通过使用参数（推荐）或使用替换来更正此问题。
& Replace(txtDescription,"'","''") &
Parameters
参数
Dim cmd As New ADODB.command
cn.Open ServerConnect
cmd.ActiveConnection = cn
stSQL = "INSERT INTO Products (ProductName, " _
& "ProductDescription, ProductUnit, SupplierID) " _
& "Values (param1,param2,param3,param4)"
cmd.CommandText = stSQL
cmd.CommandType = adCmdText
With cmd
.Parameters.Append .CreateParameter( _
"param1", adInteger, adParamInput, , cboxItemNum)
.Parameters.Append .CreateParameter( _
"param2", adVarChar, adParamInput, 50, txtDescription )
.Parameters.Append .CreateParameter( _
"param3", adInteger, adParamInput, , txtUnit )
.Parameters.Append .CreateParameter( _
"param4", adInteger, adParamInput, , linkPID )
End with
cmd.Execute recs
Note that while I have named these parameters param1 to param4, that is for my convenience, all that matters is the order, which must match the order in which the parameters are to be used.
请注意，虽然我将这些参数命名为 param1 到 param4，但为了方便起见，重要的是顺序，它必须与使用参数的顺序相匹配。
回答by Jonathan
Replace the apostrophe in values that are encased in single quotes (e.g. the O'Brien in 'O'Brien') as follows: O' & "'" & 'Brien
将单引号括起来的值中的撇号（例如 'O'Brien' 中的 O'Brien）替换如下：O' & "'" & 'Brien
Use the following code snippet: Replace(rs![field1], " ' ", " ' " & " & " & """" & " ' " & """" & " & ' ") & "'" ). NOTE I have added a space between the single and double quotes to make it easier to tell them apart. In the actual code you use, you should not have these spaces.
使用以下代码片段： Replace(rs![field1], " ' ", " ' " & " & " & """" & " ' " & """" & " & ' ") & "'" ) . 注意我在单引号和双引号之间添加了一个空格，以便更容易区分它们。在您使用的实际代码中，不应有这些空格。
For example
例如
Instead of
代替
Docmd.RunSQL ("INSERT INTO Tablename (DestinationField) SELECT '" & rs![field1] & "'")
Docmd.RunSQL ("INSERT INTO Tablename (DestinationField) SELECT '" & rs![field1] & "'")
use
用
Docmd.RunSQL ("INSERT INTO Tablename (DestinationField) SELECT '" & Replace(rs![field1], "'", "'" & " & " & """" & "'" & """" & " & '") & "'" )
Docmd.RunSQL ("INSERT INTO Tablename (DestinationField) SELECT '" & Replace(rs![field1], "'", "'" & " & " & """" & "'" & """" & " & '") & "'" )
This worked for me and allowed me to use VBA to insert values containing apostrophes (single quote marks) into a table using SQL insert statements
这对我有用，并允许我使用 VBA 使用 SQL 插入语句将包含撇号（单引号）的值插入到表中
