Syntax Error on a Sql Parametrized update command - c#

Question

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

check my answer, you are using double qoutes in the command text — Shafqat Masood
– Shafqat Masood, Commented May 5, 2013 at 12:53

chue x · Accepted Answer · 2013-05-05 12:48:48Z

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() + "'";

answered May 5, 2013 at 12:48

chue x

18.9k8 gold badges60 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shafqat Masood · Accepted Answer · 2013-05-05 12:51:47Z

1

change your command text as

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

answered May 5, 2013 at 12:51

Shafqat Masood

2,5601 gold badge19 silver badges23 bronze badges

1 Comment

Walter Fabio Simoni Over a year ago

The error message go on the "DbCommand.ExecuteNonQuery(); ", it's an exception : "Syntax Error." .

James K. Lowden · Accepted Answer · 2013-05-06 04:06:12Z

0

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.

answered May 6, 2013 at 4:06

James K. Lowden

7,8551 gold badge19 silver badges32 bronze badges

Collectives™ on Stack Overflow

Syntax Error on a Sql Parametrized update command - c#

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related