操作更新Excel工作表时,必须使用一个可更新的查询 [英] Operation must use an updateable query when updating excel sheet
问题描述
我在尝试更新Excel工作表时,收到此错误:
在'/ ReadExcelData_Csharp应用程序的服务器错误。
操作必须使用一个可更新的查询。
和这里是code,我现在用:
querys =UPDATE [工作表Sheet1 $]+SET [数字] =+ s.Trim()+WHERE [数字] =+ s2.Trim();
objcmc =新的OleDbCommand(querys,康涅狄格州);
objcmc.ExecuteNonQuery();
任何帮助将AP preciated。
和这里是我使用的连接:
如果(strFileType.Trim()==的.xls)
{
CONNSTRING =供应商= Microsoft.Jet.OLEDB.4.0;数据源=+ strNewPath +;扩展属性= \\Excel中8.0; HDR =是; IMEX = 2 \\;
}
否则,如果(strFileType.Trim()==的.xlsx)
{
CONNSTRING =供应商= Microsoft.ACE.OLEDB.12.0;数据源=+ strNewPath +;扩展属性= \\Excel的12.0; HDR =是; IMEX = 2 \\;
}
从连接字符串中删除IMEX = 2(或IMEX = 1),它会工作。我测试了这个疯狂的解决方案好几次,一些奇怪的原因去除IMEX似乎这样的伎俩(至少XLSX文件)。
以下code工作:
静态无效的主要(字串[] args)
{
字符串的connectionString =供应商= Microsoft.ACE.OLEDB.12.0;数据源=+D:\\\\ \\\\温度customers.xlsx+;扩展属性= \\Excel的12.0;只读= FALSE; HDR =是; \\;
字符串selectString =INSERT INTO [客户$](ID,公司)VALUES('12345','阿克米公司'); OleDbConnection的CON =新的OleDbConnection(的connectionString);
OleDbCommand的CMD =新的OleDbCommand(selectString,CON); 尝试
{
con.Open();
cmd.ExecuteNonQuery();
Console.WriteLine(成功);
}
赶上(异常前)
{
Console.WriteLine(ex.Message);
}
最后
{
con.Dispose();
}
到Console.ReadLine();
}
}
从<一个感谢RobertNet href=\"http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/e0f65510-2164-4d8a-a70e-631ac6a30f84/\">social.msdn.microsoft.com
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\"";
}
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
这篇关于操作更新Excel工作表时,必须使用一个可更新的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!