语法错误在INSERT语句到MS访问 [英] Syntax error in INSERT statement into MS Access
问题描述
我找不到在下面的INSERT语句中的语法错误
公共部分类doRegister:System.Web程序。 UI.Page
{
保护无效的Page_Load(对象发件人,EventArgs五)
{
字符串str = @供应商= Microsoft.ACE.OLEDB.12.0;数据源= C :\数据库;
使用使用(OleDbCommand的CMD = con.CreateCommand())
{
cmd.CommandText =(OleDbConnection的CON =新的OleDbConnection(STR))
INSERT INTO用户(STAFFID是, accessLevelIdD,用户名,密码,电子邮件)VALUES(@staffID,'2',@用户名,密码@,@电子邮件);
cmd.Parameters.AddWithValue(STAFFID @的Request.Form [STAFFID是]);
cmd.Parameters.AddWithValue(@密码的Request.Form [confpassword]);
cmd.Parameters.AddWithValue(@用户名的Request.Form [用户名]);
cmd.Parameters.AddWithValue(@电子邮件的Request.Form [电子邮件]);
con.Open();
试
{
cmd.ExecuteNonQuery();
MessageBox.Show(成功注册!);
的Response.Redirect(为Login.aspx);
}
赶上(异常前)
{
的Response.Write(ex.Message);
}
终于
{
con.Close();
}
}
}
}
好像密码
是的的保留关键字的在OLE DB提供程序。用方括号像使用[密码]
。但作为一个最佳实践,将其更改为的非保留的字。
和的OleDbCommand
不支持命名参数。
从的文档;
的传球
的OLE DB .NET提供程序不支持命名的参数
参数的SQL语句或由
的OleDbCommand调用的时候CommandType设置为文本的存储过程。在这种情况下,必须使用
问号(?)占位符。例如:
SELECT * FROM WHERE客户id =
因此,顺序OleDbParameter对象是客户?加入到
OleDbParameterCollection的必须直接对应于$ b $的位置b问号占位符的命令文本
块引用>参数
在文件它说?必须使用不过的实际的,其实不然。命名参数做的工作,但名称无关;它仍然是参数中的地位
的CommandText
键,它们被添加的事项的顺序。
和不要使用
AddWithValue
了。它的可能的有时会产生意想不到的效果。使用。。新增()
方法或它的过载
最后,你不必与
con.Close()
在最后
块,因为使用
语句自动处理。
顺便说一句,我不得不说,
accessLevelIdD
列听起来像它的名字是数字类型,因为它与结束ID
。如果是(或者应该或不),你需要传递值2
不是'2'
。I couldn't find the syntax error in the following INSERT statement.
public partial class doRegister : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string str = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database"; using (OleDbConnection con = new OleDbConnection(str)) using (OleDbCommand cmd = con.CreateCommand()) { cmd.CommandText = "INSERT INTO users (staffID,accessLevelIdD,username,password,email) VALUES (@staffID, '2', @username,@password,@email)"; cmd.Parameters.AddWithValue("@staffID", Request.Form["staffid"]); cmd.Parameters.AddWithValue("@password",Request.Form["confpassword"]); cmd.Parameters.AddWithValue("@username", Request.Form["username"]); cmd.Parameters.AddWithValue("@email", Request.Form["email"]); con.Open(); try { cmd.ExecuteNonQuery(); MessageBox.Show("Successfully registered!"); Response.Redirect("Login.aspx"); } catch (Exception ex) { Response.Write(ex.Message); } finally { con.Close(); } } } }
解决方案Seems like
Password
is a reserved keyword in OLE DB Provider. Use it with square brackets like[Password]
. But as a best practise, change it to non-reserved word.And
OleDbCommand
doesn't support named parameters.From documentation;
The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:
SELECT * FROM Customers WHERE CustomerID = ?
Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.
In documentation it says ? must be used but actually, it is not. Named parameters do work, but the names are irrelevant; it's still the position of the parameters in the
CommandText
and the order in which they are added that matters.And don't use
AddWithValue
anymore. It may generate unexpected results sometimes. Use.Add()
method or it's overloads.Read: Can we stop using
AddWithValue()
already?Finally, you don't need to close your connection manually with
con.Close()
in yourfinally
block becauseusing
statement automatically handle it.By the way, I have to say,
accessLevelIdD
column sounds like a numeric type from it's name since it ends withID
. If it is (or should or not), you need to pass value as2
not'2'
.这篇关于语法错误在INSERT语句到MS访问的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!