Syntax Error on a Sql Parametrized update command

问题:

It's my first SQL Parametrized update command in c# and i have a syntax error when i exectued my update.

Here is my code :

    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;
    }

Anyone have an idea where is my mistake ? In addition, i wrote in a old DBF file (Visual Foxpro) and i think i don't have access to log in order to debug the query :(.

Thanks a lot :)

Best regards,

Nixeus

回答1:

Try using single quotes in your UPDATE statement instead of double quotes. The last line

+ "eveinterv = @eveinterv where eveNum = " + '"' + strEvtNumeroString.ToString() + '"';

should be

+ "eveinterv = @eveinterv where eveNum = '" + strEvtNumeroString.ToString() + "'";

回答2:

change your command text as

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

回答3:

If you print out maRequete, and try executing it interactively, you will find the SQL syntax is incorrect. It seems likely you're using double-quotes to denote string constants; in SQL you should use single quotes for that. It's possible your data contains a single quote (i.e. an apostrophe). In that case, you need to add and extra one e.g.

INSERT ... values ('you''ll need two apostrophes for this');

These are just SQL rules. You have to give the server valid syntax if it's to execute your query.