How to insert date into MS Access Date/Time?

[J.D. Thompson]

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]

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

[J.D. Thompson]

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]

Do you have any ideas as to why keeping the connection open preventing me from seeing the rows added to the dB?

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.