C# - Syntax Error in UPDATE Statement

Question

I'm trying to update a database using C#, but I get the error: "Syntax error in UPDATE statement". I've looked around for other examples of this error, and found plenty, but every one is different. A lot of things can cause this error, and I just can't put my finger on this one.

query = String.Format(@"UPDATE PAYMENT 
                    SET MONTANT={0}, TYPE='4-Comptant',note='PPA',flag='O', date='{2:yyyyMMdd}'       
                    WHERE num_payment={1}", -payment, id, dt);
                    daUpdate.UpdateCommand.CommandText = query;
                    daUpdate.UpdateCommand.ExecuteNonQuery();  //update the table in the database

In debug mode, this is what the string ends up looking like:

UPDATE PAYMENT 
                                                SET MONTANT=-390, TYPE='4-Comptant',note='PPA',flag='O', date='20120601'
                                                WHERE num_payment=8

In the database, num_payment is a long integer and a primary key. MONTANT is a double, everything else is text.

Edit: Following people's advice, I've modified things a bit, but I'm still getting the same error. Here's what it looks like now:

                    OleDbCommand cmd = _con.CreateCommand();
                    cmd.CommandText = @"UPDATE PAYMENT 
                    SET MONTANT=@montant, [TYPE]='4-Comptant',note='PPA',flag='O', [date]=@theDate 
                    WHERE num_payment=@numPayment";

                    cmd.Parameters.AddWithValue("@montant", -payment);
                    cmd.Parameters.AddWithValue("@theDate", String.Format("{0:yyyyMMdd}", dt));
                    cmd.Parameters.AddWithValue("@numPayment", id);
                    cmd.ExecuteNonQuery();

The debug output looks the same as before, except that now I see '@something' instead of the real value.

database, num_payment is a long integer and a primary key. MONTANT is a double, everything else is text Something's not what you think it is. See here — Conrad Frix
– Conrad Frix, Commented Jun 1, 2012 at 16:49
@agent-j: I can't, the database is the way it is, I'm not allowed to change the formats. @ — NickLokarno
– NickLokarno, Commented Jun 1, 2012 at 18:46
@agent-j: I'm not sure what you mean by strong, it shows up as "text" is MS Access. What I meant was that my boss gave me this database as it is, with entries already in this format. He doesn't want me changing the formats. — NickLokarno
– NickLokarno, Commented Jun 1, 2012 at 19:35

Rich S · Accepted Answer · 2012-06-01 17:19:27Z

1

Put square brackets around [TYPE] and [DATE] these are SQL keywords.

query = String.Format(@"UPDATE PAYMENT  
                SET MONTANT={0}, [TYPE]='4-Comptant',note='PPA',flag='O', [date]='{2:yyyyMMdd}'        
                WHERE num_payment={1}", -payment, id, dt); 
                daUpdate.UpdateCommand.CommandText = query; 
                daUpdate.UpdateCommand.ExecuteNonQuery();  //update the table in the

answered Jun 1, 2012 at 17:19

Rich S

3,4843 gold badges33 silver badges53 bronze badges

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

2 Comments

Andriy M Over a year ago

The question is tagged mysql, and MySQL doesn't use [] as name delimiters. TYPE and DATE are not reserved words in MySQL (DATE is a keyword, but not a reserved word).

NickLokarno Over a year ago

Actually, that's my mistake, its just plain SQL. I've removed the tag.

NickLokarno · Accepted Answer · 2012-06-02 18:06:19Z

1

The fields 'note' and 'flag' needed to have [] around them. I added these and now the query doesn't give that error anymore.

answered Jun 2, 2012 at 18:06

NickLokarno

3207 silver badges22 bronze badges

Collectives™ on Stack Overflow

C# - Syntax Error in UPDATE Statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related