Syntax error in INSERT INTO statement ASP.Net C#

Question

First of all, yes I know there are several other questions about this. I have reviewed and tried different solutions proposed in them; however I'm still getting a "Syntax error INSERT INTO statement" message when I click on the submit button to run the function.

I'm running VS 2015 and an Access 2010 db. The full error message is as follows:

Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at createAcc.btnCreateAccount_Click(Object sender, EventArgs e) in e:\Documents\Visual Studio 2015\WebSites\OneStopFurniture\createAcc.aspx.cs:line 43

My code for the button handler is as follows:

protected void btnCreateAccount_Click(object sender, EventArgs e)
{
    connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\\Documents\\Visual Studio 2015\\WebSites\\OneStopFurniture\\App_Data\\OneStopFur.accdb";
    string InsertQuery;
    try
    {
        connection.Open();
        OleDbCommand cmdInsert = new OleDbCommand();
        cmdInsert.Connection = connection;

        InsertQuery= "INSERT INTO [userInfo] (
           userName, password, firstName, lastName, userAddress, userCity, userZip, userPhone, userEmail
        ) VALUES (
           @userName, @password, @firstName, @lastName, @userAddress, @userCity, @userState, @userZip, @userPhone, @userEmail
        )";
        cmdInsert.Parameters.AddWithValue("@userName", txtUserName.Text);
        cmdInsert.Parameters.AddWithValue("@password", txtPassword.Text);
        cmdInsert.Parameters.AddWithValue("@firstName", txtFirstName.Text);
        cmdInsert.Parameters.AddWithValue("@lastName", txtLastName.Text);
        cmdInsert.Parameters.AddWithValue("@userAddress", txtAddress.Text);
        cmdInsert.Parameters.AddWithValue("@userCity", txtCity.Text);
        cmdInsert.Parameters.AddWithValue("@userState", txtState.Text);
        cmdInsert.Parameters.AddWithValue("@userZip", txtZip.Text);
        cmdInsert.Parameters.AddWithValue("@userPhone", txtPhone.Text);
        cmdInsert.Parameters.AddWithValue("@userEmail", txtEmail.Text);

        cmdInsert.CommandText = InsertQuery;
        cmdInsert.ExecuteNonQuery();
        lblConfirm.Visible = true;
        lblConfirm.Text = "Account creation successful.";
    }
    catch (Exception ex)
    {
        lblConfirm.Visible = true;
        lblConfirm.Text = "Unable to create" + ex;

    }
}

Can someone please tell me what I am overlooking here? At this point, I'm at a complete lose.

Thanks.

Better query formatting would have helped you find this error yourself. Look at the change I made and how the target columns now line up somewhat with the values? It's MUCH easier to scan and compare, now. — ErikE
– ErikE, Commented Oct 5, 2015 at 17:10

Lukasz Szozda · Accepted Answer · 2015-10-05 16:57:14Z

4

Missing UserState in column list.

InsertQuery= "INSERT INTO [userInfo]
            ([userName], [password], [firstName], [lastName], [userAddress], [userCity], [userState], [userZip], [userPhone], [userEmail])
     VALUES(@userName, @password, @firstName, @lastName, @userAddress, @userCity, @userState, @userZip, @userPhone, @userEmail)";

The good practise is to qoute everything to avoid collision with keywords.

edited Oct 5, 2015 at 16:57

answered Oct 5, 2015 at 16:51

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lukasz Szozda Over a year ago

@Steve You mean password keyword?

Steve Over a year ago

Yes, the Syntax Error is caused by the word Password, then the poster will get the error caused by the unexpected parameter.

Steve · Accepted Answer · 2015-10-05 17:03:10Z

The immediate error is caused by the word Password. This is a reserved keyword in MS-Access. Use square brackets around it.

InsertQuery= @"INSERT INTO [userInfo](userName, [password], firstName, 
  lastName, userAddress, userCity, userState, userZip, 
  userPhone, userEmail) 
  VALUES(@userName, @password, @firstName, 
  @lastName, @userAddress, @userCity, @userState, @userZip, 
  @userPhone, @userEmail)";

After fixing this error you need also to add the column UserState (or whatever is called) because you have added a parameter for it.

Keep in mind that in OleDb the parameters are positional. This means the the first field receives the value from the first parameter, whatever name you have used, so missing a field or inverting the position of a parameter in the collection could cause bugs very difficult to spot. Double your checks here.

Collectives™ on Stack Overflow

Syntax error in INSERT INTO statement ASP.Net C#

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related