How to deal with single quote in Word VBA SQL query?

Question

Hi,

I get a customer name from dropdown and use that value to query an excel spreadsheet, however, the name can contain a single quote (example: Adam's Meat). This breaks my application and how do I make a query with a variable that contains a single quote?

Private Sub cboCompany_Change()
            Dim customerName As String
            customerName = cboCompany.Value

rsT.Open "SELECT Customer, Postcode, Address1, Address2, State, Country FROM Customers WHERE  Customer = '" & customerName & "'", cn, adOpenStatic

Thanks in advance.

Answer 1

Where you specify two single quotes '', one will escape the other and will result in single, try to replace it like this:

customerName = Replace(customerName, "'", "''")

Answer 2

This leaves you wide open to an SQL injection attack. I would recommend changing this to a parameterised query like this

Dim cmd as NEW ADODB.Command

With cmd
 .CommandText=”SELECT foo from tblBar where foo=?”
 .Parameters.Append .CreateParameter("@foo", adVarChar, adParamInput, 50, “What ever you want”)
 .ActiveConnection=dbCon
 .CommandType=adCmdText
End With

Set rst=cmd.execute