After validation of select combo box which I have selected and I am not able to insert it in my database. Tomcat gives following error:
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
How is this caused and how can I solve it?
You will get this error when you call any of the setXxx() methods on PreparedStatement, while the SQL query string does not have any placeholders ? for this.
For example this is wrong:
String sql = "INSERT INTO tablename (col1, col2, col3) VALUES (val1, val2, val3)";
// ...
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, val1); // Fail.
preparedStatement.setString(2, val2);
preparedStatement.setString(3, val3);
You need to fix the SQL query string accordingly to specify the placeholders.
String sql = "INSERT INTO tablename (col1, col2, col3) VALUES (?, ?, ?)";
// ...
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, val1);
preparedStatement.setString(2, val2);
preparedStatement.setString(3, val3);
Note the parameter index starts with 1 and that you do not need to quote those placeholders like so:
String sql = "INSERT INTO tablename (col1, col2, col3) VALUES ('?', '?', '?')";
Otherwise you will still get the same exception, because the SQL parser will then interpret them as the actual string values and thus can't find the placeholders anymore.
This is an issue with the jdbc Driver version. I had this issue when I was using mysql-connector-java-commercial-5.0.3-bin.jar but when I changed to a later driver version mysql-connector-java-5.1.22.jar, the issue was fixed.
