VFP OleDb的SQL参数化语法错误 [英] Sql Parametrized syntax error with VFP OleDb

问题描述

我正在尝试对DBF文件(Visual Fox Pro)进行SQL参数化的更新命令 我不知道为什么，但是"DbCommand.ExecuteNonQuery();"上出现语法错误".

I'm trying to make an SQL parametrized update command for a DBF File ( Visual Fox Pro ) I don't know why, but i have a "syntax error" on the "DbCommand.ExecuteNonQuery();".

异常错误消息是语法错误".我没有其他信息.

The exception error message is "Syntax Error.". I don't have any additionnal informations.

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"语句中使用参数，并且没有任何变化:(

I tried to use a parameter for the "where" statement, and there is no changes :(

请问有人有想法吗?

非常感谢:)

推荐答案

VFP不支持带有@符号的命名"参数.相反，只需更改为?"充当参数的占位符.然后，确保以与sql命令中显示的完全相同的顺序添加参数.您非常亲密.

VFP does not support "named" parameters with the @ symbol. Instead, just change to "?" which acts as a place-holder for parameters. Then, make sure your parameters are added in the exact same sequence as they appear in the sql command. You are very close.

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

You can even use a parameter for the eveNum value in the WHERE clause.

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();

这篇关于VFP OleDb的SQL参数化语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！