Updating cells in Excel via OleDB

[Peter Cushing via .NET 247]

I've an Excel file that I've been trying to make editable by Webusers via ASP.NET - however I just can't make the update syntaxto work. It's not an ASP problem, I've copied the code into anWindows application, and it still generates a "Syntax error inupdate statement" exception. The excel file has two columns,one with "Key" in cell A1, the other with "Value" in cell "B1". Column A is defined to be "Key", column B is defined to be"Value". C

string excelDocPath2 ="C:\\inetpub\\wwwroot\\ExcelData1.xls";
System.Data.OleDb.OleDbConnection objConn= newSystem.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelDocPath2 + ";Extended Properties=\"Excel8.0;HDR=YES;\"");



objConn.Open();

OleDbCommand objCmdUpdate = new OleDbCommand("UPDATE [Sheet1$]SET Value = '40' WHERE Key = '2'");


OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objCmdUpdate.Connection=objConn;
objAdapter1.InsertCommand = objCmdUpdate;

try
{
objCmdUpdate.ExecuteNonQuery();
}
finally
{
objConn.Close();
}

 

[W.G. Ryan eMVP]

This should help you -
http://www.c-sharpcorner.com/Code/2004/June/AccessExcelDb.asp (Essentially
naming the range) -but if I may ask, what purpose is the Adapter serving
here? Doesn't look like it's being used in this context at all.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
I've an Excel file that I've been trying to make editable by Web users via
ASP.NET - however I just can't make the update syntax to work. It's not an
ASP problem, I've copied the code into an Windows application, and it still
generates a "Syntax error in update statement" exception. The excel file
has two columns, one with "Key" in cell A1, the other with "Value" in cell
"B1". Column A is defined to be "Key", column B is defined to be "Value".
C

string excelDocPath2 =
"C:\\inetpub\\wwwroot\\ExcelData1.xls";
System.Data.OleDb.OleDbConnection objConn = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + excelDocPath2 + ";Extended Properties=\"Excel 8.0;HDR=YES;\"");



objConn.Open();

OleDbCommand objCmdUpdate = new OleDbCommand("UPDATE [Sheet1$] SET Value =
'40' WHERE Key = '2'");


OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objCmdUpdate.Connection=objConn;
objAdapter1.InsertCommand = objCmdUpdate;

try
{
objCmdUpdate.ExecuteNonQuery();
}
finally
{
objConn.Close();
}