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

Good find, but no cigar. :-) The problem is that it's a reserved word in Access SQL, not Delphi.

@GolezTrol Nop, it's reserved word of DELPHI, because when i copy SQL string and Paste directly in MS-Access, it is working fine with "PASSWORD" field name.

Maybe it's an ADO or driver issue, but it's not Delphi.

bummi · Accepted Answer · 2012-12-18 22:01:22Z

2

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 := '[email protected]';
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;

edited Dec 18, 2012 at 22:01

answered Dec 18, 2012 at 7:36

bummi

27.4k14 gold badges67 silver badges105 bronze badges

4 Comments

Sir Rufo Over a year ago

+1 for parameters (and i'll get a beer from you, fill up to 3500 rep ;o) )

i copied above Parametrized code, but still getting "SYNTAX ERROR in INSERT INTO Statement".

@Gani - did you already changed Sql.Add for qryExec.SQL.Text := ? You've been prompted to fix it many times but you never yet told you did that. Before calling qryExec.ExecSQL(); add one more line: ShowMessage(qryExec.SQL.Text); and what u see there ?

Fionnuala Over a year ago

Password is a reserved word ( support.microsoft.com/kb/248738 ) and in MS Access sql it would either be prefixed with an alias or table name or enclosed in square brackets: [Password]

valex · Accepted Answer · 2012-12-18 07:04:16Z

0

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, ''[email protected]'', ''what'', ''yes'', -1, 0, ''admin'', '''+DateToStr(Now())+''' )';

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

answered Dec 18, 2012 at 7:04

valex

24.2k7 gold badges47 silver badges62 bronze badges

4 Comments

now i used 'Clear' Method for SQL and then 'Add' method, but still have same problem.

valex Over a year ago

Did you use strQuery string from my answer?

i also used Now() in another Query and it stores DateTime as a MS-Access Function. i have passed 'now()' without quotes.

i also tried Your Above Code for NOW() function, but still have problem.

GolezTrol · Accepted Answer · 2012-12-18 07:05:53Z

0

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.

edited Dec 18, 2012 at 7:05

answered Dec 18, 2012 at 7:00

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

5 Comments

would you put his modified sources (modified to use params) into the answer ?

I could, but I didn have time this morning. Also, I feel that pointing someone to the right direction doesn't need an example per se. If they get stuck trying out parameters, they can always ask a new question. :)

Many times i feel the same, but this almost inevitably leads to someone implementing my hints and stealing my thunder ;-)

@Arioch'The I had to look up that expression and am still not sure what you mean. Is it about bummi gaining the points for adding a fully working snippet of code? That happens to me a lot, because I rather give hints and make people think than to provider a ready to use example without context. If that costs me points, then I can live with that.