I have a syntax error in my insert into statement

Question

I'm using a MS Access database, with the following columns in the Admins table:

Column        Type
======        ====
Name          Text 
Surname       Text 
Dateadded     Date/time 
Adminnumber   Number(long integer) 
Password      Text 
ID type       Autonumber  (Not sure if ID is relevant)

This is my code but it keeps giving me a syntax error.

ADOquery1.Active := false;
adoquery1.sql.Text := 'insert into Admins(Name, surname, Adminnumber, Dateadded,password)Values('''+edit11.Text+''', '''+edit12.text+''', '''+edit13.Text+''', '''+edit14.Text+''', '''+edit15.text+''')';
ADOquery1.ExecSQL;
Adoquery1.SQL.Text := 'select * from Admins';
ADOquery1.Active := true;

i have been trying for a day to figure it out but its the same error no matter what code i use. The error is

Project project1.exe raised exception class eoleException with message 'Syntax error in INSERT INTO statement'.

i have also tried:

ADOquery1.SQL.Add('Insert into admins');
ADOquery1.SQL.Add('(Name , Surname, Dateadded, Adminnumber, Password)');  
ADOquery1.SQL.Add('Values :Name, :Surname, :Dateadded, :adminnumber :Password)');
ADOquery1.Parameters.ParamByName('Name').Value := edit11.Text;
ADOquery1.Parameters.ParamByName('Surname').Value := edit12.Text;
ADOquery1.Parameters.ParamByName('Dateadded').Value := edit13.Text;
ADOquery1.Parameters.ParamByName('Password').Value := edit14.Text;
ADOquery1.Parameters.ParamByName('Adminnumber').Value := edit15.Text;
ADOquery1.ExecSQL;
ADOquery1.SQL.Text := 'Select * from admins';
ADOquery1.Open ;

But this code gives me a problem with the from clause

What is the exact error? What is the exact text of the query which produces the error? The first version could give lots of different syntax errors, given that it allows the user to execute whatever SQL they want. (Hint: It's wide open to SQL injection attacks.) — David, Sep 04 '13 at 22:13
The exact error of my first code is "Project project1.exe raised exception class eoleException with message 'Syntax error in INSERT INTO statement'." And the second codes error is ""Project project1.exe raised exception class eoleException with message 'error in FROM clause'." — user2748631, Sep 04 '13 at 22:14
I'd expect the triple Apostrophes to be the Problem. Perhaps you may use Double Quotes as your literal delimiters or you have to Escape the Single Quotes properly. Sontheim Solution is to use delphi's equivalent of the `chr` Function, representing a Single Quote by `chr(39)`. — collapsar, Sep 04 '13 at 22:15
Which query gives the error? In both cases it looks like you're executing one query after another. Where does the error happen and what is the text of the query being executed? — David, Sep 04 '13 at 22:15
In the first Piece of code it gives the error in the line Adoquery1.SQL.Text := 'select * from Admins'; — user2748631, Sep 04 '13 at 22:33
and in the second it gives the error in the same line Adoquery1.SQL.Text := 'select * from Admins'; — user2748631, Sep 04 '13 at 22:34
I've edited to add information obtained ̶by my behaving like a dentist for more than an hour (it was like pulling teeth) :-) in comments to an answer I've since deleted after new details were provided here. — Ken White, Sep 05 '13 at 00:09
Thank you for the help sorry I was so difficult to help so far but as I said this is my first question and I'm very new to delphi :-) — user2748631, Sep 05 '13 at 00:15
In the future, make it easy on yourself and list out the rendered SQL so that you can see what it says. i.e. ShowMessage(adoquery1.sql.Text); Then you can see what the SQL looks like and even copy/paste it into a query in Access or MS Query and see how it responds. You can usually cut your problem in half this way. — Chris Thornton, Sep 05 '13 at 15:44

Ken White · Accepted Answer · 2013-09-05T14:33:59.307

The problem is that Name (and possibly Password) is a reserved word in MS Access. It's a poor choice for a column name, but if you must use it you should escape it by enclosing it in square brackets ([]). You're also missing an opening parenthesis (() after your VALUES statement, and a comma after the :adminnumber parameter.

ADOquery1.SQL.Add('Insert into admins');
ADOquery1.SQL.Add('([Name] , [Surname], [Dateadded], [Adminnumber], [Password])');  
ADOquery1.SQL.Add('Values (:Name, :Surname, :Dateadded, :adminnumber, :Password)');
ADOquery1.Parameters.ParamByName('Name').Value := edit11.Text;
ADOquery1.Parameters.ParamByName('Surname').Value := edit12.Text;
ADOquery1.Parameters.ParamByName('Dateadded').Value := edit13.Text;
ADOquery1.Parameters.ParamByName('Password').Value := edit14.Text;
ADOquery1.Parameters.ParamByName('Adminnumber').Value := edit15.Text;
ADOquery1.ExecSQL;
ADOquery1.SQL.Text := 'Select * from admins';
ADOquery1.Open;

(The error can't be moving around, as you say in the comments to your question. The only line that can possibly cause the problem is the ADOQuery1.ExecSQL; line, as it's the only one that executes the INSERT statement. It's impossible for any other line to raise the exception.)

You should make some changes here that are pretty important to the maintainability of your code.

First, break the habit immediately of using the default names for controls, especially those you need to access from your code later. You change the name by changing the Name property for the control in the Object Inspector.

It's much easier in the code to use NameEdit.Text than it is to use Edit1.Text, especially by the time you get to Edit14. It would be much clearer if Edit14 was named PasswordEdit instead, and you'll be happy you did six months from now when you have to change the code.

Second, you should avoid using the default variant conversion from string that happens when you use ParamByName().Value. It works fine when you're assigning to a text column, but isn't really good when the type isn't text (such as when using dates or numbers). In those cases, you should convert to the proper data type before doing the assignment, so that you're sure it's done correctly.

ADOQuery1.ParamByName('DateAdded').Value := StrToDate(DateEdit.Text);
ADOQuery1.ParamByName('AdminNumber').Value := StrToInt(AdminNum.Text);

Finally, you should never, ever use string concatenation such as 'SOME SQL ''' + Edit1.Text + ''','''. This can lead to a severe security issue called SQL injection that can allow a malicious user to delete your data, drop tables, or reset user ids and passwords and giving them free access to your data. A Google search will find tons of information about the vulnerabilities that it can create. You shouldn't even do it in code you think is safe, because things can change in the future or you can get a disgruntled employee who decides to cause problems on the way out.

As an example, if a user decides to put John';DROP TABLE Admins; into edit14 in your application, and you call ExecSQL with that SQL, you will no longer have an Admins table. What happens if they instead use John';UPDATE Admins SET PASSWORD = NULL; instead? You now have no password for any of your admin users.

Only 10K users, and the OP of course, fully understand how patient you were on this one. Outstanding effort! — HansUp, Sep 05 '13 at 15:19
@HansUp: Thanks. :-) Much appreciated. (Although apparently not by this poster - see the [next question](http://stackoverflow.com/q/18632222/62576), which ignores much of what I wrote here.) — Ken White, Sep 05 '13 at 18:15

I have a syntax error in my insert into statement

1 Answers1

Linked