When posting records to a table using either an INSERT or UPDATE query
you may receive an error stating "Syntax error (missing operator) in query
expression". Frequently the reason for the failure is an apostrophe ('),
sometimes referred to as a single quote, in the data that is being post
to the table. For example: If someone's name entered in a text box was Bill
O'Rielly, trying to post the text directly to a database using a SQL statement
will fail. Since the apostrophe is used to denote the beginning and end
of text in a SQL statement adding an additional apostrophe ('') to the name
corrects the problem, (i.e. Bill O''Rielly). Since we can not expect users
to know they should enter double quotes instead of the correct spelling
of their name in a text field, database and web programmers need to handle
problem.
There are two ways to fix the single quote problem. If you are using
VB.NET you can simply use the VB function Replace$. You can simply search
the string and replace all of the apostrophes with two apostrophes. There
are a few draw backs to this method since it can leave your database open
to SQL injection attacks. However, if you are using ODBC database connections
this is the only method I am aware of to fix the problem.
View an Example
Using the Replace$ Function
The second way to fix the problem is to use a parameterized query. This
is best way to handle the problem because it also protects you from SQL
injection attacks. I have also tested this method with OLEDB Microsoft Access
database connections and ASP.NET 4.5.
View a
Basic Example of using a Parameterized Query