MS Access & Dates
Hey all,
I have a java program that worked fine with MS Access, problem was I used the String representation of a date like:Saturday 12 May 2007 which worked fine. I could add new days and update certain days in the database.
but I needed to change the String to a date object, particularly an sql date object as I found out the hard way. so I have got my class changing my date to an sql date, which looks like this when converted and displayed2007-05-12 and i changed my MS Access table to accept a long date instead of the varchar, so this stores a date as12 May 2007.
My program runs fine, and inserts new dates no problem, but when I update the row of the same date, i get a General error.
I have noticed that no matter what I do, my else block to update a row in my table, never works, my code always goes through the Insert block.
could you tell me where I am going wrong.
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dataSourceName ="mdbTEST";
String dbURL ="jdbc:odbc:" + dataSourceName;
Connection con =null;
Statement s =null;
PreparedStatement ps =null;
SimpleDateFormat sdf =new SimpleDateFormat("EEEE dd MMMM yyyy");
java.sql.Date d =new java.sql.Date(sdf.parse(date).getTime());
System.out.println("bs.labelDate = "+bs.labelDate);
System.out.println(d);
try
{
con = DriverManager.getConnection(dbURL,"","");
s = con.createStatement();
s.execute("SELECT * FROM "+tableName+" WHERE column_1 = "+d);
ResultSet rs = s.getResultSet();
boolean found = rs.next();
if (rs !=null)// if rs == null, then there is no ResultSet to view
{
if(!found)
{
String insert ="INSERT INTO " +tableName+" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
ps = con.prepareStatement(insert);
ps.setDate(1, (java.sql.Date)d);
for(int i = 0; i <= 11; i++)
{
ps.setString(i+2,apps[i]);//setting from column_2 using begining of said array
}
for(int j = 0; j <= 4; j++)
{
ps.setInt(j+14, stats[j]);//setting from column_14 using begining of said array
}
ps.setString(19, noteData);
System.out.println("Insert Block");//prints no matter whether I am updating or inserting
ps.execute();
}//close if
else
{
String update ="UPDATE " +tableName+
" SET column_1 = ?, column_2 = ?, column_3 = ?,"+
" column_4 = ?, column_5 = ?, column_6 = ?, column_7 = ?,"+
" column_8 = ?, column_9 = ?, column_10 = ?, column_11 = ?,"+
" column_12 = ?, column_13 = ?, column_14 = ?, column_15 = ?,"+
" column_16 = ?, column_17 = ?, column_18 = ?, column_19 = ?"+
" WHERE column_1 = ?";
ps = con.prepareStatement(update);
ps.setDate(1, (java.sql.Date)d);
for(int i = 0; i <= 11; i++)
{
ps.setString(i+2,apps[i]);//setting from column_2 using begining of said array
}
for(int j = 0; j <= 4; j++)
{
ps.setInt(j+14, stats[j]);//setting from column_14 using begining of said array
}
ps.setString(19, noteData);
ps.setDate(20,(java.sql.Date)d);//set the ? in where clause in update statement
System.out.println("Update Block");
ps.executeUpdate();
}//close if
}
}
catch (Exception err)
{
System.out.println("\nERROR: ");
err.printStackTrace();
}
finally
{
s.close();
con.close();
}
}
catch (Exception err)
{
System.out.println("\nERROR from outside Catch statement: ");
err.printStackTrace();
}
}
here is my StackTrace:
ERROR:
java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at Database.data(Database.java:77)
at TestBookingSheet.save(TestBookingSheet.java:402)
at TestBookingSheet.actionPerformed(TestBookingSheet.java:424)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
I have been stuck at this for so long.
davy
Message was edited by:
davyk
Message was edited by:
davyk
