Writing to Excel File via OleDB

[Roger Frei]

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]

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]

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

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