我的插入语句中出现语法错误 [英] I have a syntax error in my insert into statement

问题描述

我正在使用MS Access数据库，并在Admins表中包含以下列:

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

项目project1.exe引发异常类eoleException 消息"INSERT INTO语句中的语法错误".

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

我也尝试过:

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 ;

但是这段代码给我一个from子句的问题

But this code gives me a problem with the from clause

推荐答案

问题是Name(可能还有Password)是MS Access中的保留字.对于列名而言，这是一个糟糕的选择，但是如果必须使用它，则应通过将其括在方括号([])中来对其进行转义.在VALUES语句之后还缺少一个左括号(()，在:adminnumber参数之后缺少一个逗号.

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;

(错误不能四处走动，正如您在问题注释中所说的那样.唯一可能导致问题的行是ADOQuery1.ExecSQL;行，因为它是唯一执行INSERT的行.语句.其他任何行都不可能引发异常.)

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

首先，立即打破使用控件默认名称的习惯，尤其是以后需要从代码访问的控件.您可以通过在对象检查器中更改控件的Name属性来更改名称.

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.

在代码中，使用NameEdit.Text比使用Edit1.Text要容易得多，尤其是到Edit14时.如果将Edit14命名为PasswordEdit会更清楚，并且您会很高兴地发现从现在起六个月后必须更改代码.

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.

第二，您应该避免使用使用ParamByName().Value时发生的字符串的默认变体转换.当您分配给text列时，它可以很好地工作，但是当类型不是文本时(例如，使用日期或数字时)，效果就不好.在这种情况下，您应该在执行分配之前转换为正确的数据类型，以确保执行正确.

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);

最后，永远不要使用字符串连接，例如'SOME SQL'''+ Edit1.Text +'''，'''.这可能导致称为 SQL注入的严重安全问题，该问题可能允许恶意用户删除您的数据，删除表或重置用户ID和密码，并让他们自由访问您的数据. Google搜索将找到有关其可能创建的漏洞的大量信息.您甚至不应该在您认为安全的代码中执行此操作，因为将来情况可能会发生变化，或者您可能会遇到不满的员工决定在出路时引起问题.

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.

作为示例，如果用户决定将John';DROP TABLE Admins;放入应用程序的edit14中，并使用该SQL调用ExecSQL，则将不再具有Admins表.如果他们改用John';UPDATE Admins SET PASSWORD = NULL;会发生什么?现在，您没有任何管理员用户的密码.

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.

这篇关于我的插入语句中出现语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！