Java SQL exception non-numeric value appeared where numeric expected

[Azeem Rathore]

Hi my first language is German so bear with my English
i am new to programming and i have form which display Employee tables values in the text field from database. it has a colum date of birth of type Date.
When load program form display all values successfully in the textfield now i have made function update using update query . all other values updated successfully but date objects arent updated when i try to update date objects from the text field and hit save button it give" exception java sql Exception non numeric value appeared where numeric expected" message i do not know how to handle it
i get the date value from textfield from employee form and set this value to employees class

here is the code from employee form

employee d=new employee(); // instance of employee class 
SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy"); 
  Date convertedDate = dateFormat.parse(jTextField9.getText());// retrieve value form employee form class and convert it to date because textfield does not save date value it is not compatible 
  d.setDateOfBirth(convertedDate);

then i have made the update_employee function code is this

query = "UPDATE hr_employees SET"
       + " EMP_CODE="+ "'" +getEmpCode() + "',OLD_EMP_CODE= '" + getOldEmpCode() + "',"
        + " CARD_NO="+ "'" +getCardNo() + "',EMP_NAME= '" + getEmpName() + "',"
        + " FATHER_NAME="+ "'" +getFatherName() + "',GENDER= '" + getGender() + "',"
       + " EMAIL="+ "'" +getEmail() + "',EXTENSION= '" + getExtension() + "',"
      [color=red] + "[b] DATE_OF_BIRTH="+ "'" +getDateOfBirth()[/b][/color] + "',MARITAL_STATUS= '" +  getMaritalStatus() + "',"
  + " HUSBAND_NAME="+ "'" +getHusbandName() + "',NEW_NIC_NO= '" +  getNewNicNo() + "',"
  + " FAMILY_CODE="+ "'" +getFamilyCode() + "',OLD_NIC_NO= '" +  getOldNicNo() + "',"
  + " hight="+ "'" +getHight() + "',BLOOD_GROUP= '" + getBloodGroup() + "',"
           + " ID_MARK="+ "'" +getIdMark() + "',PRESENT_ADDRESS= '" + getPresentAddress() + "',"
           + " PHONE_RES="+ "'" +getPhoneRes() + "',MOBILE= '" + getMobileNumber() + "',"
           + " OTHER_PHONES="+ "'" +getOtherPhones() + "',PERMANENT_ADDRESS= '" + getPermanentAddress() + "'"          
           
        +" WHERE EMP_CODE= '" + getEmpCode() + "'"; 

how to update date of birth? kindly help please

[Paul Clapham]

I would suggest you rewrite your code to use a PreparedStatement. Using a PreparedStatement means that you don't have to deal with formatting dates in whatever way the database's version of SQL wants to see them. It also takes care of other things which could cause problems, like quote characters in your text fields (the "O'Brien Problem") and guards against SQL injection attacks (where users can put in carefully chosen text which changes your ordinary SQL command to do bad things to your database).

You'll find a tutorial about PreparedStatement here: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

[Lukas Eder]

While I agree with Paul, of course...

Paul Clapham wrote:I would suggest you rewrite your code to use a PreparedStatement. [...]

... your current problem can be solved by writing

"DATE_OF_BIRTH = DATE '" + new java.sql.Date(getDateOfBirth().getTime()).toString() + "'"

The above date literal syntax is specified by the SQL standard. It accepts the following format: DATE 'YYYY-MM-DD', which is what happens to be produced by java.sql.Date.toString().

In case you're using the Derby database, use this instead:

"DATE_OF_BIRTH = date('" + new java.sql.Date(getDateOfBirth().getTime()).toString() + "')"

In case you're using a T-SQL database (SQL Server, Sybase ASE, Sybase SQL Anywhere) or SQLite, just use the plain date string as such:

"DATE_OF_BIRTH = '" + new java.sql.Date(getDateOfBirth().getTime()).toString() + "'"

But again, it would really be better to follow Paul's advice and use a PreparedStatement with bind values instead.