Syntax error in INSERT statement into MS Access

Question

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

Soner Gönül · Accepted Answer · 2015-01-29 09:38:37Z

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 your finally block because using 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 with ID. If it is (or should or not), you need to pass value as 2 not '2'.

Collectives™ on Stack Overflow

Syntax error in INSERT statement into MS Access

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related