Excel，VBA：参数化查询 [英] Excel, VBA: Parametrized query

问题描述

我有一个excel vba函数从sql中提取数据，代码如下：

 函数GetDCF（vari As String）
 
 Dim rs As ADODB.Recordset 
 Set rs = New ADODB.Recordset 
 
 Dim cn As ADODB.Connection 
 Set cn = New ADODB .Connection 
 
 Dim strConn As String 
 Dim sql As String 
 
 
 strConn =PROVIDER = SQLOLEDB; DATA SOURCE = xxxxx; INITIAL CATALOG = xxxxx ; INTEGRATED SECURITY = sspi; 
 
 cn.Open strConn 
 
 
 sql =select * from table1 where COMPANY ='&范围（A2）。 '
 
 rs.Open sql，cn，adOpenDynamic，adLockOptimistic 
 
 GetDCF = rs.Fields（vari）/ 100 
 
 cn.Close 
 
设置rs =没有
设置cn =没有
 
结束功能
  

现在一切正常，除非我有一个公司，如麦克的狗，撇号防止sql查询运行良好。我想我会切换到参数化查询。但我不知道在这种情况下怎么做？

感谢任何建议！

解决方案

正如我在评论中提到的，您可以使用替换：

  sql =select * from table1 where COMPANY ='&替换（范围（A2）。值，'，'）& '
  




I have an excel vba function that pulls data from sql, the code is as below:

  Function GetDCF(vari As String)

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

  Dim cn As ADODB.Connection
     Set cn = New ADODB.Connection

       Dim strConn As String
          Dim sql As String


   strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=xxxxx;INITIAL CATALOG=xxxxx;INTEGRATED SECURITY=sspi;"

    cn.Open strConn


       sql = "select * from table1 where COMPANY = '" & Range("A2").Value & "' "

      rs.Open sql, cn, adOpenDynamic, adLockOptimistic

 GetDCF = rs.Fields(vari) / 100

cn.Close

Set rs = Nothing
Set cn = Nothing

End Function

Now everything works fine except when I have a company such as Mike's dog, the apostrophe prevent the sql query to work well. I guess I will switch to parametrized query. But I'm not sure how to do it in this case?

Thanks for any advice!

解决方案

As I mentioned in comments, you could escape single quote in your sql query using Replace:

sql = "select * from table1 where COMPANY = '" & Replace(Range("A2").Value, "'", "''") & "' "

这篇关于Excel，VBA：参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！