Syntax error (missing operator) in query expression

"Syntax error (missing operator) in query expression" is a common error when executing SQL statements such as INSERT and UPDATE. Most of the time, beginners are having a hard time to dissect which statement from query is causing the error. If we will read between the line, the runtime compiler is trying to tell us that you submitted an SQL syntax that it cannot understand. The error applies to Microsoft Access and SQL Server. On MS Access, error message is "Microsoft JET Database Engine (0x80040E14) Syntax error (missing operator) in query expression"


To troubleshoot the problem, Try the following steps:
1. CHECK if you have a complete SQL command on the query your trying to submit.

SELECT [fields] FROM [table] WHERE [expression]
INSERT INTO [table] ([field1],...) VALUES ([value1],...) WHERE [expression]
UPDATE [table] SET [field1]=[value1] WHERE [expression]

2. CHECK for extra qoutes inside the string value your trying to pass.

For example:
INSERT INTO tblCustomer(firstname,lastname) VALUES('Fryan's','Digital World')

This query will give you the "Syntax error (missing operator) in query expression" error due to the extra qoute in the second string value: 'Fryan's'

You can avoid this in .NET, by using the Replace() function before you pass the string value to complete the query as shown below.

Dim strQuery as String
Dim strFirstname as String = "Fryans's"
Dim strLastname as String = "Digital World"

strQuery = "INSERT INTO tblCustomer(firstname,lastname) VALUES('" & strFirstname.ToString.Replace("'", "") & "','" & strLastname.ToString.Replace("'", "") & "')"

** C# has the same syntax of Replace function

Remember that its the responsibility of the programmer to prevent this type of errors. We should not blame our users if they trying to input extra quotes during data entries.

For more .NET tips and tricks, subscribe now