Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help with MS Access & Date

 
Davy Kelly
Ranch Hand
Posts: 384
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Davy Kelly
Ranch Hand
Posts: 384
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have to create a java.sql.Date from your java.util.Date:Regards, Jan
[ May 09, 2007: Message edited by: Jan Cumps ]
 
Davy Kelly
Ranch Hand
Posts: 384
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 1
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic