Excel,VBA:参数化查询 [英] Excel, VBA: Parametrized query
本文介绍了Excel,VBA:参数化查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
函数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屋!
查看全文