Syntax error in INSERT INTO statement ASP.Net C#

Question

Syntax error in INSERT INTO statement ASP.Net C#

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.

c#

sql

asp.net

ms-access

asked on Stack Overflow Oct 5, 2015 by

tworley1977 • edited Oct 5, 2015 by

ErikE

2 Answers

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.

answered on Stack Overflow Oct 5, 2015 by

Lukasz Szozda • edited Oct 5, 2015 by

Lukasz Szozda

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.

answered on Stack Overflow Oct 5, 2015 by

Steve • edited Oct 5, 2015 by

Steve

User contributions licensed under CC BY-SA 3.0