CODEWITHSUNDEEP
javamysql
Rohan
Rohan

Reputation: 1352

No value specified for parameter 3 or index out of range

I am new to mySQL and databases. I am running into Parameter index out of range error and if I comment out the offending line I receive a No value specified for parameter 3 error.

I made a database called 'test' in mySQL. Within test, I create a simple table, person,: 

create table person ( 
    id int, 
    name varchar(32), 
    age int 
);

My simple program is below : 

public class Trial {

    static Connection sqlConnection = null;

    public static void main(String[] args) throws SQLException {
        connectToSQL();
        insertData(101,"Mark", 21);
    }
    public static void connectToSQL() {
        try{
            Class.forName("com.mysql.jdbc.Driver");
            sqlConnection = DriverManager
                      .getConnection("jdbc:mysql://localhost/test?"
                              + "user=root&password=mypass");
        } catch(Exception e) {
            System.out.println("could not connect to mySQL");
            e.printStackTrace(); 
        }
        System.out.println("connected to mySQL");
    }

    public static void insertData(int id, String name, int age) throws SQLException {
        String query = "insert into person" + " (id,name, age)" + " values (?,?,?)";
        PreparedStatement ps = sqlConnection.prepareStatement(query);

        ps.setInt(0, id);
        ps.setString(1, name);
        ps.setInt(2, age);
        ps.execute();
        System.out.println("inserted data");
    }
}

The error comes in insertData(int i,String s, int n); If I leave in ps.set(0,d); then I get Parameter index out of range error If I comment out ps.set(0,d); then I get No value specified for parameter 3.

Can I have some intuition for resolving problems like this in the future?

Upvotes: 0

Views: 571

Answers (1)

AntonH
AntonH

Reputation: 6437

PreparedStatement's indices start at 1. So instead of setting your parametres to 0, 1, 2, set them to 1, 2, 3.

ps.setInt(1, id);
ps.setString(2, name);
ps.setInt(3, age);

As to your second question: There's no real intuition to resolving these kinds of problems. The intuition will come with experience. What helps is a quick look at docs/tutorials, and seeing if something doesn't pop out at you that differs to your code/solution. Maybe take a break, and come back a bit later to get a fresh look at what you've done, see if you don't spot something you missed before.

Upvotes: 3

Related Questions