CODEWITHSUNDEEP
c#sql
NickLokarno
NickLokarno

Reputation: 310

C# - Syntax Error in UPDATE Statement

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.

Upvotes: 0

Views: 1022

Answers (2)

NickLokarno
NickLokarno

Reputation: 310

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

Upvotes: 1

Rich S
Rich S

Reputation: 3453

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

Upvotes: 1

Related Questions