由于“SQL语法”错误,无法插入MS Access

时间:2021-08-11 15:22:20

I'm trying to do an insert into MS Access, but I get an error in "SQL Syntax".

我正在尝试插入MS Access,但我在“SQL语法”中收到错误。

I believe the word "password" is a keyword. Is there a way to work around this without changing the database field name?

我相信“密码”这个词是一个关键字。有没有办法解决这个问题而不更改数据库字段名称?

OleDbCommand cmd = new OleDbCommand("INSERT INTO profiles(
     user_name, full_name, password, profile_id) 
     VALUES (@user_name, @full_name, @password, @profile_id);");

cmd.Parameters.AddWithValue("@full_name", this.FullName);
cmd.Parameters.AddWithValue("@user_name", this.UserName);
cmd.Parameters.AddWithValue("@password", this.Password);
cmd.Parameters.AddWithValue("@profile_id", this.ProfileID);

EDIT:

There was a typo in the code above, which I have now fixed. It did not appear in the original code, but appeared after I edited it for here. The fix, as many pointed out was the brackets.

上面的代码中有一个拼写错误,我现在已经修复了。它没有出现在原始代码中,但是在我编辑它之后出现了。正如许多人指出的那样,修正是括号。

2 个解决方案

#1


1  

You can put the database field in quotes or square brackets:

您可以将数据库字段放在引号或方括号中:

[password] "password"

e.g.:

"INSERT INTO profiles(user_name, full_name, [password], profile_id)

#2


3  

Enclose the field name in square brackets.

将字段名称括在方括号中。

The mismatch between between @passwords and @password is irrelevant because the parameter names are ignored when using OleDb with Access. However you must supply the parameter values in the same order as they appear in the INSERT statement --- so @user_name before @full_name:

@passwords和@password之间的不匹配是无关紧要的,因为在使用OleDb和Access时会忽略参数名称。但是,您必须按照它们在INSERT语句中出现的顺序提供参数值---所以在@full_name之前@user_name:

    OleDbCommand cmd = new OleDbCommand("INSERT INTO profiles(user_name, full_name, [password], profile_id) VALUES (@user_name, @full_name, @passwords, @profile_id);");

    cmd.Parameters.AddWithValue("@user_name", this.UserName);
    cmd.Parameters.AddWithValue("@full_name", this.FullName);
    cmd.Parameters.AddWithValue("@password", this.Password);
    cmd.Parameters.AddWithValue("@profile_id", this.ProfileID);

Judging by the comments, it appears we have a disagreement as to whether password is a reserved word in MS Access. According to Microsoft, it is a reserved word: List of reserved words in Jet 4.0

从评论来看,似乎我们对密码是否是MS Access中的保留字表示不同意见。根据微软的说法,它是一个保留字:Jet 4.0中的保留字列表

#1


1  

You can put the database field in quotes or square brackets:

您可以将数据库字段放在引号或方括号中:

[password] "password"

e.g.:

"INSERT INTO profiles(user_name, full_name, [password], profile_id)

#2


3  

Enclose the field name in square brackets.

将字段名称括在方括号中。

The mismatch between between @passwords and @password is irrelevant because the parameter names are ignored when using OleDb with Access. However you must supply the parameter values in the same order as they appear in the INSERT statement --- so @user_name before @full_name:

@passwords和@password之间的不匹配是无关紧要的,因为在使用OleDb和Access时会忽略参数名称。但是,您必须按照它们在INSERT语句中出现的顺序提供参数值---所以在@full_name之前@user_name:

    OleDbCommand cmd = new OleDbCommand("INSERT INTO profiles(user_name, full_name, [password], profile_id) VALUES (@user_name, @full_name, @passwords, @profile_id);");

    cmd.Parameters.AddWithValue("@user_name", this.UserName);
    cmd.Parameters.AddWithValue("@full_name", this.FullName);
    cmd.Parameters.AddWithValue("@password", this.Password);
    cmd.Parameters.AddWithValue("@profile_id", this.ProfileID);

Judging by the comments, it appears we have a disagreement as to whether password is a reserved word in MS Access. According to Microsoft, it is a reserved word: List of reserved words in Jet 4.0

从评论来看,似乎我们对密码是否是MS Access中的保留字表示不同意见。根据微软的说法,它是一个保留字:Jet 4.0中的保留字列表