Syntax error in INSERT INTO statement using OleDb

Question

Good day. I am trying to make a registration page and have the information stored in a database. I made the database using Microsoft Access. I get:

Syntax error in INSERT INTO statement

every time I press the 'Register' button. I have already tried searching on the net with similar problems and found some things like "Reserved Words" and "It must be your spacing". I did those and it still gives me the error. Am I missing something?

Here is the code:

public void InsertRecord()
{
    OleDbCommand cmd = new OleDbCommand("INSERT INTO ElemData(StudentID, [Password], [Name], Age, Birthday, Address, FatherName, MotherName, " +
    "GuardianName, Class, Section, Email, PhoneNumber, MobileNumber) " + 
    "VALUES (@studentid, @password, @name, @age, @birth, @address, @father, @mother, @guardian, @classs, @section, @email, @phone, @mobile)", DBConnection.myCon);
    cmd.Parameters.Add("@studentid", OleDbType.VarChar).Value = Studentid;
    cmd.Parameters.Add("@password", OleDbType.VarChar).Value = Password;
    cmd.Parameters.Add("@name", OleDbType.VarChar).Value = Name;
    cmd.Parameters.Add("@age", OleDbType.VarChar).Value = Age;
    cmd.Parameters.Add("@birth", OleDbType.VarChar).Value = Birth;
    cmd.Parameters.Add("@address", OleDbType.VarChar).Value = Address;
    cmd.Parameters.Add("@father", OleDbType.VarChar).Value = Father;
    cmd.Parameters.Add("@mother", OleDbType.VarChar).Value = Mother;
    cmd.Parameters.Add("@guardian", OleDbType.VarChar).Value = Guardian;
    cmd.Parameters.Add("@classs", OleDbType.VarChar).Value = Classs;
    cmd.Parameters.Add("@section", OleDbType.VarChar).Value = Section;
    cmd.Parameters.Add("@email", OleDbType.VarChar).Value = Email;
    cmd.Parameters.Add("@phone", OleDbType.VarChar).Value = Phone;
    cmd.Parameters.Add("@mobile", OleDbType.VarChar).Value = Mobile;
    if (cmd.Connection.State == ConnectionState.Open)
    {
        cmd.Connection.Close();
    }
    DBConnection.myCon.Open();
    cmd.ExecuteNonQuery();
    DBConnection.myCon.Close();
}

Solution 1

Class and Section are both reserved words. Enclose them in square brackets as you have done for the reserved words [Password] and [Name].

That page includes a link for Allen Browne's Database Issue Checker Utility. If you have a version of Office which includes Access, you can download that utility and use it to examine your Access db file for other problem object names.

Solution 2

change your sql as below

 OleDbCommand cmd = new OleDbCommand("INSERT INTO ElemData ([StudentID], [Password], [Name], [Age], [Birthday], [Address], [FatherName], [MotherName], " +
    "[GuardianName], [Class], [Section], [Email], [PhoneNumber], [MobileNumber]) " + 
    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", DBConnection.myCon);

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.

Comments

[Meruru]

It still gives me the same error. We have used this code in class and it works though the table used has only 4 columns. ElemData here has around 48 columns, most of it being 'Subject1', 'Subject2' and so on. Now I am wondering if the table having 48 columns is causing this because I left some columns blank as seen on the code.

[Meruru]

Thank you very much! It worked now. I guess I shouldn't just search for one list of Reserved Words only because the one I found didn't include these. Thanks for the link as well. Since I can't give reputation, I will just comment here to notify you it worked.