c# - Insert 语句中的语法不正确

Question

打开和关闭连接:

OleDbConnection conn;
private void ConnectToDatabase()
{
    // Creates a connection to the database using an absolute path.
    conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +Server.MapPath("App_Data\\BookRatings.accdb"));
    // Opens the connection.
    conn.Open();
}
private void DisconnectDatabase()
{
    // The connection is closed.
    conn.Close();
}

注册用户

 public void RegisterCustomer(string userName, string Address, string Tel, string Email, string Ques, string Ans, string Pass)
{
    // Connect to database.
    ConnectToDatabase();
    // Inserts the necessary values into the database.
    OleDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = (@"INSERT INTO user ([userName], [Address],[telephone], [emailAddress], [Password], [securityQuestion], [securityAnswer]) VALUES ('" + userName + "', '" + Address + "', '" + Tel + "', '" + Email + "', '" + Pass + "', '" + Ques + "', '" + Ans + "')");
    cmd.ExecuteNonQuery();
    // The connection is closed.
    DisconnectDatabase();
}

错误信息

Server Error in '/' Application.

Syntax error in INSERT INTO statement.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Source Error:

Line 100: cmd.ExecuteNonQuery(); Line 101: // The connection is closed. Line 102: DisconnectDatabase(); Line 103: } Line 104: [WebMethod]

Source File: *\bookClub\Service.aspx.cs Line: 102

Stack Trace:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1102900
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +247
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +189
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +162
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +107
Service.RegisterCustomer(String userName, String Address, String Tel, String Email, String Ques, String Ans, String Pass) in *\bookClub\Service.aspx.cs:102 Register.btnRegister_Click(Object sender, EventArgs e) in *\bookClub\Register.aspx.cs:46
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9752490
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +196
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

我不明白为什么 close line 会出现问题？或者为什么它在插入时崩溃...

Answer 1

用户是 reserved word 在 MS Access 中，因此只需更新您的 SQL 命令以将 USER 括在方括号中:

cmd.CommandText = (@"INSERT INTO [user] ([userName]....

此外，如评论中所述，最好使用参数化查询来防止 SQL 注入(inject)攻击。

看看这个问题的答案，告诉你如何做到这一点:

Using parameters inserting data into access database

此外，在该示例中，它使用了一个 Using block ，它处理关闭和处置您的连接，这比手动执行要好。您的代码看起来像这样，带有参数和 Using block :

conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +Server.MapPath("App_Data\\BookRatings.accdb"));
conn.Open();

// DbCommand also implements IDisposable
using (OleDbCommand cmd = conn.CreateCommand())
{
   // create command with parameter placeholders
   cmd.CommandText = 
        @"INSERT INTO [user] ([userName], [Address],[telephone]....) " +
         "VALUES (@username, @address, @telephone,....)");

   // add named parameters
   cmd.Parameters.AddRange(new OleDbParameter[]
   {
        new OleDbParameter("@userName", userName),
        new OleDbParameter("@address", Address),
        new OleDbParameter("@telephone", Tel),
        ...
    };

    // execute
    cmd.ExecuteNonQuery();
}