Apostrophe Error in SQL matching
Posted on 2004-08-11
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