Excel Vba-在SQL字符串中使用撇号 [英] Excel Vba - Using an apostrophe in an sql string

问题描述

我正在使用excel将数据输入到Access数据库中，并且我的一些数据字符串包含用于测量的撇号.

I'm using excel to input data into an Access database and some of my data strings contain an apostrophe for measurements.

这是我的SQL输入字符串

This is my SQL input string

    stSQL = "INSERT INTO Products (ProductName, ProductDescription, ProductUnit, SupplierID) " & _
            "Values ('" & cboxItemNum & "', '" & txtDescription & "', '" & txtUnit & "', " & linkPID & ")"

    cn.Execute (stSQL)

我的字符串如下:

Aliplast 4E白色. 30"X 80'X 1/4"软.

Aliplast 4E White. 30" X 80' X 1/4" Soft.

在此字符串中，'80后的'导致错误，我不确定如何解决此问题.我不能只是告诉用户不要输入撇号.我该如何解决?

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?

谢谢

推荐答案

您可以使用参数(推荐)或使用替换"来更正此问题.

You can correct this either by using parameters (recommended) or by using Replace.

& Replace(txtDescription,"'","''") & 

参数

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

请注意，尽管我已将这些参数命名为param1到param4，但重要的是顺序，该顺序必须与参数的使用顺序匹配.

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.

这篇关于Excel Vba-在SQL字符串中使用撇号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！