Example of Using the Replace$ Function to Fix SQL Syntax Errors

Using the VB.NET Replace$ function is a simple way to fix the "Syntax error (missing operator) in query expression" when updating or inserting records to a database. This method searches for all instance of the single quote and replaces them with two single quotes.

Replace$(stringtosearch, stringtofind, replacementstring [, start[, count[, compare]]])

Required
stringtosearch  The sting that you want to test for an apostrophe.
stringtofind  The string that you are looking for.
replacementstring  The string that you want to replace stringtofind with.
Optional
start  The position within stringtosearch where the search is to begin. If omitted, position 1 is assumed.
count  The number of times to perform the substitutions. If omitted, the default value is 1, which means make all possible substitutions.
compare  The numeric value indicating the kind of comparison to use when evaluating stringtofind. (0 = case sensitive, 1 = case-insensitive)

This is an example of to call this function when building a SQL statement. Note the extra space before the double quotes at the end of each line except the last. They are required so the SQL statement string is properly formed.

 

If you have any comments, questions, or suggestions please fell free to contact us.