when i update a row in an access database the date field does not update to the correct date it brings up some funny date which i dont know where it comes from. Below is the method i use to update. maybe the date types are not compatable? thanks in advance, public static void bookIN(int custID, int rNumber,java.sql.Date bkInDate ){ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String filename = "hotelDB.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; Connection con = DriverManager.getConnection( database ,"",""); int yes = 1; Statement s = con.createStatement(); s.executeUpdate("update Rooms set CustomerId ="+custID+",BookINDate ="+ bkInDate +",Occupied = "+yes+" where RoomNo = "+rNumber+" "); s.close(); con.close(); } catch (Exception err) { System.out.println("ERROR: " + err); } }
"chose" wrote... > when i update a row in an access database the date field > does not update to the correct date it brings up some > funny date which i dont know where it comes from. It comes from db's attempt to parse an invalid date-string... In Access/Jet, it needs to be enclosed within #:s, and with the date parts in a specific order... Another thing is that java.sql.Date only contains the date part, whereas the DateTime data type in Access/Jet is more like a java.sql.Timestamp. > Below is the method i use to update. maybe > the date types are not compatable? Well, partly, but you're not using the date AS a date in the SQL statement, but as a String. This will in most cases lead to errors, as the parsing of such is left to the database, not the driver... > s.executeUpdate("update Rooms set CustomerId=" + custID > + ", BookINDate = " + bkInDate + ", Occupied = " + yes > + " where RoomNo = " + rNumber + " "); It's really bad practice to concatenate the string for an SQL statement in that way, because of differences between different dbs. Instead you should use a parameterized statement. Use java.sql.Timestamp instead of java.sql.Date. http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Timestamp.html http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Date.html Read up on how to use a PreparedStatement, and it will solve your problem. http://java.sun.com/j2se/1.5.0/docs/api/java/sql/PreparedStatement.html // Bjorn A
On 9 Nov 2005 12:15:32 -0800, "chose" <busanil@gmail.com> wrote, quoted or indirectly quoted someone who said : >when i update a row in an access database the date field does not >update to the correct date it brings up some funny date which i dont >know where it comes from. note that there are two date classes, java.util.Date and java.sql.Date. -- Canadian Mind Products, Roedy Green. http://mindprod.com Java custom programming, consulting and coaching.
"Roedy Green" wrote... > "chose" wrote: >> when i update a row in an access database the date field >> does not update to the correct date it brings up some >> funny date which i dont know where it comes from. > > note that there are two date classes, java.util.Date and > java.sql.Date. If you had written "Date classes" instead of "date classes" I would agree... ;-) But there are actually *more* than two date classes. In connection to JDBC, there are *four* "date"-classes to deal with. - There's our usual java.util.Date. Then there are the three classes derived from java.util.Date - java.sql.Date *derives* from java.util.Date, but "truncates" the "time part". - java.sql.Time *derives* from java.util.Date, but "truncates" the "date part". - java.sql.Timestamp *derives* from java.util.Date, but extends it with an attribute for nanoseconds. I'm not sure of the reasons behind Suns decision to name java.util.Date to only "Date", as it can cause much confusion, as in *many* other environments a "Date type" is just the date. I've met many developers that were confused when they couldn't find a java.util.Time class... ;-) // Bjorn A
Bjorn Abelli wrote: > I'm not sure of the reasons behind Suns decision to name java.util.Date to > only "Date", as it can cause much confusion, as in *many* other environments > a "Date type" is just the date. Like in unix shells - bash for example? Guess what "time" and "date" do for you there. ;-)