StrataFrame Forum

excel help give OleDbException Syntax error in INSERT INTO statement

http://forum.strataframe.net/Topic27119.aspx

By Jocelyn Dagrain - 5/11/2010

Bonjour when I try to export using the excel helper I recieved this error message

OleDbException
  Syntax error in INSERT INTO statement.

Source     : Microsoft JET Database Engine

NB. the excel file is created but with only the header

Thank you

By Greg McGuffey - 5/12/2010

Could provide some more information? SQL statement example...stack trace....
By Jocelyn Dagrain - 5/12/2010

Bonjour here is the full error message thank you

System.Data.OleDb.OleDbException was unhandled by user code
  Message="Syntax error in INSERT INTO statement."
  Source="Microsoft JET 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 MicroFour.StrataFrame.Tools.ExcelHelper.ExportToExcel_AddRows(OleDbConnection connection, BusinessLayer businessObject)
       at MicroFour.StrataFrame.Tools.ExcelHelper.ExportToExcel(BusinessLayer businessObject, String outputPathAndFileName)
       at JDSolutions.Sys.HomePage.toolStripButtonExcel_Click(Object sender, EventArgs e) in C:\Users\dagrainj.AIM\Documents\JDS\JDSolutions\JDSolutions\Sys\HomePage.cs:line 90
       at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
       at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
       at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ToolStrip.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.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
  InnerException:

By Bill Cunnien - 5/13/2010

Hi Jocelyn,



Thanks for the stack trace! Would you be able to provide the code on and around HomePage.cs:line 90? It seems odd to me that the export is running an INSERT INTO statement against your database. Do you know where the INSERT INTO is coming from? I have never used the ExportToExcel method in the StrataFrame Tools namespace. Sounds interesting.



Bill
By Edhy Rijo - 5/13/2010

Hi Jocelyn,



Don't know if this is relevant or no, but if you are testing on a 64bit OS it could be possible that the OleDB driver being used is just for 32bit OS, if that is the case you will need to compile your project specifically for x86 instead of "AnyCPU" the default value and test again.
By Jocelyn Dagrain - 5/13/2010

Bonjour
I use this code

 //-----Employee Export excel --------------------
        private void toolStripButtonExcel_Click(object sender, EventArgs e)
        {
            //-- Export the BO to an excel document
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                MicroFour.StrataFrame.Tools.ExcelHelper.ExportToExcel(entityBO1, saveFileDialog1.FileName);
            }
        }

-My OS is window 7 in 32bit
-My project is using platform target x86
-My version of Jet engin is:
HKEY_LOCAL_MACHINE,
    "Software\\Microsoft\\Jet\\4.0\\Engines
within the Engines folder I also have Jet 2.x/Jet 3.x/Jet 4.0

Thsnk you

By Greg McGuffey - 5/14/2010

With a SQL syntax error, my first guess would be that one of you column names is causing an issue.



If that isn't the problem, then the best way to get to the bottom of this is for you create a small sample app that demonstrates the problem.
By Bill Cunnien - 5/14/2010

I looked at the ExportToExcel_AddRow method in the source code. It uses a StringBuilder to put together the syntax involved in the create row method. It is basically a SQL INSERT INTO command. The column names are surrounded with brackets, so hopefully, that would take care of that problem; however, there may be an issue with invalid string data within the business object. Here is the syntax for a string field:



createRow.Append("'" + bo.CurrentRow[col.ColumnName].ToString().Replace("'","") + "'");



createRow is the StringBuilder object. Maybe the Replace method should read: .Replace("'", "''"). Not sure how to solve the problem. Check your string data in the business object and see if anything stands out.
By Bill Cunnien - 5/14/2010

Peering into the code a little more, I recognized something quite interesting. The only column types handled by the export are int, long, datetime and string. If there is anything else in that business object outside of those types, then they are considered strings. You may have to excluded those BLOB-type columns from your export.
By Jocelyn Dagrain - 5/14/2010

Bonjour after investigation I found this list of error and possible solutions:

- The error message (Syntax error in INSERT INTO statement  Microsoft JET Database Engine) was cause by a null value in my table.

Solution:  I make sure there is no null value in the table

- The error message (Numeric field overflow  Microsoft JET Database Engine) is cause by the default date time value (1800-01-01 00:00:00.000)

Partial Solution: modify the sql statement  [convert(varchar(10), Tbl_CreatedDate, 101) as Tbl_CreatedDate]

This eliminate the error but in excel the date is format as string this can be problematic for accounting user who rely on excel built-in function to do their job.

More on this error message:  Miscrosoft Article ID: 815277

This article is base on MS Access scenario but I think it might apply

Link: http://support.microsoft.com/kb/815277

Access assigns a data type for each field of the Excel spreadsheet. This assignment is based on the data that is contained in the first eight rows. For example, if a field has a Number data type that is in the first eight rows and then has text values in some of the remaining rows, Access assigns the Number data type to the link table field. This causes Access to fail to link the records that have text data values. When you query this field, Microsoft Jet Database Engine encounters text where a number is expected. The query fails with the error message in the "Symptoms" section.

- On last error message this time manageable by error handling (The process cannot access the file 'C:\Users\dagrainj.AIM\Downloads\tset2.XLS' because it is being used by another process.)

this can occur if the excel file is save in a share directory (other user might cause this error simply by opening the file)

Thank you in advance