Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting error while inserting data into table

 
Preetish Madalia
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
could you display the date you're trying to insert and a date from your table (if you have one)?
 
Preetish Madalia
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
no Paul. I tried it, but its directly showing me the above error.
 
Mahendra Batra
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20669
65
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Preetish Madalia
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 201
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is LOGIN_TIME ? You are sending it a String, but is it a String, or a date, or what?
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 201
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic