aspose file tools*
The moose likes JDBC and the fly likes Help with MS Access & Date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help with MS Access & Date" Watch "Help with MS Access & Date" New topic
Author

Help with MS Access & Date

Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
Hey everyone,

I have a working application that can write/update information to MS access.

I use Dates in my program, to show a booking sheets date. I use these as Strings right now, but I was wondering, if you could help me that is, what date should I set as one of my MS Access database fields, e.g:
General Date 19/06/1994 17:34:23
Long Date 19 June 1994
Medium Date 19-Jun-94
Short Date 19/06/1994
Also, I want to be able to make my String representation of a date, get turned to a date obeject, used in the database, then turned back to the String again. can this be done?
My format is a SimpleDateFormat Tuesday 08 May 2007.

Would I use the SimpleDateFormat to e.g.:


would this work?

or would this be better:


I have the date as my main working part of the database, as primary field, and I will ned to sort this in order later to get statistics, hence why I need to convert.

davy
[ May 08, 2007: Message edited by: Davy Kelly ]

How simple does it have to be???
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I use these as Strings right now, but I was wondering, if you could help me that is, what date should I set as one of my MS Access database fields

If the data is a date, then a date/time data type is a much more appropriate data type to use. It makes sense to have a field that only allows dates, rather than any abitrary text (otherwise inserting 'ABC' is just as valid as insering 12/12/01 00:00:00).

I think dates in Access are really stored as a kind of wierd floating point number; left of the decimal point represents the date, right represents the time. When you see a format (such as General Date 19/06/1994 17:34:23) it's just that, a format, not the data itself. All Access is doing is applying a mask to the data to render it in a particular way it doesn't change how the date/time is stored.


Also, I want to be able to make my String representation of a date, get turned to a date obeject, used in the database, then turned back to the String again. can this be done?

Yes. You need to use a PreparedStatement and the setDate() method. This is a much better way of handling dates, since it removes all of the formatting issues from your code and delegates these to the driver.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
i tried this code:


I made my Access column_1 a long date, made it primary key.

I get an exception saying - "java.lang.ClassCastException: java.util.Date cannot be cast to java.sql.Date" here is the full stackTrace:


I checked the API, but I cant see what to do.
How can I fix this so that I can store a Date from the String?
I mainly want to change the string, cause I have taken 3 months on this, and dont want to go all over it and change the code for all the Strings to be dates.

davy
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

You have to create a java.sql.Date from your java.util.Date:Regards, Jan
[ May 09, 2007: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
youtube channel
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
hey everyone,

I am still having problems with this.

I have a date as a String working in my program, but I want to change that String, in a different class, to an sql date so that I can store data to the database using this date.

this is my code so far which seems to go through the insert block all the time.



my database worked with column_1 set as varchar and I could store the date as a string, but i changed the database to a long date, which outputs on the database as 12 May 2007.
I am converting my String to an sql date which outputs as 2007-05-12.

could you help me out with this problem, so that I can store an sql date in MS Access so that my database can work?

davy
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


my database worked with column_1 set as varchar and I could store the date as a string, but i changed the database to a long date, which outputs on the database as 12 May 2007.

See my earlier post, databases don't store a date with a format, dates are just numbers to them. The format is only applied when the date is rendered.

Have a look at java.text.SimpleDateFormat. This is what you use to define how you want your date to appear in your application.
[ May 14, 2007: Message edited by: Paul Sturrock ]
Stella Yulianti
Greenhorn

Joined: Jun 03, 2007
Posts: 1
Hello.. newbie needs help in inserting Date/Time in JSP and Ms.Access..
I've tried using simpleDateFormat but it didn't work either..

this is my JSP form code
<td class="style3">:
<input name="date" type="text" id="date" value="<% org.joda.time.DateTime dt = new org.joda.time.DateTime();%>
<%= dt.toString("yyyy-MM-dd hh:mm:ss") %>" size="50" readonly="true"></td>


this is my JSP insert code
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>
<%

String date = request.getParameter("PostDate");
String name = request.getParameter("Name");
String email = request.getParameter("Email");
String msg = request.getParameter("Message");

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbcdbc:driver={Microsoft Access Driver (*.mdb)};DBQ="+application.getRealPath("/WEB-INF/data.mdb");
Connection con = DriverManager.getConnection(url);
Statement state = con.createStatement();

String query = "insert into GuestBook values ('" + date + "','"+ name + "','" + email + "','" + msg + "')";

state.executeUpdate(query);
response.sendRedirect("guestbook.jsp");
%>


and the result
root cause

javax.servlet.ServletException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:858)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)
org.apache.jsp.insertGuestBook_jsp._jspService(insertGuestBook_jsp.java:69)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:368)
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Welcome to JavaRanch Stella Yulianti.

It is normally considered bad form to post another question on someone else's topic, but since the problem is related I can see why you might think it is an appropriate place for it.

Have a read of the posts above yours. Try using a PreparedStatement, rather than a Statement.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help with MS Access & Date