CODEWITHSUNDEEP
javasql-serverprepared-statement
Pavol
Pavol

Reputation: 19

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

I'm currently having a few problems with this code:

 public User validateUser(String username, String password) {

    boolean found = false;
    Connection c = DBHelperClass.getConnection();
    String query = "Select * from user where username= '?' and password= '?' ";


    if (c != null) {
        try {
            PreparedStatement inserter = c.prepareStatement(query);
            inserter.setString(1, username);
            inserter.setString(2, password);
            System.out.println("help: "+query);
            ResultSet resultSet = inserter.executeQuery(query);
            while (resultSet.next()) {
                this.userId = resultSet.getInt("userId");
                this.username = resultSet.getString("usrname");
                this.password = resultSet.getString("password");
                this.address = resultSet.getString("address");
                this.email = resultSet.getString("email");
                this.phone = resultSet.getInt("phone");

                found = true;

            }
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    return this;
}

The error I get is:

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

from reading other answers I tried changing around the '?' tags but failed to do so.

Upvotes: 1

Views: 2129

Answers (4)

Martin
Martin

Reputation: 149

Error message is saying that you have zero parameters in your query. It is because you have escaped question marks characters using apostrophes. Rewrite your query without using apostrophes around question marks.

String query = "Select * from user where username= ? and password= ?";

Upvotes: 0

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12401

change 

 Select * from user where username= '?' and password= '?'

to 

Select * from user where username= ? and password= ?

No need to add '

Update:

and change inserter.executeQuery(query); to inserter.executeQuery();

Upvotes: 6

Pavol
Pavol

Reputation: 19

Thanks for the help guys this is the fixed code:

public User validateUser(String username, String password) {

    boolean found = false;
    Connection c = DBHelperClass.getConnection();
    String query = "Select * from user where username= ? and password= ? ";


    if (c != null) {
        try {
            PreparedStatement inserter = c.prepareStatement(query);
            inserter.setString(1, username);
            inserter.setString(2, password);
            System.out.println("help: "+query);
            ResultSet resultSet = inserter.executeQuery();
            while (resultSet.next()) {
                this.userId = resultSet.getInt("userId");
                this.username = resultSet.getString("username");
                this.password = resultSet.getString("password");
                this.address = resultSet.getString("address");
                this.email = resultSet.getString("email");
                this.phone = resultSet.getInt("phone");

                found = true;

            }
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    return this;
}

All that I needed to change was to remove '' around ? and change executeQuery(query) to executeQuery()

Thanks a million!

Upvotes: 0

Nicolas Filotto
Nicolas Filotto

Reputation: 45005

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

You get this error because you call executeQuery(query) instead of executeQuery() such that it considers that your query doesn't need any parameters but you provided more than one which leads to this exception, so simply use inserter.executeQuery().

The second error as already mentioned is the fact that you don't need to use quotes in your query it should only be Select * from user where username= ? and password= ?

Upvotes: 1

Related Questions