Apostrophe Problems. 2

[gnt]

Hello-
I am trying to pull data from the database on our server using a SQL Select statement. The criteria is a string value that is input on a user form, and the results populate a list box. Simple enough. Here's a bit of my code:

Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConnect
strSQL = "SELECT acctno, name, city, country FROM CustomerVendor WHERE Name LIKE '" & strAgent & "%'"
Set objRS = objConn.Execute(strSQL)

Everything works just fine until the user attempts to search for a string that includes an apostrophe. Then , of course, it confuses the SQL statement. Any ideas on how to solve this?
Thanks!

 

[930driver]

strSQL = "SELECT acctno, name, city, country FROM CustomerVendor WHERE Name LIKE """ & strAgent & "%"""


"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[gnt]

Thanks!

 

[TonyJollans]

That will be fine until the user enters a string with a quotation mark in. For a complete solution you need to examine the string and replace every occurrence of an apostrophe with two apostrophes, something like

Dim strAgent As String
Dim strAgent2 As String

Do While InStr(strAgent, "'") > 0
    strAgent2 = strAgent2 & Left(strAgent, InStr(strAgent, "'") - 1) & "''"
    strAgent = Mid(strAgent, InStr(strAgent, "'") + 1)
Loop

strAgent2 = strAgent2 & strAgent

and then use strAgent2 in your SQL instead of strAgent.

Enjoy,
Tony

 

[gnt]

Ahhh, yes. Thanks for the foresight!