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:

All fields in the table are Text(255) except for the Comments field which is a Memo format. I have tried changing this to Text(255) as we have had issues with the Memo fields in the past. — Gazman Development Gosford
– Gazman Development Gosford, Commented Jun 26, 2012 at 6:27
I would encapsulate the column names with [] espacially the column comments seems suspicious to me. — ralf.w.
– ralf.w., Commented Jun 26, 2012 at 6:40
Are you sure that your table permits zero-length strings? By default an Access table does not permit ZLS, so those empty strings would be a problem. ( @ralf.w. I do not see any reserved words support.microsoft.com/kb/286335 ) — Fionnuala
– Fionnuala, Commented Jun 26, 2012 at 7:31
Hi Remou, our table does allow ZLS - other queries are running before this, mostly with ZLS as values (there may occasionally be data - I will be adding code to not run an insert if there is nothing to insert later). — Gazman Development Gosford
– Gazman Development Gosford, Commented Jun 26, 2012 at 22:47
Ralf.w, I'll try encapsulating the column names, although there are previous queries running (without fail) that use a Comments column. — Gazman Development Gosford
– Gazman Development Gosford, Commented Jun 26, 2012 at 22:48

Gazman Development Gosford · Accepted Answer · 2012-06-26 22:53:33Z

5

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)";

answered Jun 26, 2012 at 22:53

Gazman Development Gosford

972 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ralf.w. Over a year ago

thanks for sharing your solution, glad to be of help. I had similar problems in the past with the OleDb/Access-SQL parsing.

Chris Patterson Over a year ago

I had the same issue. All fields worked fine except for those being saved into Access Boolean fields. Go figure. Why just those fields and why did they need brackets, when the others didn't? In any case, the brackets [], worked like a charm. Thanks!!

KV Prajapati · Accepted Answer · 2012-06-26 06:47:44Z

1

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();
}

edited Jun 26, 2012 at 6:47

answered Jun 26, 2012 at 6:31

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

4 Comments

Gazman Development Gosford Over a year ago

Thanks AVD, I'll give it a go and report back ASAP :)

Gazman Development Gosford Over a year ago

Sorry AVD, still getting syntax error on insert. I'm going to leave the parameters in there as I think this is a much better way to go.

KV Prajapati Over a year ago

@user1481804 Parameter way the cleanest and safest. You have to try it and post your code,exception trace.

Gazman Development Gosford Over a year ago

WILCO - just need to comment out my error handling. I'll do this first thing in the morning and report back. Thanks :)

Collectives™ on Stack Overflow

C# OleDBCommand SQL Syntax Error

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related