locked
SELECT ...FROM [Hoja1$] works fine in Microsoft.Jet.OLEDB.4.0 but not in Microsoft.ACE.OLEDB.12 RRS feed

  • Question

  • I'm in front of an X-File ;-)

    This is the query:

    SELECT EM_CODI, EM_NOMB,
    IIF(EM_CODI='90000001', '@error', '@'+Right(CStr(EM_CODI),7)) AS EM_TARJ_mod, EM_FALT, EM_FBAJ, EM_CEMP, IIf(LCase(EM_CACC)='general','100',IIf(LCase(EM_CACC)='acceso servidor','200','000')) AS EM_CACC_mod, EM_CCAL, EM_SECC, EM_AREA
    FROM [Hoja1$] ORDER BY EM_NOMB

    It works fine under this environment:

    • Vista 32 bits
    • an ASP.NET app
    • using DbCommand.ExecuteReader method of C#3.0
    • using this connection string: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IMPORTACIONES\excel_manipulado.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

    I'm getting an exception under these environments:

    • Win 7 64 bits (using a pure pool of 64bits)
    • Win XP 32 bits
    • same ASP.NET app
    • same DbCommand.ExecuteReader method of C#3.0
    • using this connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\IMPORTACIONES\excel_manipulado.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

    This is the exception at ExecuteReader method:

    {System.Data.OleDb.OleDbException: Error de sintaxis (falta operador) en la expresión de consulta 'IIf(LCase(EM_CACC)=''general'',''100'',IIf(LCase(EM_CACC)=''acceso servidor'',''200'',''000''))'.
       en System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       en System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       en System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       en System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       en System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       en System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
       en Evalos.Importaciones.xmlV2.SQL.run() en C:\...\Expresiones\SQL.cs:línea 58}

    ErrorCode = -2147217900

    (Error de sintaxis (falta operador) en la expresión de consulta = Syntax error (operator missing) at query expression)

    I have linked the XLS file in a ACCDB and the qery is complety valid and return my rows.

    I will appreciate suggestions.

    Cheers

    Tuesday, October 19, 2010 12:54 PM

Answers

All replies

  • I would try changing the connection string (Excel version) first to see if it makes any difference. It should be 12.0 for the ACE OLEDB Provider:

    http://www.connectionstrings.com/excel-2007


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Tuesday, October 26, 2010 8:35 AM
    Tuesday, October 19, 2010 2:30 PM
  • Hello Josep,

    In addition to Paul's suggestion, please switch to x86 platform for your project when you work in a 64bit environment. By the way, would you please translate the error message into English?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Thursday, October 21, 2010 8:45 AM