Clash Royale CLAN TAG#URR8PPP

Syntax Error in Insert to statement for Delphi 10.2 Tokyo

I need help with my coding, I have tried changing variables and displaying the SQL and I still can not see the error

Thank you.

Here is my code:

qryCustomerInformation.SQL.Add('/*The SQL query string from below*/');
showmessage(qryCustomerInformation.SQL.Text);
qryCustomerInformation.ExecSQL;


Here is my query string:

INSERT INTO BasicInformation (
Passwords,
CustomerID,
Names,
Surname,
Age,
IdentitiyNumber,
Address,
CardNumber,
CSV,
Expirey,
Image,
PhoneNumber
) VALUES (
''' + CustomerPassword +
''',''' + CustomerId +
''',''' + CustomerName +
''',''' + CustomerSurname +
''',''' + CustomerAge +
''',''' + CustomerIdNumber +
''',''' + CustomerAddress +
''',''' + CustomerCardNumber +
''',''' + CustomerCSV +
''',''' + CustomerExpirey +
''',''' + CustomerImage +
''',''' + CurstomerPhoneNumber
+ ''')


1 Answer
1

Part of the reason that you are having problems is that your SQL is syntactically
unnecessarily complicated. Assuming CustomerPassword, etc are all string variables,
you can simplify your Sql as follows:

CustomerPassword

var InsertSql : String;

begin
[...]
InsertSql :=
'INSERT INTO BasicInformation ('
+ ' Passwords, '
+ ' CustomerID, '
+ ' Names, '
+ ' Surname, '
+ ' Age, '
+ ' IdentitiyNumber, '
+ ' Address, '
+ ' CardNumber, '
+ ' CSV, '
+ ' Expirey, '
+ ' Image, '
+ ' PhoneNumber'
+ ')'
+ ' VALUES ('
+ QuotedStr(CustomerPassword) + ', '
[... etc]
+ QuotedStr(CurstomerPhoneNumber) + ')'
;

qryCustomerInformation.SQL.Add(InsertSql);


I have added the InsertSql local variable to avoid constructing the Sql within
the call to qryCustomerInformation.SQL.Add(), because that frequently results in errors itself.

The code above uses QuotedStr around the values of the string variables. It not only supplies the necessaryu surrounding quotes but also correctl handles the case where the variable's value includes embedded single-quote marks.

QuotedStr

However, as David Heffernan says, it is better to use a parameterised query,
for which you need to add a parameter to qryCustomerInformation for each value
in the Values clause, and modify the InsertSql so that it refers to each of them
instead of the original string value, like so

Values

+ ' VALUES ('
+ ':CustomerPassword, '
etc.


qryCustomerInformation will automatically substitute the parameter values for the labels prceded by colons.

qryCustomerInformation

Btw, your code contains a number of spellings which look suspicious to me:
Expirey
CustomerExpirey
CurstomerPhoneNumber

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.