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

by

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:

More Related Contents:

  1. SQL syntax error in "insert into 'keys'(a,b,c,d,e) values(?,?,?,?,?)" [closed]
  2. Hours and seconds are lost when storing a date in the database [closed]
  3. Multiple queries executed in java in single statement
  4. Java: Insert multiple rows into MySQL with PreparedStatement
  5. ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long on connect to MySQL
  6. MySQL and JDBC with rewriteBatchedStatements=true
  7. Closing JDBC Connections in Pool
  8. No suitable driver found for ‘jdbc:mysql://localhost:3306/mysql [duplicate]
  9. Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client
  10. “Loading class com.mysql.jdbc.Driver … is deprecated” message
  11. Returning a ResultSet
  12. How to change MySQL timezone in a database connection using Java?
  13. How to configure Tomcat to connect with MySQL
  14. Java JDBC MySQL exception: “Operation not allowed after ResultSet closed”
  15. java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname [duplicate]
  16. Retrieve an Image stored as BLOB on a MYSQL DB
  17. Can I execute multiple queries separated by semicolon with MySQL Connector/J? [duplicate]
  18. mysql prepared statement error: MySQLSyntaxErrorException
  19. how to get list of Databases “Schema” names of MySql using java JDBC
  20. SQL Error: 0, SQLState: 08S01 Communications link failure [duplicate]
  21. Access denied for user ‘root’@’localhost’
  22. How to correctly write UTF-8 strings into MySQL through JDBC interface
  23. Glassfish Admin Console throws java.lang.IllegalStateException when creating JDBC Pool
  24. java.sql.SQLException: Before start of result set [duplicate]
  25. How to use prepared statement for select query in Java?
  26. Stumped SQL Exception for JDBC
  27. MySQL & Java – Get id of the last inserted value (JDBC) [duplicate]
  28. Performance of MySQL Insert statements in Java: Batch mode prepared statements vs single insert with multiple values
  29. java.sql.SQLException: No database selected – why?
  30. JDBC MySQL connection using Unix Socket

Leave a Comment