Syntax error in "insert into statement" on MS-Access using Delphi6

Question

When i execute SQL Query directly on MS-Access it will insert the Record, but when via TADOQuery component in Delphi, show above error. What is Error in my Code?

Here is my Code:

strQuery := 'INSERT INTO MAS_User_Master
            (First_Name, Middle_Name, Last_Name, User_Name, Password, Mobile_No, 
             Email_Id, Security_Question, Security_Answer, Is_Admin, Is_Deleted,
             Created_By, Created_Date) 
            VALUES
            ('Adam', 'G.', 'James', 'adam', 'ada23',9999599990,
             'adam@yahoo.com', 'what', 'yes', -1, 0,
             'admin', Now())'

qryExec.SQL.Add(strQuery);
qryExec.ExecSQL();

EDIT:

Above strQuery value is copied at Run-Time. To Create statement i used below Code:

strQuery := 'INSERT INTO MAS_User_Master ' +
                  '(First_Name, Middle_Name, Last_Name, User_Name, Password, Mobile_No, Email_Id, '+
                  'Security_Question, Security_Answer, Is_Admin, Is_Deleted, Created_By, Created_Date) '+
                  'VALUES (''' + UserRec.FirstName + ''', ''' + UserRec.MiddleName +
                  ''', ''' + UserRec.LastName + ''', ''' +  UserRec.UserName + ''', ''' + UserRec.Password +
                  ''',' + UserRec.MobileNubmer + ', ''' + UserRec.EmailId + ''', '+
                  '''' + UserRec.SecurityQuestion + ''', ''' + UserRec.SecurityAnswer + ''', ' +
                  UserRec.IsAdmin + ', 0, '''+ g_strUserName + ''', Now())';

Answer 1

qryExec.Paramcheck := true;
qryExec.SQL.Text := 'INSERT INTO MAS_User_Master '
            +'(First_Name, Middle_Name, Last_Name, User_Name, [Password], Mobile_No, '
            +' Email_Id, Security_Question, Security_Answer, Is_Admin, Is_Deleted,'
            +' Created_By, Created_Date)'
            +'VALUES'
            +'(:fn, :mn, :ln, :un, :pw,:mno,'
            +' :em, :q, :an, :isad, :isDel,'
            +' :cb, :cd)';
qryExec.Parameters.ParamByName('fn').Value := 'Adam';
qryExec.Parameters.ParamByName('mn').Value := 'G.';
qryExec.Parameters.ParamByName('ln').Value := 'James';
qryExec.Parameters.ParamByName('un').Value := 'adam';
qryExec.Parameters.ParamByName('pw').Value := 'ada23';
qryExec.Parameters.ParamByName('mno').Value := 9999599990;
qryExec.Parameters.ParamByName('em').Value := 'adam@yahoo.com';
qryExec.Parameters.ParamByName('q').Value := 'what';
qryExec.Parameters.ParamByName('an').Value := 'yes';
qryExec.Parameters.ParamByName('isad').Value := -1;
qryExec.Parameters.ParamByName('isdel').Value := 0;
qryExec.Parameters.ParamByName('cb').Value := 'admin';
qryExec.Parameters.ParamByName('cd').Value := Now();
qryExec.ExecSQL();

another way could be:

AdoDataset.CommandText :='Select * from MAS_User_Master where 1=0';
AdoDataset.Append;
Adodataset.FieldByName('First_Name').Value := 'Adam';
// and so on
Adodataset.Post;

Answer 2

Thank You! All of You..

I found the Solution :

All of the Above Queries and Syntax are Correct and Working..

Important thing:

i have one Field Named 'PASSWORD' in MS-Access Database. Delphi has reserved this word, so we can not use them. I changed name of the Field and All the stuff working fine...

Answer 3

There are a couple of could-be errors

First, your query contains quotes. To use a quote in a string in Delphi, you must type a double quote to escape it, otherwise it is seen as a string terminator. This will generate a Delphi compile error.

Second, you Add to the SQL. This means, if the SQL property already contains information, this statement is added to it, likely resulting in an invalid statement. This would generate a runtime error, generated by ADO at the time of executing the ExecSQL method.

Other things you can do, that won't solve your problem directly, but make your life easier and your project more maintainable:

Use the object inspector to edit the SQL property. You won't need to escape quotes, can easily add line breaks, and can execute the same query, as is, in Access to test. You can use a TADOQuery (or rather a TADOCommand for insert and update statement) per statement you need to execute, so you never have to update the SQL itself.

To use 'variable values' in the statements, you can use the Parameters of the command.

To make sure they are not in the way, blocking the site of your form when designing, you can put the commands on a data module.

Answer 4

In Delphi you should use double quotas to insert one quote into string. Make sure qryExec.SQL is empty. Just do qryExec.SQL.Clear before qryExec.SQL.Add. Also Now() is a Delphi function so you should convert results of Now() into string to put it into SQL query.

strQuery := 'INSERT INTO MAS_User_Master (First_Name, Middle_Name, Last_Name, User_Name, Password, Mobile_No, Email_Id, Security_Question, Security_Answer, Is_Admin, Is_Deleted, Created_By, Created_Date) VALUES (''Adam'', ''G.'', ''James'', ''adam'', ''ada23'',9999599990, ''adam@yahoo.com'', ''what'', ''yes'', -1, 0, ''admin'', '''+DateToStr(Now())+''' )';

qryExec.SQL.Add(strQuery);
qryExec.ExecSQL();