java sql SQLException Parameter index out of range 1 number of parameters which is 0

Question

After the selection in the select combo box has been validated, I am unable to enter it into my database. Tomcat displays the subsequent 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?

Answer 1

When using any of the setXxx() methods on PreparedStatement without any placeholders in the SQL query string, you may encounter this issue. due to 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);

To specify the placeholders, you must make the appropriate corrections to the SQL query string.

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);

You do not need to cite those placeholders in the following manner because the parameter index begins with 1:

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.

I hope this helps you.