.net - VFP OleDb 的 Sql 参数化语法错误

Question

我正在尝试为 DBF 文件(Visual Fox Pro)创建 SQL 参数化更新命令
我不知道为什么，但我在“DbCommand.ExecuteNonQuery();”上有一个“语法错误”。

异常错误消息是“语法错误”。我没有任何附加信息。

string maRequete = "UPDATE "  + strNomTable + " set " 
+ "evetype = @evetype ,"
+ "evedes = @evedes ,"
+ "evecli = @evecli ,"
+ "eveusermo = @eveusermo ,"
+ "eveinterv = @eveinterv where eveNum = '"  + strEvtNumeroString.ToString() + "'";

OleDbCommand DbCommand = new OleDbCommand(maRequete);

DbCommand.Parameters.Add("@evetype", OleDbType.VarChar);
DbCommand.Parameters.Add("@evedes", OleDbType.VarChar);
DbCommand.Parameters.Add("@evecli", OleDbType.VarChar);
DbCommand.Parameters.Add("@eveusermo", OleDbType.VarChar);
DbCommand.Parameters.Add("@eveinterv", OleDbType.VarChar);


DbCommand.Parameters["@evetype"].Value = m_strEvtType.ToString().Trim();
DbCommand.Parameters["@evedes"].Value = m_strDesignation.ToString().Trim();
DbCommand.Parameters["@evecli"].Value = m_strCodeClient.ToString().Trim();
DbCommand.Parameters["@eveusermo"].Value = m_strUserModification;
DbCommand.Parameters["@eveinterv"].Value = m_strCodeIntervenant.ToString().Trim();


try
{
    string strStringConnect = @"Provider=vfpoledb.1;Data Source=" + m_strDirectoryDBF + @"\" + strDbfFile + ".dbf;Collating Sequence=general";
    OleDbConnection DbConnection = new OleDbConnection(strStringConnect);

    DbCommand.CommandType = System.Data.CommandType.Text;

    DbConnection.Open();
    DbCommand.Connection = DbConnection;

    DbCommand.ExecuteNonQuery();
    return "O";
}
catch (Exception Ex)
{
    return Ex.Message;
}

我尝试为“where”语句使用参数，但没有任何变化:(

有人有想法吗？

非常感谢 :)

Answer 1

VFP 不支持带有@ 符号的“命名”参数。相反，只需更改为“？”它充当参数的占位符。然后，确保您的参数以与它们在 sql 命令中出现的完全相同的顺序添加。你很亲密。

您甚至可以在 WHERE 子句中为 eveNum 值使用参数。

string maRequete = "UPDATE "  + strNomTable + " set " 
+ "evetype = ?,"
+ "evedes = ?,"
+ "evecli = ?,"
+ "eveusermo = ?,"
+ "eveinterv = ? where eveNum = ?";

OleDbCommand DbCommand = new OleDbCommand(maRequete);

DbCommand.Parameters.Add("Varevetype", OleDbType.VarChar);
DbCommand.Parameters.Add("Varevedes", OleDbType.VarChar);
DbCommand.Parameters.Add("Varevecli", OleDbType.VarChar);
DbCommand.Parameters.Add("Vareveusermo", OleDbType.VarChar);
DbCommand.Parameters.Add("Vareveinterv", OleDbType.VarChar);
DbCommand.Parameters.Add("VarWhere", OleDbType.VarChar);


DbCommand.Parameters["Varevetype"].Value = m_strEvtType.ToString().Trim();
DbCommand.Parameters["Varevedes"].Value = m_strDesignation.ToString().Trim();
DbCommand.Parameters["Varevecli"].Value = m_strCodeClient.ToString().Trim();
DbCommand.Parameters["Vareveusermo"].Value = m_strUserModification;
DbCommand.Parameters["Vareveinterv"].Value = m_strCodeIntervenant.ToString().Trim();
DbCommand.Parameters["VarWhere"].Value = strEvtNumeroString.ToString().Trim();