Operation must use an updateable query when updating excel sheet

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"";
            }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

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.

Method 3

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);


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x