Java.Sql.Sqlexception Parameter Index Out of Range (1 ≫ Number of Parameters, Which Is 0)

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

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

Parameter index out of range (1 > number of parameters, which is 0) in update

You are using wrong syntax in your update clause. The code should be like this.

    String sql = "SELECT NUM FROM diary WHERE NUM=" + idx;
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.executeQuery();

    pstmt.close();
    sql = "UPDATE diary SET TITLE=? ,CONTENTS=? WHERE NUM=?"; 
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, title);
    pstmt.setString(2, contents);
    pstmt.setString(3, idx);
    pstmt.executeUpdate();

Error: SQLException: Parameter index out of range (1 > number of parameters, which is 0)

Don't put the placeholder between single quotes:

String instqury = 
    "Select quality_name,quality_size,quality_weight,quality_spec " + 
    "from quality where quality_code = ?";
                                       ^-- here

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0-

change

PreparedStatement pst = (PreparedStatement) con.prepareStatement("query");

to

PreparedStatement pst = (PreparedStatement) con.prepareStatement(query);

Notice that you need to pass actual query variable not a "query" string

and as Mark noted below:
In addition pst.executeUpdate(query) needs to be changed to pst.executeUpdate().

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0) through jdbcTemplate

The problem is that the number of parameters in the SQL statement doesn't match with the number of provided arguments. It is not obvious by looking at the code, but

Object[] obj = new Object[3];
obj[0] = "Duke";
jdbcTemplate.update(sql, obj);

is basically the same as

jdbcTemplate.update(sql, new Object[] { "Duke", null, null });

JdbcTemplate assumes that the number of parameters in the SQL statement and in provided arguments array is the same.

To handle different number of arguments you can change from arrays to lists

List<Object> args = new ArrayList<>();
// some logic with args.add(...)
jdbcTemplate.update(sql, args.toArray());

An alternative is the NamedParameterJdbcTemplate:

Map<String, Object> args = new HashMap<>();
args.put("name", "Duke");

NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
jdbcTemplate.update("UPDATE user SET name = :name", args);

java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 4) on Netbeans

You have added a quote after last_name=?' hence its considering it as literal.

Place it after last_name and before =

and it should look like this,

String editQuery = "UPDATE `clients` SET `first_name` = ?, `last_name` = ?, `phone` = ?, `email` = ? WHERE `id` = ? "; 

Upvote the answer if it was helpful.

---

Related Topics