Insert Date in Java to Access DB - Weird issue

Question

I'm trying to update a table in my AccessDB and i'm having a weird problem. The update executes without throwing any exceptions but the date value is wrong and everytime i update a record the value always changes to "30/12/1899". Same thing hapens when i'm trying to insert a new record.

In my DB the Date field is in ShortDate format.

Here is an example of my code:

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         

    DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT);

    if (jList1.isSelectionEmpty()) {
        JOptionPane.showMessageDialog(null, "You have not selected any computer!");
    } else {
        try {
        String sql = "Update SYSTEMS set "
            + " CPU='" + cpuTextField.getText().trim()
            + "', MOBO='" + moboTextField.getText().trim()
            + "', RAM='" + ramTextField.getText().trim()
            + "', GPU='" + gpuTextField.getText().trim()
            + "', HDD='" + hddTextField.getText().trim()
            + "', PSU='" + psuTextField.getText().trim()
            + "', MONITOR='" + monitorTextField.getText().trim()
            + "', KEYBOARD='" + keyboardTextField.getText().trim()
            + "', MOUSE='" + mouseTextField.getText().trim()
            + "', OS='" + osTextField.getText().trim()
            + "', SOFTWARE='" + othersTextArea.getText().trim()
            + "', PURCHASE_DATE=" + df.format(jDateChooser1.getDate())
            + " where SYSTEM_ID='" + jList1.getSelectedValue().toString() + "'";

        st = con.prepareStatement(sql);
        st.executeUpdate();

        } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
        }
        JOptionPane.showMessageDialog(null, "Updated");
    }
    }

In order to figure out what is going wrong, I made a button and when pressed i had a Message showing the result of df.format(jDateChooser1.getDate()) and it showed the correct date.

private void jButton7ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT);
    JOptionPane.showMessageDialog(null, df.format(jDateChooser1.getDate()));
}

I'm using this component to get the date: JCalendar If that makes any difference. I dont mind replacing it with a plain TextField, as long as the date is imported correctly. When using select to retrieve the date from the DB everything goes well. The problem only occurs when updating/inserting.

maerics · Accepted Answer · 2012-01-17 16:41:19Z

2

The problem likely has to do with the formatting of the SQL query; use a PreparedStatement instead of formatting it manually. Doing so will also decrease the likelihood of errors related to validating user input, including security issues such as SQL injection. For example:

String sql = "Update SYSTEMS set "
    + " CPU=?, MOBO=?, RAM=?"
    + //...
    + ", PURCHASE_DATE=?"
    + " where SYSTEM_ID=?";
PreparedStatement stmt = con.prepareStatement(sql);
int nextField = 1;
stmt.setString(nextField++, cpuTextField.getText().trim());
stmt.setString(nextField++, moboTextField.getText().trim());
stmt.setString(nextField++, ramTextField.getText().trim());
// ...
stmt.setDate(nextField++, jDateChooser1.getDate());
stmt.setString(nextField++, jList1.getSelectedValue().toString());
stmt.executeUpdate();

[Edit] Note that the PreparedStatement#setDate() method requires a java.sql.Date, so you may need to convert the date type returned by your date chooser into one of those, e.g.:

stmt.setDate(nextField++,
    new java.sql.Date(jDateChooser1.getDate().getTime()));

edited Jan 17, 2012 at 16:41

answered Jan 17, 2012 at 16:18

maerics

157k47 gold badges277 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Christos Baziotis Over a year ago

I dont know how to do it. Can you give my an example or a link to read?

Christos Baziotis Over a year ago

Thanks, I'll give it a try and i will post back.

Christos Baziotis Over a year ago

I'm getting an error: no suitable method found for setDate(int,java.util.Date) stmt.setDate(nextField++, jDateChooser1.getDate());

maerics Over a year ago

@sijoune: double-check the types required by the setDate() method and returned by the date chooser getDate() method. Ultimately, I guess it depends on the JDBC driver you're using; see what its documentation says about dates.

Christos Baziotis Over a year ago

method PreparedStatement.setDate(int,java.sql.Date,Calendar) is not applicable (actual and formal argument lists differ in length) method PreparedStatement.setDate(int,java.sql.Date) is not applicable (actual argument java.util.Date cannot be converted to java.sql.Date by method invocation conversion). Should i use java.sql.Date instead. I tried it before and i was getting different dates.

Sam Goldberg · Accepted Answer · 2012-01-17 16:40:37Z

0

Access requires dates to specified in format #MM/dd/yyyy# (including the hash marks). So if you add the # delimiters at the beginning and end of the date string, it should work. As maerics suggested, the best would be to use PreparedStatement, because the JDBC drive will handle converting Java Date to the format Access understands, without you needing to format the value.

answered Jan 17, 2012 at 16:40

Sam Goldberg

6,8218 gold badges57 silver badges85 bronze badges

Comments

david a. · Accepted Answer · 2012-01-17 16:21:40Z

0

Looks like your date format is different from what Access expects. To get rid of it, use name parameters - as at http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html#supply_values_ps, rather than concatenating the SQL on your own.

answered Jan 17, 2012 at 16:21

david a.

5,28924 silver badges25 bronze badges

Collectives™ on Stack Overflow

Insert Date in Java to Access DB - Weird issue

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related