This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes Getting error while inserting data into table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting error while inserting data into table" Watch "Getting error while inserting data into table" New topic
Author

Getting error while inserting data into table

Preetish Madalia
Ranch Hand

Joined: Dec 12, 2007
Posts: 37
Hi All,

public static final String INSERT_QUERY =
"INSERT INTO USER_SUMMARY(USER_ID,LOGIN_TIME) VALUES (?,?)";
/**
* @param args
*/
public static void main(String[] args) {

try {
Driver d = (Driver)Class.forName
("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
Connection conn = DriverManager.getConnection(
"jdbcdbcriver={Microsoft Access Driver (*.mdb)};DBQ=E:/preetish/userapplication.mdb"
);

System.out.println("Connection Obtained : " + conn );

PreparedStatement ps =
conn.prepareStatement(INSERT_QUERY);

ps.setInt(1,1);

Date date = new Date();

Calendar cal = Calendar.getInstance();
cal.setTime(date);

ps.setString(2,cal.getTime().toString());


boolean rs = ps.execute();
System.out.println(rs);
/*while(rs.next()){
System.out.print("User_id : " + rs.getString("user_id"));
}
*/


} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch(SQLException sqe){
sqe.printStackTrace();
}


Here, is a simple java programme were i m trying to insert data in the table user_summary. But, the error i'm gettign is :


java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at com.connection.JdbcConnection.main(JdbcConnection.java:43)

I don't hav any idea as to wat this error is.
Pls. help me out in this.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
could you display the date you're trying to insert and a date from your table (if you have one)?
Preetish Madalia
Ranch Hand

Joined: Dec 12, 2007
Posts: 37
no Paul. I tried it, but its directly showing me the above error.
Mahendra Batra
Greenhorn

Joined: Nov 30, 2007
Posts: 1
Hi Preetish,

The error you shown clearly mentions that type of columns and the data you are trying to send are not in sync.
The possible situations could be, either your first column (USER_ID) is not a number or your second column(LOGIN_TIME) is not a String.
I sense that your second column must be of type Date/Time, for which a String i.e cal.getTime().toString() won't work.
Also, when you insert a Date using PreparedStatement, it cant't be java.util.Date but it has to be java.sql.Date

Hope it helps.
Regards,
Mahendra
Preetish Madalia
Ranch Hand

Joined: Dec 12, 2007
Posts: 37
Hi Mahendra,
I tried using even a for USER_ID only. But, its still not working.
in the table. user_id is a number for which i have given ps.setInt().

Should i use ps.setLong() or any other thnig??
Also, thanks for telling me abt. the Date.

Lots a lot.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19674
    
  18

Be aware with the use of java.sql.Date.

When you use Date, some databases may ignore the time part. If you want both the date and time stored, use java.sql.Timestamp instead.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Preetish Madalia
Ranch Hand

Joined: Dec 12, 2007
Posts: 37
I am able to only access data from the databade..
there is problem in inserting data in the database.
I am using the local database. i.e. a .mdb file.
Pls. tell me if any specific settings are required to be made.
Bill Johnston
Ranch Hand

Joined: Nov 17, 2005
Posts: 201
What is LOGIN_TIME ? You are sending it a String, but is it a String, or a date, or what?


~Bill
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Bill Johnston:
What is LOGIN_TIME ? You are sending it a String, but is it a String, or a date, or what?


or said another way... if you don't present your date string in the correct format or tell the data base that it is a date and the format of your date string you want to insert into the database date column... the database will not allow you to insert the row into the database because it appears (to the database) you are attempting to insert invalid data.
[ December 13, 2007: Message edited by: Paul Campbell ]
Preetish Madalia
Ranch Hand

Joined: Dec 12, 2007
Posts: 37
Got It!!!
Its working fine now.
Thanks Guys for replying.
There was a problem in date fromat.

I tried using java.sql.Date instead of java.util.Date
and its working fine now.

Thanks once again to all.

Regards,
Preetish.
Bill Johnston
Ranch Hand

Joined: Nov 17, 2005
Posts: 201
You're welcome. If the DB column is a date - which you said it is - you might find it better to let the database form the date from the String. IOW ... in Oracle for example, I might have a date String in my Java program in this format '12152007', and I can insert it into the database date column like this: TO_DATE('12312006','MMDDYYYY'), and it will do the right thing. Something to check out if you do alot of date work.
 
Don't get me started about those stupid light bulbs.
 
subject: Getting error while inserting data into table