aspose file tools*
The moose likes JDBC and the fly likes Cannot update an access 2007 database table (Date field) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Cannot update an access 2007 database table (Date field)" Watch "Cannot update an access 2007 database table (Date field)" New topic
Author

Cannot update an access 2007 database table (Date field)

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
The field of the Access 2007 table is defined as Short date
No inputmask and having null default value

The Formatted Text Field is defined as


One thing weird is that although the Database table contains values of the DOB such as 1990-10-10, the retrieved value is 1990-10-10 00:00:00.00

The retrieval code


When I update the table

I get java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'd[1993-12-12 00:00:00.0]}'.
Ranganathan Kaliyur Mannar
Bartender

Joined: Oct 16, 2003
Posts: 1085
    
  10

You have declared a variable of PreparedStatement, but you are using it like a Statement instance.
I would suggest reading Using Prepared Statements and then using the setDate() method to set the date value.


Ranga.
SCJP 1.4, OCMJEA/SCEA 5.0.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
Ranganathan Kaliyur Mannar wrote:You have declared a variable of PreparedStatement, but you are using it like a Statement instance.
I would suggest reading Using Prepared Statements and then using the setDate() method to set the date value.


Thanks Ranganathan Kaliyur Mannar,
I would take a look first.
Really grateful
Jack
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
Hello,
Your way does make life easier  thanks
But how would I cast/convert an Object data type to Calendar so that I can put it into my preparedstatement?
Thanks
Jack
Maneesh Godbole
Saloon Keeper

Joined: Jul 26, 2007
Posts: 10451
    
    8

Please do take care to CarefullyChooseOneForum
Your question is not UI related. I will move it to a more appropriate forum for you.


[How to ask questions] [Donate a pint, save a life!] [Onff-turn it on!]
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
Maneesh Godbole wrote:Please do take care to CarefullyChooseOneForum
Your question is not UI related. I will move it to a more appropriate forum for you.


Ok, sorry about it, just leaning towards another type of question gradually.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
Jack Luk wrote:
Maneesh Godbole wrote:Please do take care to CarefullyChooseOneForum
Your question is not UI related. I will move it to a more appropriate forum for you.


Ok, sorry about it, just leaning towards another type of question gradually.


Many many thanks folks, problem solved, but another problem is created.
By looking at post no 1, you will see that I am using rs2xml.jar to bind the database table to the form table.
This causes some errors as Access Date/Time field will be transformed to a Java Data type which has the values like 1990-10-10 00:00:00.00
And I just want 1990-10-10. How do I resolve that?
Thanks very much
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Just ignore the time part of the Java Calendar?

You can use the SimpleDateFormat class to format the date in various ways, eg. yyyy-MM-dd.

If you do calculations on these fields and want to ignore the time portion, you'll might need to "manually" set the time portion of the Date class to zero. The Calendar class might be better to perform date-time arithmetic (the Joda Time library would be much, much better than any JDK class as of JDK 7).
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
Martin Vajsar wrote:Just ignore the time part of the Java Calendar?

You can use the SimpleDateFormat class to format the date in various ways, eg. yyyy-MM-dd.

If you do calculations on these fields and want to ignore the time portion, you'll might need to "manually" set the time portion of the Date class to zero. The Calendar class might be better to perform date-time arithmetic (the Joda Time library would be much, much better than any JDK class as of JDK 7).


After examining your options for one and a half hour, I still couldn't get it to work. Could you provide an example on how to clip off the time portion?
Sorry for being dumb.
Thanks
Jack
John Jai
Bartender

Joined: May 31, 2011
Posts: 1776
Are you using a java.util.Calendar? Can you show the code snippet that tries to set the date without setting time?
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
John Jai wrote:Are you using a java.util.Calendar? Can you show the code snippet that tries to set the date without setting time?


Hi John,
No, I am not. I am binding the database field directly to the table
But I would say this

Thanks everybody
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Jack Luk wrote:After examining your options for one and a half hour, I still couldn't get it to work. Could you provide an example on how to clip off the time portion?
Sorry for being dumb.

It was me being dumb. I missed the part where you speak about the rs2xml library. I'm sorry.

What does the DbUtils.resultSetToTableModel method look like? Is it a method in that library, or your own?

Do you have problems while displaying the date values in the table, or while editing them? To alter the display of the value, you need to set the table's renderer for that column (have you seen JTable tutorial?). Editing is slightly more involved, so I'm not going to discuss it until you get the rendering right.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
Martin Vajsar wrote:
Jack Luk wrote:After examining your options for one and a half hour, I still couldn't get it to work. Could you provide an example on how to clip off the time portion?
Sorry for being dumb.

It was me being dumb. I missed the part where you speak about the rs2xml library. I'm sorry.

What does the DbUtils.resultSetToTableModel method look like? Is it a method in that library, or your own?

Do you have problems while displaying the date values in the table, or while editing them? To alter the display of the value, you need to set the table's renderer for that column (have you seen JTable tutorial?). Editing is slightly more involved, so I'm not going to discuss it until you get the rendering right.


I have difficulties in displaying the Date values. Here is the library
http://technojeeves.com/joomla/index.php/free/59-resultset-to-tablemodel
The retrieval part was okay, But It was all turned into something like 1990-10-10 00:00:00.00, it firstly looks really ugly to display as DOB values
I can edit them thru the JTable and JTextField or JForrmattedTextField, but it became a nightmare when I tried to update it to the Access database, the ODBC engine refused to update it because it was in the wrong format. It didn't like the time portion part.
Thanks
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Can you show us the code that writes changes made by the JTable back to the database?
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
Martin Vajsar wrote:Can you show us the code that writes changes made by the JTable back to the database?


Here is the code in the JFrame constructor


Here is the updating code

Let me know if this is enough for your assistance.
Thanks
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I didn't go thoroughly through your code, so I might have overlooked something. There is undoubtedly a problem related to the DOB field, though: in your update SQL statement, the parameter assigned to the DOB field is the eight one. However, you are setting the date field at position 7 (remember, JDBC indexes start at one, not zero, which is a terrible blunder in JDBC).

Have a closer look at the update statement: there is update Patient set Patient_ID = ? ... where Patient_ID = ?. Unless you want to change the patient ID (and I hope you don't want to), you can (or perhaps should) omit the Patient_ID=?.

savedValues[0] is contains a String (String.valueOf(jTextPatientID.getText()).trim()), but you then cast it to an int. Similar with District ID. I'd expect you get an exception at these casts.

I'd also suggest creating a class that would represent the patient in your application, instead of using an array of objects. I personally would even use this class to load patient from database and create a table model (based on AbstractTableModel, not DefaultTableModel) that would keep its data in a list of patient instances. It would save you from casting and index access, which is error prone. It's a big change, though, so you might perhaps begin with introducing this class just to the code that saves the data to the DB.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
hi martin,


I think the problem now lies in that rs2xml.jar reads in any dates and turns it to 1990-10-10 00:00:00.00 etc. The updates are successful now. But now I've only got the display problem now
Thanks
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

The problem is with the format of the dates, not with the values, right? (Just to make sure I got the problem right).

Assuming it is the wrong format problem -- did you have a look at the JTable renderers tutorial I've posted some time earlier? Setting up a renderer for the DOB column should be relatively easy and straightforward.

And, by the way, I still don't understand how your code can work without throwing a ClassCastException when casting a String to an int.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Cannot update an access 2007 database table (Date field)