• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to insert date into MS Access Date/Time?

 
J.D. Thompson
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34973
378
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
JD,
Can you use a PreparedStatement instead? Then you call prepStmt.setDate() instead of wrestling with the format.
 
J.D. Thompson
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34973
378
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic