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

Doug

what are the column types in ElemData table? – Nahuatl

They are currently to 'TEXT'. – Doug

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

Coinsurance

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

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

Nahuatl

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