Delphi 7 Syntax error (comma) in ADO query

Question

I'm trying to create a record in a database using a SQL query in Delphi 7. I'm using an ADO Query and I've tried both with and without parameters, but to no avail. The error occurs between ShowMessage 1 and 2.

sName := ledName.Text;
sSurname := ledSurname.Text;
sSchool := ledSchool.Text;
sMotherName := ledMotherName.Text;
sMotherCell := ledMotherCell.Text;
sMotherEmail := ledMotherEmail.Text;
sFatherName := ledFatherName.Text;
sFatherCell := ledFatherCell.Text;
sFatherEmail := ledFatherEmail.Text;
sAddress := ledAddress.Text;
sBirthday := DateToStr(dpcBirthday.Date);
ShowMessage(sBirthday);
case rgpGender.ItemIndex of
  0 : cGender := 'M';
  1 : cGender := 'F';
end;
iGrade := rgpGrade.ItemIndex - 2;
if chkLeader.Checked = true then
  bLeader := True
else
  bLeader := False;
with dmData do begin
  ShowMessage('1');
  qryMain.Active := False;
  qryMain.SQL.Text := 'SELECT * FROM Users Where Name = "'+sName+'", Surname = "'+sSurname+'", Birthday = "'+sBirthday+'" ';
  qryMain.Open;
  if qryMain.RecordCount = 0 then begin
    qryMain.Close;
    ShowMessage('2');
    //qryMain.SQL.Text := 'INSERT INTO Users(Name, Surname, MotherName, FatherName, Gender, Grade, Birthday, School, Address, MotherCell, FatherCell, MotherEmail, FatherEmail, NameTag, Volunteer) VALUES("'+sName+'", "'+sSurname+'", "'+sMotherName+'", "'+sFatherName+'", "'+cGender+'", "'+IntToStr(iGrade)+'", "'+sBirthday+'", "'+sSchool+'", "'+sAddress+'", "'+sMotherCell+'", "'+sFatherCell+'", "'+sMotherEmail+'", "'+sFatherEmail+'", False, "'+BoolToStr(bLeader)+'") ';
    qryMain.SQL.Text := 'INSERT INTO Users(Name, Surname, MotherName, FatherName, Gender, Grade, Birthday, School, Address, MotherCell, FatherCell, MotherEmail, FatherEmail, NameTag, Volunteer) ' + 'VALUES(:Name, :Surname, :MotherName, :FatherName, :Gender, :Grade, :Birthday, :School, :Address, :MotherCell, :FatherCell, :MotherEmail, :FatherEmail, False, :Leader) ';
    qryMain.Parameters.ParamByName('Name').Value := sName;
    qryMain.Parameters.ParamByName('Surname').Value := sSurname;
    qryMain.Parameters.ParamByName('MotherName').Value := sMotherName;
    qryMain.Parameters.ParamByName('FatherName').Value := sFatherName;
    qryMain.Parameters.ParamByName('Gender').Value := cGender;
    qryMain.Parameters.ParamByName('Grade').Value := iGrade;
    qryMain.Parameters.ParamByName('Birthday').Value := sBirthday;
    qryMain.Parameters.ParamByName('School').Value := sSchool;
    qryMain.Parameters.ParamByName('Address').Value := sAddress;
    qryMain.Parameters.ParamByName('MotherCell').Value := sMotherCell;
    qryMain.Parameters.ParamByName('FatherCell').Value := sFatherCell;
    qryMain.Parameters.ParamByName('MotherEmail').Value := sMotherEmail;
    qryMain.Parameters.ParamByName('FatherEmail').Value := sFatherEmail;
    qryMain.Parameters.ParamByName('Leader').Value := bLeader;
    ShowMessage('3');
    qryMain.ExecSQL;
    qryMain.SQL.Text := 'SELECT * FROM Users';
    qryMain.Open;

The commented out part was the one way I tried doing this, and it gave this error:

Syntax error (comma) in query expression 'Name="Derp",Surname="Foo",Birthday="1900-01-01"'

The code with parameters gives me this error:

Syntax error (comma) in query expression 'Name="Derp",Surname="Foo",Birthday="1900-01-01"'

Any help would be greatly appreciated!

No, the two queries cannot give that exact same error. They may both give an EOleException, but the message would be different, and the message is important here. Please do include the other error message you're getting, and make it clear which exception you're getting for which query. — user743382
– user743382, Commented Aug 21, 2014 at 9:47
@hvd I've added a screenshot of the first error as well, but as I said, they are exactly the same. — DearVolt
– DearVolt, Commented Aug 21, 2014 at 9:55
Maybe your db back-end doesn't recognise False as a valid value f0r the column in question - Boolean is not necessarily supported as a column type. — MartynA
– MartynA, Commented Aug 21, 2014 at 9:59
@MartynA I've used Boolean elsewhere before using the first method (Without parameters) and it worked just fine. If you look at the error, you'll see for some strange reason there's an extra apostrophe after the date and I can't see where it's coming from. The ShowMessage returns 1900-01-01 so I don't think it's a part of the date. — DearVolt
– DearVolt, Commented Aug 21, 2014 at 10:05

J... · Accepted Answer · 2014-08-21 11:09:09Z

2

Now that we can see your real code, your error is clear

'SELECT * FROM Users Where Name = "'+sName+
                    '", Surname = "'+sSurname+
                    '", Birthday = "'+sBirthday+'" ';

should use the AND operator to link those conditions :

'SELECT * FROM Users Where Name = "'+sName+
                 '" AND Surname = "'+sSurname+
                 '" AND Birthday = "'+sBirthday+'" ';

You should also, naturally, seriously consider parameterizing this query as well.

edited Aug 21, 2014 at 11:09

answered Aug 21, 2014 at 11:04

J...

31.5k7 gold badges71 silver badges148 bronze badges

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

2 Comments

DearVolt Over a year ago

Which way is better? With parameters or without? And why exactly? Thank you so much!

J... Over a year ago

@DJSquared With parameters, always. The reasons are myriad - it's cleaner, easier to read and maintain, it avoids the mess of string concatenation, prevents SQL injection attacks, is less error prone, allows the DB to cache the query (resulting in better performance), etc, etc. There are plenty of resources out there if you wish to read more.

Collectives™ on Stack Overflow

Delphi 7 Syntax error (comma) in ADO query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related