MSAccess SQL Too few parameters. Expected 4

Question

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.'.

Answer 1

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.