CODEWITHSUNDEEP
mysqlsqldelphi
Bonang
Bonang

Reputation: 101

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 
+ ''')

Upvotes: 3

Views: 202

Answers (1)

MartynA
MartynA

Reputation: 30735

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:

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.

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 ('
   + ':CustomerPassword, '
   etc.

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

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

Upvotes: 2

Related Questions