SQL : Update Statement : Syntax Error

Question

The Votes column defualt value is 0. Every time when I click the button it must add whichever value I want to the specific row I want.

My error is :

Syntax error : Update statement. [[Delphi]]

This is my code :

procedure TForm4.BitBtn1Click(Sender: TObject);    
var     
  spinval : integer;
begin
  spinval := SpinEdit1.value;``

  // Candidatetable.Insert;
  // Candidatetable['Votes'] := Candidatetable['Votes'] + spinval;

  ADOQuery1.Active := false;
  ADOQuery1.SQL.Text := 'Update Candidate_table set votes = ''' +
                        Candidatetable['Votes'] + IntToStr(spinval) +
                        ''' where Name = ''' + DBLookupComboBox1.Text + '''';

  ADOQuery1.ExecSQL;
  ADOQuery1.Active := false;
  ADOQuery1.SQL.Text := 'Select * from Candidate_table';
  ADOQuery1.Active := true;

  MessageDlgPos('Thank you for voting. You will be logged out.' , mtInformation, [mbOK], 0, 1000, 500);

  Form4.Hide;
  Form2.Show;
end;

PlEASE HELP =)

Thanks.

Use [named parameters](http://stackoverflow.com/questions/7754345/how-to-pass-parameters-to-an-adoquery-object) instead of unsanitized string concatenation. — DCoder, Sep 05 '13 at 12:34
maybe remove the tick marks after` spinval := SpinEdit1.value;` — jev, Sep 05 '13 at 12:49
Which part of the task are you having trouble with? Look at your SQL and find the error. You haven't shown the SQL here. You've shown the formula you use to *construct* the SQL, but not the result, and the result is obviously the part that Delphi dislikes. — Rob Kennedy, Sep 05 '13 at 13:19
Which DBMS are you using? Postgres? Oracle? Can you print out the generated SQL statement and show it here? I'm pretty sure the error is obvious then. — , Sep 05 '13 at 13:33
Jaco, please stop what you are doing right now and google the words "SQL injection". — whosrdaddy, Sep 05 '13 at 13:42
Or consider what might happen if a user decided to call himself `(DELETE FROM SOMETABLE)` — Hugh Jones, Sep 05 '13 at 13:44
Here's the XKCD comic that most of us are thinking about..... http://bobby-tables.com/ — Chris Thornton, Sep 05 '13 at 14:46
Please do not put tag information in your subject (title), especially like [[DELPHI]]. The tag system here works very well for classifying questions and getting them to the right people to see, and it doesn't need any help. Added noise in your subject does not get you answers faster. Thanks. — Ken White, Sep 05 '13 at 16:12
@Jaco: I've rolled back (reversed) your last edit. It is not allowed here to change the entire question after you've received answers to it. Doing so can make answers no longer match the question, causing downvotes. It's also a big waste of the time of people who answered your first question only to have you change it completely. If you have a **new question**, post a **new question**. You might want to read [ask] again, which you agreed to understanding when you created your account here. — Ken White, Sep 05 '13 at 21:36
possible duplicate of [Update statement not working correctly. SQL](http://stackoverflow.com/questions/18646522/update-statement-not-working-correctly-sql) — Blorgbeard, Sep 05 '13 at 22:16

Hugh Jones · Accepted Answer · 2013-09-06T08:14:58.943

5

I think this is what you are looking for.

  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('Update Candidate_table');
  ADOQuery1.SQL.Add('set votes = votes + :Votes');
  ADOQuery1.SQL.Add('where Name = :Name');

  ADOQuery1.Parameters[0].Value := spinval;
  ADOQuery1.Parameters[1].Value := DBLookupComboBox1.Text;

edited Sep 06 '13 at 08:14

answered Sep 05 '13 at 13:43

Hugh Jones

2,706
19
30

2

Small remark, I would use ParamByName, not magic index values. – whosrdaddy Sep 05 '13 at 13:49
@whosrdaddy, magic is faster, non-magic safer :-) Yes, in this case it definitely doesn't matter, but for repetitive parameter assignments I'd either store `TParameter` reference or use index. – TLama Sep 05 '13 at 13:54
2

I tend to use index values when the sql is right close to the parameter setting line. If the Sql is elsewhere I use ParamByName unless I am looking for something VERY performant. – Hugh Jones Sep 05 '13 at 13:54
Uhg...Sorry its not working it isnt counting the votes up as i like its just replacing the votes' value to the spinedit's value – Jaco. Sep 05 '13 at 21:36
@Jaco: just replace the line ADOQuery1.SQL.Add('set votes = :Votes'); with ADOQuery1.SQL.Add('set votes = votes + :Votes'); – whosrdaddy Sep 06 '13 at 05:59
Yes it wasn't 100% clear from your code fragment what logic was intended – Hugh Jones Sep 06 '13 at 08:13
@Jaco - I have edited the response slightly so `Spinval` is added to existing 'votes'. Be aware that I am assuming the votes field in the table cannot be `NULL`. – Hugh Jones Sep 06 '13 at 08:17
@ everyone thanks for the help. I got it to work and learnt something new. – Jaco. Sep 06 '13 at 12:01

SQL : Update Statement : Syntax Error

1 Answers1

Linked