Hello.

I'm trying to write to an Excel file via an OleDbConnection. It always fails 
with the error "Operation must use an updateable query".
The Excel file is not read-only and the process has enough rights to write 
data to the filesystem. Additionally, reading from the file is possible but 
writing fails. This is the code:

(Additional information: C#/ASP.net 2/Office 2003)

string path = Server.MapPath("temp\\test.xls");
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
path + @";Extended Properties=""Excel 8.0;HDR=YES;""";

OleDbConnection objConn = new OleDbConnection(connectionString);

try {
    objConn.Open();

     OleDbCommand oCmd = new OleDbCommand("INSERT INTO [myWorkSheet$] 
([orderId], [hours]) VALUES (1,2)", objConn);
      int i = oCmd.ExecuteNonQuery();

      Response.Write("<br>Updated rows: " + i);

} finally {
        objConn.Close();
}

Please help. This f**** problem is driving me nuts! Should it generally be 
possible to write to Excel files in this way?

Cheers Roger

Does the ASP.NET app running user account (ASPNET,NETWORK SERVICE, or 
whatever account you impersonated to) has the write permission to the "*.xls 
file and the folder of the *.xls file?


"Roger Frei"  wrote in message 
news:udmgzKwwHHA.3588@TK2MSFTNGP06.phx.gbl...

> Hello.
>
> I'm trying to write to an Excel file via an OleDbConnection. It always 
> fails with the error "Operation must use an updateable query".
> The Excel file is not read-only and the process has enough rights to write 
> data to the filesystem. Additionally, reading from the file is possible 
> but writing fails. This is the code:
>
> (Additional information: C#/ASP.net 2/Office 2003)
>
> string path = Server.MapPath("temp\\test.xls");
> string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
> + path + @";Extended Properties=""Excel 8.0;HDR=YES;""";
>
> OleDbConnection objConn = new OleDbConnection(connectionString);
>
> try {
>    objConn.Open();
>
>     OleDbCommand oCmd = new OleDbCommand("INSERT INTO [myWorkSheet$] 
> ([orderId], [hours]) VALUES (1,2)", objConn);
>      int i = oCmd.ExecuteNonQuery();
>
>      Response.Write("<br>Updated rows: " + i);
>
> } finally {
>        objConn.Close();
> }
>
> Please help. This f**** problem is driving me nuts! Should it generally be 
> possible to write to Excel files in this way?
>
> Cheers Roger
>
>
> 

"Norman Yuan"  schrieb im Newsbeitrag 
news:e0PDnzxwHHA.1776@TK2MSFTNGP03.phx.gbl...

> Does the ASP.NET app running user account (ASPNET,NETWORK SERVICE, or 
> whatever account you impersonated to) has the write permission to the 
> "*.xls file and the folder of the *.xls file?
>


Thanks for your answer but I solved it in the meantime.. :-) I have just 
found another code part on the internet that works.. Actually, I do not 
really know why my old code didn't work.. ?!

New code that works (only with Office2003 / adjust the connection string for 
other Office Versions):

DbProviderFactory _dbFactory =
DbProviderFactories.GetFactory("System.Data.OleDb");

using (DbConnection connection = _dbFactory.CreateConnection()) {
                    connection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\inetpub\\wwwroot\\test\\temp\\mySheet.xls;Extended
Properties=\"Excel 8.0;HDR=YES;\"";
                    DbCommand c = connection.CreateCommand();

                    c.CommandText = sqlCommand;
                    c.Connection = connection;
                    int res = c.ExecuteNonQuery();
}



>
> "Roger Frei"  wrote in message 
> news:udmgzKwwHHA.3588@TK2MSFTNGP06.phx.gbl...
>> Hello.
>>
>> I'm trying to write to an Excel file via an OleDbConnection. It always 
>> fails with the error "Operation must use an updateable query".
>> The Excel file is not read-only and the process has enough rights to 
>> write data to the filesystem. Additionally, reading from the file is 
>> possible but writing fails. This is the code:
>>
>> (Additional information: C#/ASP.net 2/Office 2003)
>>
>> string path = Server.MapPath("temp\\test.xls");
>> string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data 
>> Source=" + path + @";Extended Properties=""Excel 8.0;HDR=YES;""";
>>
>> OleDbConnection objConn = new OleDbConnection(connectionString);
>>
>> try {
>>    objConn.Open();
>>
>>     OleDbCommand oCmd = new OleDbCommand("INSERT INTO [myWorkSheet$] 
>> ([orderId], [hours]) VALUES (1,2)", objConn);
>>      int i = oCmd.ExecuteNonQuery();
>>
>>      Response.Write("<br>Updated rows: " + i);
>>
>> } finally {
>>        objConn.Close();
>> }
>>
>> Please help. This f**** problem is driving me nuts! Should it generally 
>> be possible to write to Excel files in this way?
>>
>> Cheers Roger
>>
>>
>>
>
>