f



how do you update a java.sql.date type in an ms access database

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);
            }
      }

0
busanil (1)
11/9/2005 8:15:32 PM
comp.lang.java.help 13340 articles. 0 followers. Post Follow

4 Replies
965 Views

Similar Articles

[PageSpeed] 56

"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


0
bjorn_abelli (300)
11/9/2005 8:49:33 PM
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.
0
11/10/2005 12:52:04 AM
"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


0
bjorn_abelli (300)
11/10/2005 11:53:52 AM
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. ;-)
0
igoplan (47)
11/10/2005 12:35:22 PM
Reply:

Similar Artilces:

Cast from java.util.DATE to java.sql.DATE
DB ORACLE Calendar stCal = Calendar.getInstance(); stmt.setDate(COLUMN_DATA, stCal.getTime()); doesn't work because setDate method Sorry for the first post.... DB ORACLE Calendar stCal = Calendar.getInstance(); stmt.setDate(COLUMN_DATA, stCal.getTime()); doesn't work because setDate method (int, java.sql.Date) in the type PreparedStatement is not applicable for the arguments (int, java.util.Date) If I try with: stmt.setDate(COLUMN_DATA, (java.sql.Date)stCal.getTime()); I obtain a class cast exception Any solution? How can I insert a java.util.date obtained from a calendar object in a Oracle DB (java.sql.Date)? thanks in advance Dom On 01.02.2005 12:36 Dom wrote: > > Any solution? How can I insert a java.util.date obtained from a calendar > object in a Oracle DB (java.sql.Date)? > Calendar stCal = Calendar.getInstance(); java.util.Date utlDate = stCal.getTime(); java.sql.Date sqlDate = new java.sql.Date(utlDate.getTime()); stmt.setDate(COLUMN_DATA, sqlDate); Thomas "Thomas Kellerer" <NNGNVRDSJEBN@spammotel.com> schrieb im Newsbeitrag news:3698dfF4v06i6U1@individual.net... > > > On 01.02.2005 12:36 Dom wrote: > > > > Any solution? How can I insert a java.util.date obtained from a calendar > > object in a Oracle DB (java.sql.Date)? > > > > Calendar stCal = Calendar.getInstance(); > java.util.Date utlDate = stCal.getTime()...

(Again) java.util.Date vs java.sql.Date
Hi there, (Again) Im trying to understand the EXACT difference between java.util.Date vs java.sql.Date. Googling, I can see that this is a very "popular" subject, but I still cannot figure out it exactly. Many writers claim that java.sql.Date only stores the DATE part (yyyy- mm-dd) but not the TIME part (hh:MM:ss) of a Date/Time value, but that I can easily disprove: java.util.Date ud = new java.util.Date(); java.sql.Date sd = new java.sql.Date(ud.getTime()); System.out.println(DateFormatUtils.format(ud, "yyyy-mm-dd hh:MM:ss.SSS")); System.out.println(DateFormatU...

Converting java.util.Date to java.sql.Date
Hello Friends In my program i am using the following codes: Date today = new Date(); log.debug("today is"+today); SimpleDateFormat fmt = new SimpleDateFormat("yyyy-mm-dd"); String todayStr = fmt.format(today); log.debug("todayStr value"+todayStr); java.sql.Date dt = java.sql.Date.valueOf(new String(todayStr)); log.debug("current Dt is:"+dt); The output iam getting is like this: today is Sat Sep 09 19:20:13 IST 2006(from java.util.Date) todayStr value 2006-20-09(from string value) current Dt is: 2007-08-09(from java.sql.Date) So h...

Convert java.util.Date to java.sql.Date
I convert java.util.Date to java.sql.Date this way:java.util.Date utilDate = new java.util.Date();java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());And then send sqlDate to prepareStatement.ps.setDate(1, sqlDate).But I got the date such as dd-mm-yyyy. And I need also take hours,minutes pm(am).How can I get the Date "dd-mm-yyyy hh:mm a"? Bumsys@gmail.com wrote: > I convert java.util.Date to java.sql.Date this way: > > java.util.Date utilDate = new java.util.Date(); > java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); > > And then send sqlD...

java.lang.Set with elements of type java.lang.Set
Roughly I do something along the lines of: Set set = new HashSet(); Set elem = new HashSet(); set.add(elem); // now we change the elem and add it again to the set elem.add(some object here); set.add(elem); I found out the hard way that 'set' may now contain 'elem' either once or twice, the reason being that 'elem.add()' changes the hashCode of elem such that it is not noticed that it is in 'set' already on the 2nd 'set.add()'. Question: What I would actually want is an IdentityHashSet() set = new IdentityHashSet() but this does not...

Dates, Java, and Access Databases
I have a form created via NetBeans. I have an Access Database. I am correctly connected and can write to the database. However, I have three Date fields that I need some help with. First, I need to have formatted input on the Form Fields. Then I need to save the date to the database in the correct date/time (it's set up that way) format. My problem other than the formatted input is that there are times that one or more of the date Fields will be without data. I can't seem to get a blank field to write to the database properly. I get a data mismatch error whenever I try to send the blank field to the DB. Any help or suggestions would be appreciated. "dave" <b1codemaster@gmail.com> wrote in message news:1144339297.044527.235960@u72g2000cwu.googlegroups.com... >I have a form created via NetBeans. I have an Access Database. I am > correctly connected and can write to the database. However, I have > three Date fields that I need some help with. > > First, I need to have formatted input on the Form Fields. Then I need > to save the date to the database in the correct date/time (it's set up > that way) format. My problem other than the formatted input is that > there are times that one or more of the date Fields will be without > data. I can't seem to get a blank field to write to the database > properly. > > I get a data mismatch error whenever I try to send the blank field to > the DB. > &...

java.sql.Date value truncated using JDBC in MS-SQL Server
Hi, I'm trying to write a java.sql.Date to a database and the minutes/ seconds etc. are not being written. I've seen and tested many examples found via searches and still have come up with nothing. Here's what I have: java.sql.Date formation - recommended constructor forms: java.sql.Date entryDttm = new java.sql.Date(System.currentTimeMillis()); OR java.sql.Date entryDttm = new java.sql.Date(new java.util.Date().getTime()); // prepared statement insert pstmt.setDate(1, entryDttm); // what is written to database 2007-02-07 12:00:00.000 // what needs to be w...

Java in Java
Is it possible to download a Java app (applet etc?) and run it inside a desktop Java app? -- Dirk http://www.transcendence.me.uk/ - Transcendence UK http://www.theconsensus.org/ - A UK political party http://www.onetribe.me.uk/wordpress/?cat=5 - Our podcasts on weird stuff Dirk Bruere at NeoPax wrote: > Is it possible to download a Java app (applet etc?) and run it inside a > desktop Java app? > Quite likely, but you won't necessarily get the same security model, unless you were careful about it. -- Daniel Pitts' Tech Blog: <http://virtualinfinity.net/wordpress/&g...

java.sql.SQLException: java.sql.Time
I get the following error message... java.sql.SQLException: Value '00:00:00 can not be represented as java.sql.Time >From the following SQL call... SELECT COUNT(pageurl) AS PageCount, pageurl, MAX(datetime) AS LastAccess, SUM(VisitLength) as TotalVisitLength, VisitLength, RecordNum, SUM(CASE WHEN VisitLength IS NULL THEN 1 ELSE 0 END) AS NullVisits FROM clubvisits WHERE groupid = #clubid# GROUP BY pageurl ORDER BY #OrderKey# I am using ColdFusion 7 on MySQL 4.1.12a. I'm not a Java programmer - I don't know any Java, but ColdFusion acts essentially as a script layer to J2EE, so for some reason it's throwing a Java error. I'm especially stuck, because I need to resolve this without programming any Java, and the Adobe forums for ColdFusion and MySQL groups are no help. The error refers to the "VisitLength" field which is a "time" data type in the database. In the MySQL documentation, it says that 00:00:00 is allowable for time (http://dev.mysql.com/doc/refman/4.1/ en/ time.html). So my question is, why is this error coming up if it's an allowable value? And is there anything I can do? >So my question is, why is this error coming up if it's an allowable >value? And is there anything I can do? one way is to store the values as raw ints or longs as far as SQL is concerned. -- Roedy Green Canadian Mind Products The Java Glossary http://mindprod.com <mehatespam1085@yahoo.com> wrote... ...

Passing java.lang.Class type as an argument to Java method
I am trying to set the default cell renderer for a JTable from within Matlab. All my efforts to this point have been based on Sun's 'How To Use Tables' tutorial. According to methodsview('JTable') the signature of the function is setDefaultRenderer(java.lang.Class,javax.swing.table.TableCellRenderer ) I cannot get this function to executed, and I think its because I cannot create a java.lang.Class argument from within Matlab. Following a corresponding code snipit: import javax.swing.*; import java.awt.*; import java.awt.event.*; columnNames = { 'First Name', &...

java.sql.SQLException: java.sql.Time
I get the following error message... java.sql.SQLException: Value '00:00:00 can not be represented as java.sql.Time >From the following SQL call... SELECT COUNT(pageurl) AS PageCount, pageurl, MAX(datetime) AS LastAccess, SUM(VisitLength) as TotalVisitLength, VisitLength, RecordNum, SUM(CASE WHEN VisitLength IS NULL THEN 1 ELSE 0 END) AS NullVisits FROM clubvisits WHERE groupid = #clubid# GROUP BY pageurl ORDER BY #OrderKey# I am using ColdFusion 7 (not my choice - don't laugh) on MySQL 4.1.12a. The error refers to the "VisitLength" field which is a "time" da...

java.lang.ExceptionInInitializerError: java.lang.ArrayIndexOutOfBoundsException
Hi, I am new to DB2. I am getting this error while loading the DB2Driver. I don't have any idea about where i might have gone wrong. please help me. Below is the stack trace. Stack Trace: java.lang.ExceptionInInitializerError: java.lang.ArrayIndexOutOfBoundsException at COM.ibm.db2.jdbc.app.DB2Driver.SQLAllocEnv(Native Method) at COM.ibm.db2.jdbc.app.DB2Driver.<init>(DB2Driver.java:245) at COM.ibm.db2.jdbc.app.DB2Driver.<clinit>(DB2Driver.java:130) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:120) Thanks in advance, Ganesh Majji ganesh.m@gmail.com wrote: > I am new to DB2. I am getting this error while loading the DB2Driver. I > don't have any idea about where i might have gone wrong. please help > me. Unfortunately my crystal bowl stays dark because you didn't mentioned your operating system and which DB2 version you're using. For Windows, *nix and OS/2 please ensure that your application is using the db2java.zip from SQLLIB\java. Check if there are multiple db2jdbc.dll on your system, remove any extra db2jdbc.dll files on the system. Ensure that you can connect to DB2 with the DB2 workstation tools from your machine. sorry for not mentioning the system details. Operation System is SunOS (Solaris) and DB2 SDK 8.2.2 Thank you very much for the kind response, Ganesh Bernd Hohmann wrote: > ganesh.m@gmail...

Java Java
Have my first Open Source Linux Java Project. Working on a second right now. Coming out with a distro called OPEN*WINDOWS. It will be at www.black-and-company.com tab wrote: > Have my first Open Source Linux Java Project. > Working on a second right now. > > Coming out with a distro called OPEN*WINDOWS. > > It will be at www.black-and-company.com > Wasn't that the whole point of Lindows? Oh, right, we didn't care for that either. tab wrote: > Have my first Open Source Linux Java Project. > Working on a second right now. > > Coming out with a ...

Java with MS Access 2000 Database
Hi All I am trying to connect with MS Access database but faild. Please let me know what are the alternatives, so that I can connect to an Access Database. Thanks in Advance Zee What are you failing with? Make sure you have your data source/DB setup via control panel. The following pice of code shows how I connect to a ms access database called myDB. ------------------ // Load the driver class Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Define the data source for the driver String sourceURL = "jdbc:odbc:myDB"; // Create a connection thr...

java.lang.StringIndexOutOfBoundsException: String index out of range: 23 at java.lang.String.charAt(String.java:460)
Hi All I am having the torque3.1.jar and postgresql-7.4. I have compiled the new jdbc driver called as postgresql.jar and have placed it in the lib directory from where the ant scripts catch the jars. Whenever i try to access through torque gestList = BaseGestlistPeer.doSelect(new Criteria()); this error arises java.lang.StringIndexOutOfBoundsException: String index out of range: 23 at java.lang.String.charAt(String.java:460) at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653) at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java...

warning: [deprecation] in java.util.Date(java.lang.String) has been deprecated
Dear Java Programmers, When compiling my Java program I get the following warning: "warning: [deprecation] in java.util.Date(java.lang.String) has been deprecated" on for example this statement: new Date("09/23/2003") The code is used to generate the dates on a webpage. Depending on the language of the webpage the generated format is different. For example English (see "Date" and "Choose publication" on the page opened): http://home.scarlet.be/kenya-belgium/list_5_en/a_kenyan_birthday_party_and_a_belgian_birthday_party.html French: http://home.scarlet....

value comparison of java.sql.Date type
Hi, how to compare two values of java.sql.Date type? That is, how to know which one is eariler or later? Thx! "Diphay Z" wrote... > Hi, how to compare two values of java.sql.Date type? > That is, how to know which one is eariler or later? Thx! http://java.sun.com/j2se/1.4.2/docs/api/java/util/Date.html#compareTo(java.util.Date) // Bjorn A ...

Execute Update from Java to Access database problem...
public boolean addItem(String barcode, String style, String colour, String size, String qty, String store){ boolean complete = false; String addItem = "INSERT INTO items (barcode, style, colour, size, qty, store) VALUES ('"+barcode+"','"+style+"','"+colour+"','"+size+"','"+qty+"','"+store+"')"; Statement stmnt = null; System.out.println(addItem); try { stmnt = conn01.createStatement(); stmnt.executeUpdate(addItem); //stmnt.execute(addItem); complete = true...

Date format in Ms-Access/SQL Database
In ODBC, I notice some difference between in Date format for Access and some of the SQL database. for example. to SELECT/UPDATE date field in SQL databases. MySQL. SELECT * FROM MyTable WHERE MyDate='YYYY-MM-DD' UPDATE SET MyDate='YYYY-MM-DD' INTO MyTable WHERE MyCode='Code' MS-SQL Server. SELECT * FROM MyTable WHERE MyDate='YYYY-MM-DD' UPDATE SET MyDate='YYYY-MM-DD' INTO MyTable WHERE MyCode='Code' Ms-Access. SELECT * FROM MyTable WHERE MyDate=#YYYY-MM-DD# UPDATE SET MyDate='YYYY-MM-DD' INTO MyTable WHERE MyCode...

value comparison of java.sql.Date type
Greeting, How to compare two values of java.sql.Date type? That is, how to know which date is earlier or later? Thanks! "Diphay Z" <picassozd@macaustreet.com> wrote in message news:1102771397.250117.151770@c13g2000cwb.googlegroups.com... > Greeting, > > How to compare two values of java.sql.Date type? That is, how to know > which date is earlier or later? Thanks! boolean isAfter = mySqlDateOrAnySubclassOfDateForThatMatter.after(anotherDate); also before(myDate) -- Mike W ...

Date format problem with Java and MS-Access
Hi, I'm using access as database for my Java applications.I have specified the Date format in Access as d/m/yyyy. But when I retrieve the data from the database, I get for some dates as m/d/yyyy format(for dates 1-10 specially). I have even formatted my dates using SimpleDateFormat in my jsp files as d/m/yyyy, but I'm still not able to get the dates in the correct format. Please help. ruds wrote: > Hi, > I'm using access as database for my Java applications.I have specified > the Date format in Access as d/m/yyyy. > But when I retrieve the data from the database, I ge...

Loading a large dataset from a database-- java.lang.OutOfMemoryError: Java heap space
Dear all, While trying to load a big table, I received the following error. Is there any way to circumvent this problem without doing additional programming? Thanks you help, Kirill Andreev ??? Java exception occurred: java.lang.OutOfMemoryError: Java heap space at java.util.Arrays.copyOf(Unknown Source) at java.util.Arrays.copyOf(Unknown Source) at java.util.Vector.ensureCapacityHelper(Unknown Source) at java.util.Vector.addElement(Unknown Source) at com.mathworks.toolbox.database.fetchTheData.dataFetch(fetchTheData.java: 740) Error in ==> cursor....

XML-RPC
Hello, I am getting an error "XML-RPC - java.lang.ClassCastException: java.util.Date" when I call the "XmlRpcClient" execute method. I believe the underlying classes are having trouble parsing the result from the time.xmlrpc.com/RPC2:80 XML-RPC service. I apologize that I could not navigate the Apache bug reporting and search system. I hope someone can recognize what is up with this. Thank you in advance for any help, Michael import java.util.Vector; import java.util.Hashtable; import org.apache.xmlrpc.*; public class GetTime { private final static String serv...

Java Applets connecting to Databases: access denied (java.net.SocketPermission ... resolve)
Hi folks,I'm new about developing Java applets and I need some help.I'm developing a Web Application based on JSPs and Tomcat in Netbeans.I've also developed a Java Applet which its package has some classes.This Applet connects to a Sybase Database (JConnect) in order to querysome information. It works fine when I run the Applet Class atNetbeans.The Jar containing this Applet (and the Jar supporting the DB driver)is embedded into a JSP of my Web Application through JSP Plugin tag.When I call for this JSP (where the Applet is embedded) I get thiserror (at the Client Java Console):ja...

Web resources about - how do you update a java.sql.date type in an ms access database - comp.lang.java.help

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Oracle: Fear Not, ’12c’ Database Nearing Its Breakout, Says Credit Suisse
... from cloud computing applications in 2016, along with the adoption by customers of the its “ 12c ” version of its flagship relational database ...

Open source database improves protection and performance
Most enterprises rely on databases in some form or another, but they can be vulnerable to attack from people looking to steal information. They ...

NTTC's Liquid Products Database making steady progress
NTTC's Liquid Products Database making steady progress Modern Bulk Transporter NTTC is asking carriers to suggest “proprietary blends” for ...

TransGrid Switches Oracle Database Support to Rimini Street
TransGrid Switches Oracle Database Support to Rimini Street

Herbalife sinks on database error report
Herbalife said it had overstated growth in the number of "active new members" in some instances in the past three quarters due to database errors. ...

Microsoft's SQL database software now runs on Linux
Remember when Steve Ballmer likened Linux to cancer, and the notion of Microsoft courting the open source crowd was virtually unimaginable? The ...

DB Networks Launches Real-Time Database Security Sensor
By integrating this sensor into their products, security OEMs provide their customers with more usage detail and metrics from data-tier cyber ...

How Postgres and Open Source Are Disrupting The Market for Database Management Systems
Open source database management systems (OSDBMSs) have matured into viable alternatives to proprietary, commercial solutions. It comes as no ...

Resources last updated: 3/9/2016 8:03:13 PM