Delphi ADO SQL Syntax Error

Question

I am getting an Syntax Error when processing the following lines of code. Especially on the AQ_Query.Open;

procedure THauptfenster.Button1Click(Sender: TObject);
var
    option: TZahlerArray;
begin
    option := werZahlte;
    AQ_Query.Close;
  AQ_Query.SQL.Clear;
  AQ_Query.SQL.Add('USE wgwgwg;');
  AQ_Query.SQL.Add('INSERT INTO abrechnung ');
  AQ_Query.SQL.Add('(`datum`, `titel`, `betrag`, `waldemar`, `jonas`, `ali`, `ben`)');
  AQ_Query.SQL.Add(' VALUES ');
  AQ_Query.SQL.Add('(:datum, :essen, :betrag, :waldemar, :jonas, :ali, :ben);');
  AQ_Query.Parameters.ParamByName('datum').Value := DateToStr(mcDatum.Date);
  AQ_Query.Parameters.ParamByName('essen').Value := ledTitel.Text;
  AQ_Query.Parameters.ParamByName('betrag').Value := ledPreis.Text;
  AQ_Query.Parameters.ParamByName('waldemar').Value := option[0];
  AQ_Query.Parameters.ParamByName('jonas').Value := option[1];
  AQ_Query.Parameters.ParamByName('ali').Value := option[2];
  AQ_Query.Parameters.ParamByName('ben').Value := option[3];
  AQ_Query.Open;
end;

The error:

SQL Error

I am using MySQL Delphi 2010.

da-soft · Accepted Answer · 2010-03-25 19:20:01Z

5

USE and INSERT are two different SQL commands.
MySQL does not support so called "Batches".

=> you have to call these commands one-by-one

answered Mar 25, 2010 at 19:20

da-soft

7,77031 silver badges37 bronze badges

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

2 Comments

Acron Over a year ago

Ok. I Have now two different statements. Still, I need to call AQ_Query.ExecSql in order to insert. Open does not work because it says: "AQ_Query does not return any data." Still, it does not update in the DBGrid. How to update it?

Deltics Over a year ago

Yes, you use Open for queries that return results otherwise use ExecSQL. Where did any DBGrid feature in the original question ?!?!

Mason Wheeler · Accepted Answer · 2010-03-25 17:28:09Z

1

Looks to me like you're using backticks on the third AQ_Query.SQL.Add line, when you need to use normal single quotes.

answered Mar 25, 2010 at 17:28

Mason Wheeler

85k54 gold badges288 silver badges510 bronze badges

4 Comments

Francesca Over a year ago

Yep, that's the line the error message points to and I don't see anything else in there...

Scott W Over a year ago

Actually, since the items in backticks are apparently column names, there shouldn't be a need for any kind of punctuation at all.

skamradt Over a year ago

You could surround the column names with square brackets. '([datum], [titel], [betrag] ... although its not required.

Deltics Over a year ago

The error message points to the INSERT statement, not the columns list. (Line #2 is the INSERT statement, line #1 is the USE statement). See Dmitry's answer - I think he is right. I don't think you can use an ADO query object to execute a script, only an individual statement.

RRUZ · Accepted Answer · 2010-03-26 13:06:44Z

0

Use the database in the sql script.

AQ_Query.SQL.Add('INSERT INTO wgwgwg.dbo.abrechnung ');
  AQ_Query.SQL.Add('(`wgwgwg.dbo.abrechnung.datum`, `wgwgwg.dbo.abrechnungtitel`, `wgwgwg.dbo.abrechnungbetrag`, `wgwgwg.dbo.abrechnungwaldemar`, `wgwgwg.dbo.abrechnungjonas`, `wgwgwg.dbo.abrechnungali`, `wgwgwg.dbo.abrechnungben`)');

  AQ_Query.SQL.Add(' VALUES ');

  AQ_Query.SQL.Add('(:datum, :essen, :betrag, :waldemar, :jonas, :ali, :ben);');
  AQ_Query.Parameters.ParamByName('datum').Value := DateToStr(mcDatum.Date);
  AQ_Query.Parameters.ParamByName('essen').Value := ledTitel.Text;
  AQ_Query.Parameters.ParamByName('betrag').Value := ledPreis.Text;
  AQ_Query.Parameters.ParamByName('waldemar').Value := option[0];
  AQ_Query.Parameters.ParamByName('jonas').Value := option[1];
  AQ_Query.Parameters.ParamByName('ali').Value := option[2];
  AQ_Query.Parameters.ParamByName('ben').Value := option[3];

You can also make a new connection to wgwgwg and refer your AQ_Query to the new connection

edited Mar 26, 2010 at 13:06

RRUZ

137k20 gold badges365 silver badges489 bronze badges

answered Mar 26, 2010 at 12:41

Ravaut123

2,8181 gold badge34 silver badges49 bronze badges

2 Comments

Acron Over a year ago

where comes that dbo in wgwgwg.dbo.abrechnung.datum from?

Ravaut123 Over a year ago

Database Owner (dbo) The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically. see: msdn.microsoft.com/en-us/library/aa905208%28SQL.80%29.aspx

Collectives™ on Stack Overflow

Delphi ADO SQL Syntax Error

3 Answers 3

2 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related