CODEWITHSUNDEEP
javamysql
ashima
ashima

Reputation: 11

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '' for column 'Date_Of_Birth' at row 1

I'm trying to update the personal detail of a user through a java panel.Panel has fields like user_id(autogenerated),name,date of birth. problem is when i enter nothing to the date of birth field in java panel and then save it. It gives me the above mentioned error. i tried to verify it by inserting null to the date of birth(Date datatype) field directly using the mysql database.There it gives no error. Why is it not taking null string when i insert through java panel but is taking when insert directly using mysql.

CREATE TABLE `userpersonaldetail` (
  `User_Id` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(45) default NULL,
  `Date_Of_Birth` date default NULL,
  `Address` varchar(300) default NULL,
  `Phone` varchar(20) default NULL,
  `Mobile` varchar(20) default NULL,
  `Email` varchar(50) default NULL,
  PRIMARY KEY  (`User_Id`),
  CONSTRAINT `FK_userpersonaldetail_1` FOREIGN KEY (`User_Id`) REFERENCES `usermaster` (`User_Id`)
)

And the portion of the code where exception occurs is:

  try
     {
        con=(Connection) DBConnection.getConnection();
        pstmt=(PreparedStatement) con.prepareStatement("Update userpersonaldetail set "+
               "name=?,date_of_birth=?,address=?,phone=?,mobile=?,email=? where user_id=?");

            pstmt.setInt(7,perBean.getUserId());
            pstmt.setString(1,perBean.getName());
            pstmt.setString(2,perBean.getDateOfBirth());
            pstmt.setString(3,perBean.getAddress());
            pstmt.setString(4,perBean.getPhone());
            pstmt.setString(5,perBean.getMobile());
            pstmt.setString(6,perBean.getEmail());

            int i=pstmt.executeUpdate();
 }

here perBean is the javaBean which retrieves values from the gui.In one of the test case i kept the date_of_birth text box null which is giving error while storing in DB.

Upvotes: 1

Views: 12615

Answers (4)

DIM
DIM

Reputation: 73

I replaced older version of my-sql-connector jar (to be found in lib folder of the server) with the latest. That solved my problem.

Upvotes: 1

eis
eis

Reputation: 53462

If an attribute does not have a value but you have it mentioned in the column list, forcing you to give something there, you need to use

statement.setNull(index, datatype)

to it. Setting to "" is not the same thing as setting to null.

Upvotes: 0

Brian
Brian

Reputation: 8616

Ahh, i see now, you can't insert '' as a date. you will need to pass a date.

Upvotes: 0

Brian
Brian

Reputation: 8616

My initial guess would be the field has been defined as 'NOT NULL' which means it will force you to enter a value...

If you were to do a mysql dump of the table (or view it in some tool) you'll probably find it defined such as:

`someDT` datetime NOT NULL

Upvotes: 1

Related Questions