Excel, VBA: Parametrized query

Question

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!

You should escape single quote '. Change Range("A2").Value to Replace(Range("A2").Value, "'", "''"). — Dmitry Pavliv
– Dmitry Pavliv, Commented Apr 16, 2014 at 18:29

Dmitry Pavliv · Accepted Answer · 2014-04-16 18:37:41Z

2

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, "'", "''") & "' "

answered Apr 16, 2014 at 18:37

Dmitry Pavliv

35.9k13 gold badges85 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel, VBA: Parametrized query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related