While inserting a date in MS-Access an Error Occured in java

Question

This is code through which I want to insert a date in to MS-Aceess database

  try { 
    pst = con.prepareStatement("insert into InOut (Date) Values(?)");
    pst.setString(1,jTextField3.getText());
    pst.executeUpdate();
    JOptionPane.showMessageDialog(null,"Saved Successfully.");
  } catch(Exception xp1) {
    xp1.printStackTrace();
    JOptionPane.showMessageDialog(null,xp1.getMessage());
    return;
  }

But while running said code following error occured :-

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]
Syntax error in INSERT INTO statement
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
at sun.jdbc.odbc.JdbcOdbc.SQLPrepare(JdbcOdbc.java:4837)
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:475)
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:443)

Please help me.....

Gord Thompson · Answer 1 · 2014-04-05T11:33:28.270

1

DATE is a reserved word in Access SQL so if you have to use it as a column name you need to enclose it in square brackets like this:

pst = con.prepareStatement("insert into InOut ([Date]) Values(?)");
ps.setString(1, "2011-12-31");
ps.executeUpdate();

edited Apr 05 '14 at 11:33

answered Apr 05 '14 at 11:19

Gord Thompson

116,920
32
215
418

score 0 · Accepted Answer · edited May 23 '17 at 10:26

As mentioned by @Gord Thompson You can cast a value inside native sql query. But cast the variable jTextField3.getText() to Date before sending it into the sql. Because it is a UI input, use Date parser to first parse it and then convert it into Date.

I assume the name of the column in database is userDateColumn. You will have to enforce to the user to enter the date in a particular format else you will get exception.

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

try { 
   String userInput = jTextField3.getText();
   // Convert UserInput Into Date
   Date userDate = sdf.parse(userInput);

   pst = con.prepareStatement("insert into InOut (userDateColumn) Values(?)");

    // Send that is the input to the sql 
   pst.setString(1,userDate);

Check this SimpleDateFormat ignoring month when parsing

Thanks for your comment, for better understanding of me can you pls, put code of the same please ..... — Sandee1983, Apr 05 '14 at 10:09

While inserting a date in MS-Access an Error Occured in java

2 Answers2