ASP.NET getting an error when trying to insert into MSAccess DB

Question

I have a school project due soon, it is a web app in ASP.NET and I thought I had finished and had it working as intended using a local mysql database. My professor has recently instructed us to change our database to MS Access and I have most of my classes working (ones like login where it only checks the data already in the table). The last one i'm having issues with is my registration page, i'm getting an error regarding my insert statement. (An exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll but was not handled in user code

Additional information: Syntax error in INSERT INTO statement.)

I have tried tweaking various things based on other posts/youtube videos but nothing has worked thus far. I'd greatly appreciate someone pointing out where i'm going wrong! Thanks in advance.

protected void submitBtn_Click(object sender, EventArgs e)
    {
        OleDbConnection connect = new OleDbConnection(ConfigurationManager.ConnectionStrings["newregDBConnectionString"].ConnectionString);
        {                                           
            if (parentRadBtn.Checked)
            {
                if (firstNameBox.Text == "" || surnameBox.Text == "" || postcodeBox.Text == "" || teleBox.Text == "" || emailBox.Text == "" || userBox.Text == "" || passwordBox.Text == "")
                {
                    Response.Write("<script>alert('Please ensure all fields have an entry');</script>");
                    successLabel.Text = ("");
                    userBox.Text = "";
                    firstNameBox.Text = "";
                    surnameBox.Text = "";
                    postcodeBox.Text = "";
                    teleBox.Text = "";
                    emailBox.Text = "";
                    passwordBox.Text = "";
                }                 
                else
                {
                    OleDbCommand pa = new OleDbCommand("INSERT INTO parent(parentID, firstname, surname, postcode, telephone, email, password) VALUES (@parentID, @firstname, @surname, @postcode, @telephone, @email, @password)", connect);
                    pa.Parameters.AddWithValue("@parentID", userBox.Text);
                    pa.Parameters.AddWithValue("@firstname", firstNameBox.Text);
                    pa.Parameters.AddWithValue("@surname", surnameBox.Text);
                    pa.Parameters.AddWithValue("@postcode", postcodeBox.Text);
                    pa.Parameters.AddWithValue("@telephone", teleBox.Text);
                    pa.Parameters.AddWithValue("@email", emailBox.Text);
                    pa.Parameters.AddWithValue("@password", passwordBox.Text);

                    connect.Open();
                    pa.ExecuteNonQuery();
                    connect.Close();
                }

                if (IsPostBack)
                {
                    userBox.Text = "";
                    firstNameBox.Text = "";
                    surnameBox.Text = "";
                    postcodeBox.Text = "";
                    teleBox.Text = "";
                    emailBox.Text = "";
                    passwordBox.Text = "";
                }

            }           
            else if (childRadBtn.Checked)
            {
                if (firstNameBox.Text == "" || dayDobList.Text == "" || monthDobList.Text == "" || yearDobList.Text == "" || genderList.Text == "" || userBox.Text == "" || passwordBox.Text == "")
                {
                    Response.Write("<script>alert('Please ensure all fields have an entry');</script>");
                    successLabel.Text = ("");
                    userBox.Text = "";
                    firstNameBox.Text = "";
                    dayDobList.Text = "";
                    monthDobList.Text = "";
                    yearDobList.Text = "";
                    genderList.Text = "";
                    passwordBox.Text = "";
                }                 
                else
                {
                    OleDbParameter dob = new OleDbParameter("@dob", OleDbType.DBDate);
                    dob.Value = new DateTime(Int32.Parse(yearDobList.Text), Int32.Parse(monthDobList.Text), Int32.Parse(dayDobList.Text));                      

                    OleDbCommand ca = new OleDbCommand("INSERT INTO children(childID, firstname, dob, gender, password) VALUES (@childID, @firstname, @dob, @gender, @password)", connect);
                    ca.Parameters.AddWithValue("@childID", userBox.Text);
                    ca.Parameters.AddWithValue("@firstname", firstNameBox.Text);
                    ca.Parameters.Add(dob);
                    ca.Parameters.AddWithValue("@gender", genderList.Text);
                    ca.Parameters.AddWithValue("@password", passwordBox.Text);

                    connect.Open();
                    ca.ExecuteNonQuery();
                    connect.Close();
                }
                if (IsPostBack)
                {
                    userBox.Text = "";
                    firstNameBox.Text = "";
                    dayDobList.Text = "";
                    monthDobList.Text = "";
                    yearDobList.Text = "";
                    genderList.Text = "";
                    passwordBox.Text = "";
                }              
            }
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        Response.Redirect("ViewDelete.aspx");
    }

    protected void backBtn_Click(object sender, EventArgs e)
    {
        Response.Redirect("Home.aspx");
    }

    protected void emailBox_TextChanged(object sender, EventArgs e)
    {

    }

    protected void userBox_TextChanged(object sender, EventArgs e)
    {

    }

    protected void firstNameBox_TextChanged(object sender, EventArgs e)
    {

    }
}

}

score 3 · Accepted Answer · edited May 23 '17 at 11:53

From MSDN:

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. 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.

By default OleDbCommandType is set to Text, hence your parent & children query should be changed to this (note that all OleDbParameter should declared in their proper order):

// insert parent command
OleDbCommand pa = new OleDbCommand("INSERT INTO parent(parentID, firstname, surname, postcode, telephone, email, [password]) VALUES (?, ?, ?, ?, ?, ?, ?)", connect);

// insert children command
OleDbCommand ca = new OleDbCommand("INSERT INTO children(childID, firstname, dob, gender, [password]) VALUES (?, ?, ?, ?, ?)", connect);

NB: All column names which potentially conflicted with Access OLE DB reserved words should put into square brackets to prevent accidental usage of either Access JET SQL keyword or Access ACE SQL keyword (i.e. PASSWORD).

SO related issues:

What's wrong with these parameters?

insert into access database

Thank you so much, putting square brackets on password worked. I did look at reserved words briefly but didn't see password when glancing over! Again, thank you :) — ACostea, Mar 02 '17 at 08:34

ASP.NET getting an error when trying to insert into MSAccess DB

1 Answers1