Jul 18th, 2005, 05:45 PM
Database - How do I put the ' character into an SQL string?
The ' character is used in SQL to mark the start or end a string, so the following fails:
This is because the database engine thinks that you want the string to end after the letter O.
Where surname = 'O'Brien'
To put the ' character into an SQL string you need to use twice as many, so in this case we would add one extra, eg:
Where surname = 'O''Brien'
If you are getting the data from any source that could contain the ' character (such as input from your users) you should always use the Replace function to format the data, eg:
Dim mySQL as String
mySQL = "Select * From myTable Where surname = '" & Replace(Text1.text, "'","''") & "'"
Note however that the ' character is not the only issue, so doing this has only been a partial fix to the problem. A better solution (which deals with all of the other issues too) is to use Parameters.
For an explanation of the issues they solve and the code needed, see the FAQ article Why should I use Parameters instead of putting values into my SQL string?
Last edited by si_the_geek; Dec 30th, 2008 at 05:00 AM.
Click Here to Expand Forum to Full Width
Survey posted by VBForums.