Incorrect Syntax in Insert statement

Question

Open and Close Connection:

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

Register User

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

Error Message

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

I don't understand why the close line is giving an issue? Or why it's crashing on insert...

Your code in vulnerable to SQL injection attacks, use SQL parameters instead of concatenating the parameters directly into the string. — Albin Sunnanbo, Jul 29 '14 at 07:48
Hi, did you know that your code is prone to [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks? It could also be the source of the error you're getting though I think Tanner is more right in that regard. You should definitely not write your code concatenating the SQL like that, but instead use SQL parameters. — Lasse V. Karlsen, Jul 29 '14 at 07:48

score 4 · Accepted Answer · edited May 23 '17 at 11:50

User is a reserved word in MS Access so simply update your SQL command to wrap USER in square brackets:

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

Also, as mentioned in the comments, it's better to use a parameterized query to prevent SQL injection attacks.

Take a look at the answer on this question that shows you how you can do this:

Using parameters inserting data into access database

Also, in that sample, it uses a Using block, which handles closing and disposing your connection, which is better than manually doing it. Your code would look something like this with parameters and a 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();
}

+1 - I think he also shouldn't have the extra methods ConnectToDatabase() and DisconnectDatabase() - a using block would be better.. — therak, Jul 29 '14 at 08:06

Incorrect Syntax in Insert statement

1 Answers1

Using parameters inserting data into access database