NumberFormatException

[rich ike]

Hi everyone,
I need some help.
I have a program that creates a window with JTexboxes on it and a button. The user is to fill in the textboxes and press the button. When this occurs the program is to retreive the information from the textboxes and then insert it into a database. I used getText() to get the information from the textboxes. That works fine. But when i press the button to send it to the database it gives me an error "NumberFormatException" and then the contents of the textbox in question. Everything seems to be fine. I get the information as a String pass it to the method as a string. Then when i send it to the database I use parseInt() to change it to an Integer because the format of the database column is int(11). I don't know why it is giving me this error. Another funny thing that is happening is that it gives me the error only when i fill in the next following textbox which is also an Integer. Here is the code:

public void UpdateData(String sex,String account,String id,String fname,
String lname,String birth,String age,String street,
String city,String province,String pc,
String medical,String hphone,String cphone,
String email,String momfname,String momlname,
String dadfname,String dadlname,String level,
String hours,String timefrom,String timeuntil,
String fee) {
String s=sex;
String acc = account;
String ident = id;
String first = fname;
String last = lname;
String birthday = birth;
String ageyear = age;
String streetaddr = street;
String cityname = city;
String prov = province;
String post = pc;
String med = medical;
String phone1 = hphone;
String phone2 = cphone;
String emailaddr = email;
String mfirst = momfname;
String mlast = momlname;
String dfirst = dadfname;
String dlast = dadlname;
String levelname = level;
String thour = hours;
String tfrom = timefrom;
String tuntil = timeuntil;
String feecost = fee;

if ((s.equals("m"))||(s.equals("M"))){
String data = "jdbc:mysql://localhost:3306/athletes";
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
data, "root", "naofumi");
Statement st = conn.createStatement();
st.executeUpdate("INSERT INTO Male" +
"VALUES (",'"+Integer.parseInt((String)acc)+"'"+ ",'"+ident+"'"+ ",'"+s+"'"+ ",'"+last+"'"+ ",'"+first+"'"+ ",'"+birthday+"'"+ ",'"+Integer.parseInt((String)age)+"'"+ ",'"+streetaddr+"'"+ ",'"+cityname+"'"+ ",'"+prov+"'"+ ",'"+post+"'"+
This one triggers it ->",'"+Integer.parseInt((String)med)+"'"+
This one gets the error -> ",'"+Integer.parseInt((String)phone1)+"'"+ ",'"+Integer.parseInt((String)phone2)+"'"+ ",'"+emailaddr+"'"+ ",'"+mlast+"'"+ ",'"+mfirst+"'"+ ",'"+dlast+"'"+ ",'"+dfirst+"'"+ ",'"+levelname+"'"+ ",'"+Integer.parseInt((String)thour)+"'"+ ",'"+tfrom+"'"+ ",'"+tuntil+"'"+ ",'"+Float.parseFloat((String)feecost)+"');");
st.close();
} catch (SQLException sq) {
System.out.println("SQL Error: " + sq.toString() + " "
+ sq.getErrorCode() + " " + sq.getSQLState());
} catch (Exception e) {
System.out.println(" Error: " + e.toString()
+ e.getMessage());
}
}

This is the crucial part of the program so if anyone can help please do.

[Stefan Wagner]

I allways create the sql-statement as separate String, so I can easily debug it, paste it to an editor and try to execute it with other methods than my application:

 
String query = "UPDATE ...";
System.out.println (query);
st.executeUpdate (query); 

Most syntax-errors are very easy to find in the printed statement, without all that masking javacode.

If 'age' is an int, you should insert it by

INSERT INTO male (age) VALUES 17; 
-- not
INSERT INTO male (age) VALUES '17'; 

Some noise in your code is the copying of parameters to local values, and why do you cast a String to a String?

 (String)acc, (String)med

A much more secure way in two meanings is, to use prepared statements (see the javadocs for details).
1.) You don't need to mask Strings with + ",'" and + "', " - more secure for the developer.
2.) It's much more secure at runtime. Imagine names containing ' like O'Reilly. Bad guys (and girls) use this trap for sql-injection-attacks.

[Stuart Ash]

Too many concatenations, the SQL string is too complex, might help rethinking how to code this.

[Jesper de Jong]

Instead of building the SQL statement yourself by concatenating SQL fragments and parameters, use java.sql.PreparedStatement. You write an SQL statement with question marks in the place of the parameters and use the set...() methods of PreparedStatement to set the values. The JDBC driver will take care of adding quotes or other conversions necessary for the database.

Lookup the API documentation of java.sql.PreparedStatement.

PreparedStatement ps = connection.prepareStatement("INSERT INTO Male VALUES (?,?,?,..."); // add as many question marks as you need
 
ps.setString(1, acc);
ps.setString(2, ident);
// ... etc
 
ps.executeUpdate();

[ October 26, 2005: Message edited by: Jesper de Jong ]

[Jeff Albertson]

I agree with the previous two posts: use PreparedStatement.
It also has another benefit: if you are inserting multiple
rows, it's more efficient, because it's precompiled.
Even if you only insert one row at a time, prepared statements
are often pooled, in the same way connections are pooled,
so you still get the performance benefits.