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. — , Aug 21 '14 at 09:47
@hvd I've added a screenshot of the first error as well, but as I said, they are exactly the same. — DearVolt, Aug 21 '14 at 09: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, Aug 21 '14 at 09: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, Aug 21 '14 at 10:05
@DJSquared the extra apostrophe is part of the error message `'Syntax error...`, `expression 'Name...` -- two single quotes open, `..."1900-01-01"''` two single quotes close. — J..., Aug 21 '14 at 10:08
@DJSquared Huh?! This shouldn't be possible... How can a query fail because of a syntax error in text that isn't in the query? Just to rule out silly mistakes, you have verified that it's *that* particular `ExecSQL` that's throwing the exception, and not some other unrelated query that gets executed later? — , Aug 21 '14 at 10:10
@hvd I know it shouldn't be possible, that's why I can't get my head around this. This is the only ExecSQL in the entire application as I have just started with it , so it can only be this query. I can upload the application if necessary. If we can't fix this, is there another way of creating a record in the Users table? — DearVolt, Aug 21 '14 at 10:15
@J... Right, didn't see the second single quote open before Name. Thanks. — DearVolt, Aug 21 '14 at 10:16
@DJSquared If you run it in the debugger (you already are), and break when the exception is thrown, inspect the call stack. See where the exception is coming from. There are other ways to have queries executed than with `ExecSQL`. For example `query.Open`, already part of your question, might be used in multiple places in your project. — , Aug 21 '14 at 10:20
I doubt the error could be raised on that line. The exception message is specifically coming from the database and that can't happen until `ExecSQL` is called. What is the data type of the `Birthday` column (or the types of the entire table, for that matter...)? — J..., Aug 21 '14 at 10:36
@J... It's a Date/Time field. I've been looking for the error in the wrong place... Going to update the code in the main post now... The exception happens before the second ShowMessage. — DearVolt, Aug 21 '14 at 10:44
Btw, most db back-ends come with a utility for executing SQL. You might try using the one for yours, as you may get a more helpful error message without Delphi and ADO getting in the way. — MartynA, Aug 21 '14 at 11:01
@DJSquared That makes more sense... the error didn't even match the query. I was wondering how much code you weren't showing us... Lesson 1 - always show us your *real* code! — J..., Aug 21 '14 at 11:02

J... · Accepted Answer · 2014-08-21T11:09:09.930

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 '14 at 11:09

answered Aug 21 '14 at 11:04

J...

30,968
6
66
143

Which way is better? With parameters or without? And why exactly? Thank you so much! – DearVolt Aug 21 '14 at 11:06
5

@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. – J... Aug 21 '14 at 11:08

Delphi 7 Syntax error (comma) in ADO query

1 Answers1