CODEWITHSUNDEEP
sqlsql-serverexcelvba
Codelinsky
Codelinsky

Reputation: 17

Running a sql select command with a vba string variable that contains an apostrophe

Using VBA, I'm trying to select data from a SQL table, but am running into an issue because the Customer name I'm looking up contains an apostrophe (McDonald's). Instead of using a replace function, I'm trying to setup customerName as a parameter to avoid this issue.

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim cmd As New ADODB.Command
        customerName = .Range("customerName")

        queryCustomers = "select * from Customers where CustomerName = '" & customerName & "'"

        'Open a connection to SQL Server
        conn.Open cnstring

        With cmd
         .CommandText = queryCustomers
         .Parameters.Append .CreateParameter("@param1", adVarChar, adParamInput, 50, customerName)
         .ActiveConnection = conn
         .CommandType = adCmdText
        End With

        Set rs = cmd.Execute

I'm getting the same error because of the apostrophe which means the parameter setup I'm using is incorrect. In summary, I need help to make my queryCustomers string pass customerName as a parameter so the apostrophe in customerName does not affect the SQL command.

Upvotes: 0

Views: 616

Answers (1)

Matus
Matus

Reputation: 181

Your 

queryCustomers = "select * from Customers where CustomerName = '" & customerName & "'"

gets evaluated to 

"select * from Customers where CustomerName = 'McDonald's'"

There is no variable where the value can be placed. This is what is executed

Try something like this

cmd.CommandText = "select * from Customers where CustomerName = @Value1"
Dim param1 As ADODB.Parameter
Set param1 = cmd.CreateParameter("@Value1", adVarWChar, adParamInput, 8, "McDonald's")
cmd.Parameters.Append param1

This approach should escape your McDonald's -> McDonald''s automatically.

Upvotes: 1

Related Questions