Operation must use an updateable query when updating excel sheet

Question

I am getting this error when trying to update excel sheet :

Server Error in '/ReadExcelData_Csharp' Application.
Operation must use an updateable query.

and here is the code that i am using :

  querys = "UPDATE [Sheet1$] "+"SET [Number]=" +s.Trim()+ " WHERE [Number]=" + s2.Trim() ;
  objcmc = new OleDbCommand(querys, conn);
  objcmc.ExecuteNonQuery();

any help will be appreciated .

and here is the connection i used :

if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

What connection string did you use to create the conn object? — barrowc
– barrowc, Commented Oct 1, 2011 at 20:29
if (strFileType.Trim() == ".xls") { connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (strFileType.Trim() == ".xlsx") { connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } — Eslam Soliman
– Eslam Soliman, Commented Oct 4, 2011 at 19:35
It's worth checking the Mode property of the conn object after it has been opened. Compare the value to the list at msdn.microsoft.com/en-us/library/ms675792%28v=VS.85%29.aspx to be sure that the connection is in the correct mode — barrowc
– barrowc, Commented Oct 4, 2011 at 22:52

competent_tech · Accepted Answer · 2011-11-12 01:30:52Z

30

Remove the IMEX=2 (or IMEX=1) from the connection string and it will work. I have tested this crazy solution several times and removing the IMEX for some strange reason seems to do the trick (at least for xlsx files).

The following code works:

    static void Main(string[] args)
    {
        string connectionString  = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + "d:\\temp\\customers.xlsx" + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";
        string selectString = "INSERT INTO [Customers$](Id,Company) VALUES('12345', 'Acme Inc')";

        OleDbConnection con = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand(selectString, con);

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine("Success");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            con.Dispose();
        }
        Console.ReadLine();
    }
}

Thanks to RobertNet from social.msdn.microsoft.com

edited Nov 12, 2011 at 1:30

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

answered Nov 12, 2011 at 1:06

ItsAllABadJoke

3163 silver badges3 bronze badges

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

4 Comments

Eslam Soliman Over a year ago

it works with .xlsx but not with .xls i think that there is another solution but thanks it is an internal sys so we are all using new .xlsx extintion version :)

Aditya Bokade Over a year ago

What a solution man! past two days i was going mad cuz of this issue...thanks a ton!!!

arpan shah Over a year ago

Is it supposed to take a lot of time..I have loop which runs 50 time and update FirstName based on diff StudentId. It takes 4-5 mins to process and the gives a success message. But when i try to open the .xls file its says "this file may be corrupted located on a server that is not responding or read-only" and i can access the file after like 20-30 mins and even the date modified time changes after 20-30 mins

Merav Kochavi Over a year ago

This solution did not work because with my data base I actually needed the IMEX - because my column had mixed data types, some double values some string values. When I remove IMEX =1, I get the a runtime exception "Unable to cast object of type" because it automatically selects the type based on the most popular value type and then fails to cast the unselected type.

Merav Kochavi · Accepted Answer · 2015-11-01 14:46:00Z

I used the solution provided above and removed the IMEX=2 or IMEX=1 string from the connection string. But this was not enough. In my case, the solution needed an additional work around.

But in my data base I actually needed the IMEX - because my column had mixed data types, some double values some string values. When I remove IMEX =1, I get the a runtime exception "Unable to cast object of type" because it automatically selects the column data type based on the most popular value in the column and then fails to cast the values which are not of the selected type.

I worked around this issue by changing my double and int values to string values (added a ' in the beginning of the cell value manually in excel) and removed the IMEX from the connection string. and then this solved the issue.

Hamid · Accepted Answer · 2017-04-07 18:43:34Z

0

In my case,I changed ConnectionString and then this solved the issue.
I removed ReadOnly=False;HDR=Yes; parametes in connectionString

string _connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;\";", pathToExcelFile);

answered Apr 7, 2017 at 18:43

Hamid

1,5632 gold badges21 silver badges33 bronze badges

Comments

user3385405 · Accepted Answer · 2023-03-08 19:37:21Z

0

My issue was a previous connection locked the excel file into readonly mode. Needed to kill the connection.

answered Mar 8, 2023 at 19:37

user3385405

213 bronze badges

Collectives™ on Stack Overflow

Operation must use an updateable query when updating excel sheet

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related