UCAExc:::3.0.7 unexpected token: logDate

Question

Good day, all. I am working on a personal project that needs to interact with an ms access 2016 db. My java application gets data from a user and this info is stored in an Object[]. I am trying to insert the elements of my obj array to a table in my db. This is my code:

Connection conn = null;
PreparedStatement pstmnt = null;
String sql = null;
ResultSetMetaData md = null;
Statement stm = null;
ResultSet rs = null;
int i = 0;
String q = "SELECT * from QueryData";
try{
    conn = DriverManager.getConnection("jdbc:ucanaccess://filePath");
    stm = conn.createStatement();
    rs = stm.executeQuery(q);
    md = rs.getMetaData();
    int count = md.getColumnCount();
    String[] colName = new String[count];
    for (int n = 1; n <= count; n++)
        colName[n-1] = md.getColumnLabel(n);
    while ( i <= data.length) {//data being the object array containing the data to be inserted in db
        query = "INSERT into QueryData ('"+colName[i]+"') VALUES ('"+data[i]+"')";
            //The following code is where I get the exception
        pstmnt = conn.prepareStatement(query);
        //some more code follows..

On the first pass throught the while loop, colName[i] is "logDate" which is the first field in the table and data[i] is a LocalDate object formatted as 2016-12-23. I know I did not close the while loop above nor did I given the catch clause, but my program does not run past the pstmnt assignment. I keep getting the exception "net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 unexpected token: logDate".

Any assistance will be greatly appreciated as I've scoured the web amd this forum but could not find a working solution to my problem.

Answer 1

You are surrounding your column name with quotes, which isn't allowed. You can use square brackets instead (although not really necessary unless you have spaces in the field names, which Access allows).

query = "INSERT into QueryData (["+colName[i]+"]) VALUES ('"+data[i]+"')";

You might also need to use # instead of ' to delimit the date value. Access used to use # for date delimiters, and I'm not sure if more recent versions accept ':

query = "INSERT into QueryData (["+colName[i]+"]) VALUES (#"+data[i]+"#)";