none
Unable to create table to a excel work sheet. "Cannot modify the design of table 'TestSheet'. It is in a read-only database"... RRS feed

  • Question

  • Hey Experts,

    I am trying to create a table in my Winform using the following lines of code, but am unable to do so:

    using (OleDbConnection excelConnection = new OleDbConnection(connectionString))
    {
    excelConnection.Open();

    OleDbCommand excelCmd = new OleDbCommand();

    excelCmd.Connection = excelConnection;
    excelCmd.CommandText = "CREATE TABLE TestSheet (Auto_Number char(255), .......)";


    excelCmd.ExecuteNonQuery();//This particular statement will rise the above exception.

    }

    The following are a few details of the exception:

    System.Data.OleDb.OleDbException {"Cannot modify the design of table 'TestSheet'. It is in a read-only database."}

    I have even checked the Properties of my database and the "Read-only" option is unchecked.

    Looking forward to hearing from you,

    Thanks,
    Abhisheik.


    Tuesday, October 16, 2007 1:30 AM

All replies

  • Tuesday, October 16, 2007 2:27 AM
    Moderator
  • Is the Excel file local or on a network resource?

     

    Tuesday, October 16, 2007 1:11 PM
  • Thanks a lot for replying to my query. Here are the details:

     VMazur wrote:

    Check if it is not a permissions issue, as described here

    http://databases.aspfaq.com/database/how-do-i-solve-operation-must-use-an-updateable-query-errors.html



    I have checked it, its not a persmissions issue.

     Paul P Clement IV wrote:
    Is the Excel file local or on a network resource?

    The file is a local one. I have created the file using the windows explorer.



    To give you guys a better idea as to what I would exactly want to do:

    The user will actually select multiple rows from a DataGridView control on a windows form. Based on the rows selected I would build a DataSet/DataTable. This new DataSet/DataTable is what I would want to save in a new work sheet of an already existing local Excel file.

    For doing this I have to first create a empty work sheet using the "CREATE TABLE " query and would then have to insert the items row-by-row into the sheet using the "INSERT INTO " query.

    I was wondering if you guys have a better or a more cleaner way to doing the same. I mean I basically have to insert the whole DataSet/DataTable into a new WorkSheet whose name will also be provided by the user. I would also want to be able to give the user the choice of Exporting the same to a MS Access database later on.

    Thanks,
    Abhisheik.


    Tuesday, October 16, 2007 8:30 PM
  • Hey Experts,

     

    Can somebody please help me out with this issue.

     

    Thanks,

    Abhisheik.

    Wednesday, October 17, 2007 9:09 PM
  • I'll see if I can reproduce the problem you are encountering. I'm assuming that none of the file protection features were enabled for the document using Microsoft Excel.

    Thursday, October 18, 2007 2:26 AM
  • Paul,

     

    Yes none of the file protection features where enabled on the file.

     

    Hope you come up with a solution.

     

    Thanks,

    Abhisheik.

     

    Thursday, October 18, 2007 6:51 AM
  • Add an extension (xls) to your file so it looks like:

    CREATE TABLE TestSheet.xls (Auto_Number char(255), .......)";

    thanks

    Shafeeq
    S. Weera
    Friday, February 13, 2009 7:40 AM
  • Do we need to add same for drop table test.xls

    Ramu Gade

    Thursday, December 18, 2014 6:20 AM
  • Do we need to add same for drop table test.xls

    Ramu Gade


    Old thread, but FYI you can't use DROP TABLE to delete a Worksheet from Excel. It will simply delete the contents of the Worksheet.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, December 23, 2014 6:22 PM