McGrath Electronics, Inc. Provides Network Support in Phoenix, AZ
McGrath Electronics, Inc. Provides Network Design for the 21st Century

Function to Parse Strings for SQL Statements

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