java.sql.SQLException Parameter index out of range (1 > number of parameters, which is 0) [closed]

Question

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?

Answer 1

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.

See also:

• JDBC tutorial - prepared statements