如何向OleDbDataWriter添加参数

Question

当我试图使用ADO.NET写入excel文件时，会出现语法错误。如何向查询中添加参数。我正在更新mysql数据库。

string error="Text for status";
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";

System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection(connectionString);
string ExcelQuery;

ExcelQuery = "Update [Sheet1$] set Status="+error; // from Sheet1";

//Create the command to be executed
ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection);

//Open the connection to the file
ExcelConnection.Open();

//Execute the update
ExcelCommand.ExecuteNonQuery();

//Close the connection
ExcelConnection.Close();

查询表达式“状态的文本”中的语法错误(缺少运算符)。

Answer 1

可以使用OleDbCommand.Parameters.Add向命令添加参数。

public void CreateMyOleDbCommand(OleDbConnection connection,
    string queryString, OleDbParameter[] parameters) 
{
    OleDbCommand command = new OleDbCommand(queryString, connection);
    command.CommandText = 
        "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?";
    command.Parameters.Add(parameters);

    for (int j=0; j<parameters.Length; j++)
    {
        command.Parameters.Add(parameters[j]) ;
    }

    string message = "";
    for (int i = 0; i < command.Parameters.Count; i++) 
    {
        message += command.Parameters[i].ToString() + "\n";
    }
    MessageBox.Show(message);
}

Answer 2

看起来您在'语句中丢失了UPDATE。

ExcelQuery = "Update [Sheet1$] set [Status]='" + error + "'";