2

I'm using Delphi XE2 and AnyDAC and an MSAccess db.

The table 'timea' has 5 fields:

Rec_No AutoNumber
App text
User_ID text
PW text
Comment memo

This code throws the error below. The query works just fine in Access query designer.

sql := 'INSERT INTO [timea] (App, User_ID, PW, Comment) VALUES ("zoo", "Bill", "mi7", "Liger");';
adconnection1.ExecSQL(sql);

Project PWB.exe raised exception class EMSAccessNativeException with message '[AnyDAC][Phys][ODBC][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4.'.

Mike
  • 301
  • 4
  • 16

1 Answers1

0

Both SQL and Delphi are using single quotes as string boundaries. Since you want to have singe quote inside the string, you have to "escape" it using doube single quote.

For example, if you write S := 'Guns''N''Roses' the varieble S will contain string Guns'N'Roses - 12 characters, not 14.

Be careful when you are concatenating string values, since they might contain single quotes, too. The recommended way to write the query in this case is, for example:

sql := 'INSERT INTO Table (Col) VALUES (' + QuotedStr(Val) + ')';

Function QuotedStr wll take care and double all single quotes in the string. This is also essential to avoid insertion hacks.

adlabac
  • 416
  • 4
  • 12
  • In another program using DAO with Access, double quotes work just fine. The difference is a mystery. – Mike Aug 19 '14 at 14:12
  • Actually, depending on the driver used, different formats can be accepted. Some drivers accept, for example, different wildcards (`*`, `%`), date formats, etc. – adlabac Aug 20 '14 at 15:02
  • Not quite true. Some DBMSs use double quotes, or different escape chars. Simply use parameters and you'll be fine. AnyDAC (FireDAC) will do the rest. – Victoria Jul 02 '17 at 13:50