Ucanaccess error - unexpected token

Question

//golfer and CourseName are strings initialised earlier 
String query = "INSERT INTO Temp (GolferID,CourseID,DatePlayed,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th,11th,"
            + "12th,13th,14th,15th,16th,17th,18th)VALUES('" 
            + golfer + "','" + CourseName + "',#1/1/2011#";

    for(int j = 0; j <=17; j++)
    {
        query = query + "," + Scores[j];
    }
    query = query + ")";
    System.out.println(query);
//INSERT INTO  Temp(GolferID,CourseID,DatePlayed,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th,11th,12th,13th,14th,15th,16th,17th,18th)VALUES('test','Blue Valley CC',#1/1/2011#,4,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)*/
\\this is what the string eventually looks like.

    DBConnect db = new DBConnect();
    db.InsertGame(query);

this is the relevant code in DBConnect - InsertGame...

connection = DriverManager.getConnection("jdbc:ucanaccess://Golf.accdb");
statement = connection.createStatement();//declared earlier
statement.execute(query);

This keeps giving me the following errors: SEVERE: null net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: 2 net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:109)"

If i copy and paste the query straight into access it executes perfectly. I am using the same method of inserting into another table in the database, that works perfectly fine however all those fields are text fields. Im not too sure if that makes a difference.

Answer 1

I was able to recreate your issue under UCanAccess 3.0.0 using the following code:

sql = 
        "INSERT INTO Temp (GolferID,CourseID,DatePlayed,1st,2nd,3rd) " +
        "VALUES ('test','Blue Valley CC',#1/1/2011#,4,3,5)";

UCanAccess seems to be getting confused by the column name 2nd. I was able to successfully execute the statement when I enclosed the column names in square brackets:

sql = 
        "INSERT INTO Temp (GolferID,CourseID,DatePlayed,[1st],[2nd],[3rd]) " +
        "VALUES ('test','Blue Valley CC',#1/1/2011#,4,3,5)";