OLEDB单引号错误

Question

可能重复： OLEDB，编写Excel单元格时不带撇号

我用的是这个代码：

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\test_excel.xls';Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;

sql = "CREATE TABLE EmployeeData (Id char(255), Name char(255), BirthDate date)";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();

sql = "INSERT INTO EmployeeData (Id, Name, BirthDate) values ('AAA', 'Andrew', '12/4/1955')";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();

MyConnection.Close();

代码工作，但我的问题是单引号(见下图)

怎么修？

问题http://www.freeimagehosting.net/newuploads/e32f9.jpg示例

Answer 1

您应该始终使用参数化查询。他们是安全的注入，特殊的字符，并使您的代码更加可读性。

OleDbCommand cm = MyConnection.CreateCommand();
cm.CommandText = "INSERT INTO EmployeeData (Id, Name, BirthDate) values (@Id, @Name, @BirthDate)";
cm.Parameters.AddWithValue("@Id", "AAA");
cm.Parameters.AddWithValue("@Name", "'Andrew");
cm.Parameters.AddWithValue("@BirthDate", new DateTime(1955, 4, 12));
cm.ExecuteNonQuery();

Answer 2

你有没有试过改变

('AAA', 'Andrew', '12/4/1955') 

转到

("AAA", "Andrew", "12/4/1955")