none
Error in creating an Excel workbook RRS feed

  • Question

  • I have a connection string like:

          string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                       pathToFile +
                       ";";
          connectionString += "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
    
    

    And I am trying to create the file and the sheets. At first I tried to create a blank work book (the file path does not exist) and I get the error:

    System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
      at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
      at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
      at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
      at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
      at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
      at System.Data.OleDb.OleDbConnection.Open()
      at AutoMerchandise.CandidateService.ReadCandidateList() in C:\Projects\BuySeasonsIT\Source\AutoMerchandise\MainWindow.xaml.cs:line 760
    A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in AutoMerchandise.exe
    A first chance exception of type 'System.Reflection.TargetInvocationException' occurred in mscorlib.dll
    System.Windows.Data Error: 34 : ObjectDataProvider: Failure trying to invoke method on type; Method='GetCandidateList'; Type='CandidateService'; Error='<null>' TargetInvocationException:'System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
      at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
      at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
      at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
      at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
      at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
      at System.Data.OleDb.OleDbConnection.Open()
      at AutoMerchandise.CandidateService.ReadCandidateList() in C:\Projects\BuySeasonsIT\Source\AutoMerchandise\MainWindow.xaml.cs:line 783
      at AutoMerchandise.CandidateService.GetCandidateList() in C:\Projects\BuySeasonsIT\Source\AutoMerchandise\MainWindow.xaml.cs:line 843
      --- End of inner exception stack trace ---
      at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
      at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
      at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
      at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
      at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
      at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
      at System.Windows.Data.ObjectDataProvider.InvokeMethodOnInstance(Exception& e)'
    A first chance exception of type 'System.Reflection.TargetInvocationException' occurred in PresentationFramework.dll
    

    If I first create the spreadsheet (a blank one) using Excel using the following code:

              using (OleDbConnection candidateDebugConnection =
                       new OleDbConnection(ExcelConnectionString("candidate.xlsx")))
              {
                using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE [BCAmazon](product CHAR(64),variant CHAR(64),oid INT,category CHAR(255))", candidateDebugConnection))
                {
                  if (candidateDebugConnection.State != ConnectionState.Open) candidateDebugConnection.Open();
                  cmd.ExecuteNonQuery();
                }
                using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE CEAmazon(product CHAR(64),variant CHAR(64),oid INT,category CHAR(255))", candidateDebugConnection))
                {
                  if (candidateDebugConnection.State != ConnectionState.Open) candidateDebugConnection.Open();
                  cmd.ExecuteNonQuery();
                }
                using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE BCBuy(product CHAR(64),variant CHAR(64),oid INT,category CHAR(255))", candidateDebugConnection))
                {
                  if (candidateDebugConnection.State != ConnectionState.Open) candidateDebugConnection.Open();
                  cmd.ExecuteNonQuery();
                }
                using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE CEBuy(product CHAR(64),variant CHAR(64),oid INT,category CHAR(255))", candidateDebugConnection))
                {
                  if (candidateDebugConnection.State != ConnectionState.Open) candidateDebugConnection.Open();
                  cmd.ExecuteNonQuery();
                }
              }
            }
            catch (Exception ex)
            {
              Debug.WriteLine(ex.ToString());
              throw;
            }
    
    

    Then I get:

    System.Data.OleDb.OleDbException: Cannot modify the design of table 'BCAmazon'. It is in a read-only database.
      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 AutoMerchandise.CandidateService.ReadCandidateList() in C:\Projects\BuySeasonsIT\Source\AutoMerchandise\MainWindow.xaml.cs:line 761
    

    So in summary I get an unknown error when the file doesn't exist and I get a 'read-only' error when I first create a blank workbook (.xlsx). Is what I want to do is create a workbook with multiple sheets and write to those sheets if the workbook doesn't exist and overwrite it if it does. Of course I also don't want the 'read-only' exception. Any ideas?

    Thank you.

    Kevin

     


    Kevin Burton
    • Moved by Bessie Zhao Monday, November 29, 2010 6:13 AM (From:Excel for Developers)
    Monday, November 29, 2010 1:58 AM

Answers

  • It looks like you need to remove the IMEX parameter from the connection string when creating the Excel Workbook. I was able to repro the error using your connection string, but after removing this argument it worked fine.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by KevinBurton Thursday, December 2, 2010 3:16 PM
    Thursday, December 2, 2010 1:42 PM

All replies

  • Hello Kevin,

    Thanks for posting. Since this issue is more an issue related to ADO.NET, I will move this thread from Excel forum to ADO.NET Managed Providers forum for more support. Have a nice day.

    Best regards,
    Bessie Zhao - MSFT
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, November 29, 2010 6:12 AM
  • Kevin,

    What I would suggest to do is to check first if file exists in a folder using File or FileInfo .NET Framework class and delete the file (since you need to override it). Then create the file as you do it right now and close connection, so the file is really created. Then re-open the file and work with the data.

    If you need simpler way to export data into excel file without using Jet, you can also try .NET component from my web side.


    Val Mazur (MVP) http://www.xporttools.net
    Monday, November 29, 2010 11:39 AM
    Moderator
  • That is the problem. If the file doesn't exist then I get an error (details previously) in trying to create the file. I created a blank workbook using Excel just as a test (but ended up getting another read-only error). I don't want to have to create the file using Excel. Even if I do I have to overcome the read-only error.

    Kevin


    Kevin Burton
    Monday, November 29, 2010 11:59 AM
  • Hello,

    Is the ExcelLibrary what you want?


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    • Edited by liurong luo Tuesday, November 30, 2010 10:40 AM link
    Tuesday, November 30, 2010 10:39 AM
  • Roahn,

    Thank you for your response.

    Is this not possible with OleDB (Managed Provider)? I hate to bring another library and dependency into the project if at all possible.

    Kevin


    Kevin Burton
    Tuesday, November 30, 2010 3:02 PM
  • Hello,

     

    Yes, it’s possible. Pleases take a look at this KB article which demonstrated with detail steps and sample code:
    http://support.microsoft.com/kb/316934

    code sample:
          '==========================================================================

          ' Create a workbook with a table named EmployeeData. The table has 3

          ' fields: ID (char 255), Name (char 255) and Birthdate (date). 

          '==========================================================================

          Dim conn As New OleDbConnection()

          conn.ConnectionString = m_sConn1

          conn.Open()

          Dim cmd1 As New OleDbCommand()

          cmd1.Connection = conn

          cmd1.CommandText = "CREATE TABLE EmployeeData (Id char(255), Name char(255), BirthDate date)"

          cmd1.ExecuteNonQuery()


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Wednesday, December 1, 2010 7:45 AM
  • Not sure that your code is much different than mine, but I responded to similar question about a week ago:

    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/d0be9748-1b99-4b4f-b8a0-bb8bfa808c93/

    I would make certain that the unknown/read-only errors aren't related to permissions for the folder. I don't know which version of Windows you are using but you might want to try a path to a Documents folder to see if the same issues occur.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, December 1, 2010 2:34 PM
  • So what do you suspect I am doing wrong or what is wrong with my set up. I have looked at the code and it seems that it is the same as that posted.

            try
            {
              using (OleDbConnection candidateDebugConnection =
                       new OleDbConnection(ExcelConnectionString("candidate.xlsx")))
              {
                using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE [BCAmazon](product CHAR(64),variant CHAR(64),oid INT,category CHAR(255))", candidateDebugConnection))
                {
                }
                  if (candidateDebugConnection.State != ConnectionState.Open) candidateDebugConnection.Open();
                  cmd.ExecuteNonQuery();
              }
            }
            catch (Exception ex)
            {
              Debug.WriteLine(ex.ToString());
              throw;
            }
    
    

    The connection string that I am using is

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=candidate.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"

    But I get the exception on the Open() statement:

    System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
      at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
      at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
      at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
      at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
      at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
      at System.Data.OleDb.OleDbConnection.Open()
      at AutoMerchandise.CandidateService.ReadCandidateList() in C:\Projects\BuySeasonsIT\Source\AutoMerchandise\MainWindow.xaml.cs:line 973
    

    Kevin

     

     


    Kevin Burton
    Wednesday, December 1, 2010 2:38 PM
  • I changed the path to explicitly specify C:/TEMP which I have had not problem writing to in the past. I still get the same error. The connection string looks like

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/TEMP/candidate.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"

    The error description is "The Microsoft Access database engine could not find the object 'C:\\TEMP\\candidate.xlsx'. Make sure the object exists and that you spell its name and the path name correctly. If 'C:\\TEMP\\candidate.xlsx' is not a local object, check your network connection or contact the server administrator.". Of course the file doesn't exist I want to create it.

    I am runnining Windows 2003 SP1 Server.

    Kevin


    Kevin Burton
    • Edited by KevinBurton Wednesday, December 1, 2010 6:16 PM Update
    Wednesday, December 1, 2010 6:11 PM
  • It looks like you need to remove the IMEX parameter from the connection string when creating the Excel Workbook. I was able to repro the error using your connection string, but after removing this argument it worked fine.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by KevinBurton Thursday, December 2, 2010 3:16 PM
    Thursday, December 2, 2010 1:42 PM