C# OleDBCommand SQL Syntax Error

Question

I have a problem running an insert query via C# OleDbCommand to an Access backend table. The SQL runs fine if you copy it and run it directly in Access, and it works fine for other data prior to hitting this particular entry.

I have tried altering the data, with no success. The source for this is an XML file. The XML data is being loaded correctly.

The command that is failing is the dbCommand.ExecuteNonQuery() with the error "Syntax error in INSERT INTO statement".

dbCommand = new OleDbCommand(string.Format(INSERT_RECURRING, tableName, fieldList, valueList), dbCon);
dbCommand.ExecuteNonQuery();

The SQL that is trying to run and failing is below. Note that table and field names are generated from an XML source file, hence the capitalisation and underscoring.

INSERT INTO LIST_CONTACTSGP (RID, CONTACT_TYPE, SURNAME_OR_FAMILY_NAME, GIVEN_NAMES, ORGANISATION, ADDRESS_GP, PHONE, MOBILE, WORK, FAX, EMAIL, COMMENTS) VALUES ('1-8HZAZN', 'Daughter, Other Emergency Contact', 'Smith', 'Ms Jenny', '', '', '49123456', '0416123456', '', '', '', '');

As I mentioned earlier, this SQL runs without fail from within Access, and other insert commands work fine (on other tables) prior to this failure. I strip out all SQL Special characters that may cause issues.

Any help would be greatly appreciated.

As requested, copy of the Stack Trace:

System.Data.OleDb.OleDbException was unhandled
  Message=Syntax error in INSERT INTO statement.
  Source=Microsoft Office Access Database Engine
  ErrorCode=-2147217900
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at CCAPXMLImport.CXIDatabase.insertRepeatedSection(Dictionary`2 ReferralData, String ReferralID) in C:\SMNS-local\Development\TeleHealth\CCAPXMLImport\CCAPXMLImport\CCAPXMLImport\CXIDatabase.cs:line 279
       at CCAPXMLImport.frmMain.btnBrowse_Click(Object sender, EventArgs e) in C:\SMNS-local\Development\TeleHealth\CCAPXMLImport\CCAPXMLImport\CCAPXMLImport\frmMain.cs:line 131
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at CCAPXMLImport.Program.Main() in C:\SMNS-local\Development\TeleHealth\CCAPXMLImport\CCAPXMLImport\CCAPXMLImport\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Answer 1

OK, I have an answer (thanks to ralf.w). Big thanks also go to AVD and Remou for for taking the time to help with this issue.

The column names needed to be encapsulated in square brackets. Despite working fine with other tables and directly in Access, when sending it through the OleDbCommand, one (or more) of the column names must have been causing an issue.

So the SQL Code should be:

string sql="INSERT INTO LIST_CONTACTSGP ([RID], [CONTACT_TYPE], [SURNAME_OR_FAMILY_NAME], 
            [GIVEN_NAMES], [ORGANISATION], [ADDRESS_GP], [PHONE], [MOBILE], [WORK], [FAX], [EMAIL], 
            [COMMENTS]) VALUES (@RID, @CONTACT_TYPE, @SURNAME_OR_FAMILY_NAME, 
            @GIVEN_NAMES, @ORGANISATION, @ADDRESS_GP, @PHONE, @MOBILE, @WORK, @FAX, 
             @EMAIL, @COMMENTS)";

Answer 2

Use parameters instead of string concatenation.

string sql="INSERT INTO LIST_CONTACTSGP (RID, CONTACT_TYPE, SURNAME_OR_FAMILY_NAME, 
            GIVEN_NAMES, ORGANISATION, ADDRESS_GP, PHONE, MOBILE, WORK, FAX, EMAIL, 
            COMMENTS) VALUES (@RID, @CONTACT_TYPE, @SURNAME_OR_FAMILY_NAME, 
            @GIVEN_NAMES, @ORGANISATION, @ADDRESS_GP, @PHONE, @MOBILE, @WORK, @FAX, 
             @EMAIL, @COMMENTS)";

using(OleDbCommand dbCommand = new OleDbCommand(sql,conn))
{
  dbCommand.Parameters.Add("@RID",OleDbType.VarChar,20).Value="10";
  dbCommand.Parameters.Add("@CONTACT_TYPE",OleDbType.VarChar,20).Value="Somethig";
  .....
  conn.Open();
  dbCommand.ExecuteNonQuery();
  conn.Close();
}