File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to insert date into MS Access Date/Time? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to insert date into MS Access Date/Time?" Watch "How to insert date into MS Access Date/Time?" New topic
Author

How to insert date into MS Access Date/Time?

J.D. Thompson
Greenhorn

Joined: Mar 08, 2006
Posts: 28
Cannot seem to find the right format to provide to a MS Access DB for the Date/Time Field.

Table Name: holiday

holiday field names in order: record(type: int), holiday(type: text), include(type: yes/no), dateValue(type ate/Time).

If I remove the dateValue field from the table the SQL insert statement works fine so this reduces the problem to strictly how I am providing information for the dateValue field.


//======================================================================
int recNum=1;
boolean include=true;
String name="New Years Day Observed";
java.sql.Date temp=new java.sql.Date(new java.util.Date().getTime());

String s1="Insert into holiday values ("+recNum+", '"+name+"',"+include+", #"+ temp+"#)";
//=======================================================================

I then pass the s1 as the sql to my DB Accessor class which handles the JDBC-ODBC function of preparing the statement, connection, etc.

I have tried single quotes, 'temp', single pound, #temp#, and double pound, ##temp##, to no avail.

I am currently using Java 1.5, a JDBC-ODBC bridge, and MS Access 2000 (Ver 9.0).

I hope I have provided the pertinent information and eagerly await your help. I have wrestled with this since last Thursday and have exhausted all resources I am aware of.

Thanks and have a great day!

JD
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30404
    
150

JD,
Can you use a PreparedStatement instead? Then you call prepStmt.setDate() instead of wrestling with the format.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
J.D. Thompson
Greenhorn

Joined: Mar 08, 2006
Posts: 28
Hi Jeanne!

Thanks for responding!

I will try and modify to use a prepared statement as for the application long-term that would be a better approach.

I was able to determine that the syntax is correct, through much T/S last night. For those that read this thread in the future, the above "Insert.." sql works.

The culprit in my code was that I never closed the connection.

When I create the DBAccessor:

DBAccessor a1=new DBAccessor("timeCard",[qry string herer],true);

I forgot to close the connection to the dB:

a1.closeCon();

For some reason, this was preventing the update to the dB from showing. Once I did this, everything worked fine.

Do you have any ideas as to why keeping the connection open preventing me from seeing the rows added to the dB? Or is this a quirk associated with Acccess? I am guessing that the table was locked due to the open connection and therefore not accepting new inserts....?

Again, thanks tons for your quick response.

Hope you have a great day!

JD
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30404
    
150


Some drivers don't do the database commit until you close the connection. I don't enough about Access to say whether this is the case here. But it is a possible explanation for why you weren't seeing the updates.
 
jQuery in Action, 2nd edition
 
subject: How to insert date into MS Access Date/Time?