Apostrophe Error in SQL matching

I have used code similar to the following to search for matching records in a database, and it works well, providing a narrowing list of matches as the 'Search' texts gets longer.

Variables FtExt1 and FtExt2 are the contents of the two Search text boxes, and the code is called to update the Recordset after every change in either text box.

The problem occurs when the Search text includes an APOSTROPHE eg O'Carrol. This is because SQL uses the apostrophe for its' own purposes, and this extra apostrophe destroys the logic of the SQL.

If the user types an Apostrophe, the program crashed, until I added code to trap this character


    SQL = "Select [ID], [Title], [Name], Age FROM Entrants WHERE ([Title] like '" & FtExt1 & "' OR [Title] is Null) AND ([Name] like '" & FtExt2 & "') ORDER BY 3,2;"
    MatchingRs.Open SQL, SMConn, adOpenKeyset, adLockReadOnly

This problem is not a large one, but does restrict the scope of my 'Find' routine to some extent.

Is there a way to include the apostrophe in the SQL without crashing it

thanks

nedwob
nedwobAsked:
Who is Participating?
 
leonstrykerCommented:
You may want to use a function to do this to all of your SQl string before passing them to the database.  Here is sample function which does it:

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_20602507.html

Leon
0
 
Éric MoreauSenior .Net ConsultantCommented:
You have to double it:

 SQL = "Select [ID], [Title], [Name], Age FROM Entrants WHERE ([Title] like '" & replace(FtExt1, "'","''") & "' OR [Title] is Null) AND ([Name] like '" & replace(FtExt2,"'", "''") & "') ORDER BY 3,2;"
0
 
bkthompson2112Commented:
Hi nedwob,

Use 2 apostrophes.  ''
When the user enters an apostrophe add another to the search string.

bkt
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PePiCommented:
use replace like so:


SQL = "Select [ID], [Title], [Name], Age FROM Entrants WHERE ([Title] like '" & Replace(FtExt1,"'","''") & "' OR [Title] is Null) AND ([Name] like '" & Replace(FtExt2,"'","''") & "') ORDER BY 3,2;"


in the replace function, the second parameter is a double quote, single quote, double quote
the third parameter is a double quote,  a single quote, another single quote then a double quote.

it's very hard to see the difference of these single & double quotes just by looking at it. hope this helps
0
 
mladenoviczCommented:
Public Function m_PrepareQueryParams(str As String) As String
Dim sRes As String
   
    sRes = str
   
    sRes = Replace(sRes, "[", "[[]")
    sRes = Replace(sRes, "*", "[*]")
    sRes = Replace(sRes, "?", "[?]")
    sRes = Replace(sRes, "#", "[#]")
    sRes = Replace(sRes, "%", "[%]")
    sRes = Replace(sRes, "_", "[_]")
    sRes = Replace(sRes, "'", "''")
   
    m_PrepareQueryParams = sRes
   
End Function
0
 
nedwobAuthor Commented:
Thanks for the comments. I will look at them and come back to award the points in a day or two.

nedwob
0
 
JR2003Commented:
I use a function called DBStr and call it with every string I put into some sql.
Just paste the function into a module and call it from all the literals you put in sql.
It works a treat...

'Example Usage:
===========

 SQL = "Select [ID], [Title], [Name], Age FROM Entrants WHERE ([Title] like '" & DBStr(FtExt1) & "' OR [Title] is Null) AND ([Name] like '" & DBStr(FtExt2) & "') ORDER BY 3,2;"



'Function:
'======

Public Function DBStr(sIn As String) As String

    Dim tmp As String
    Dim i As Long
    Dim j As Long
   
    If Len(sIn) <> 0 Then
        j = 1
        Do
            i = InStr(j, sIn, "'")
            If i = 0 Then Exit Do
            tmp = tmp & Mid$(sIn, j, i - j) & "''"
            j = i + 1
        Loop
        DBStr = tmp & Mid(sIn, j)
    End If
   
End Function

0
 
nedwobAuthor Commented:
Thanks to all. I have increased the points and will split according to the input or usefulness to me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.