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.