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

Question

public class Brothers extends javax.swing.JFrame {

    /**
     * declaring connection and SQL statement
     */

    Connection cn;
    Statement st;
    PreparedStatement pstmt=null;
    PreparedStatement pst;
    ResultSet rs;
    Object fname, mname, lname, bdate, nation, statusq,InstNo,  photo, combo, place, mimi; 
    int status;



    public Brothers() {        
        dbconnect _db = new dbconnect();

/*//////////////the above is just to show that I have two prepared statement each for each sql statement that i have //////////*/
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //String unicode= "?useUnicode=yes&characterEncoding=UTF-8";
            cn = DriverManager.getConnection(_db.getHost(), _db.getUsername(), _db.getPassword());
            st=cn.createStatement(); 

 try{

        String Sql="INSERT INTO brothers(FirstName, MiddleName, LastName, BirthDate, BirthPlace, Nationality, InstituteNumber, Status, Picture) VALUES(?,?,?,?,?,?,?,?,?)";  
        pstmt=cn.prepareStatement(Sql);
        //pstmt.setString(1,txtTrial.getText());('?','?','?','?','?','?','?','?','?')
        pstmt.setString(2,txtFirtsName.getText());
        pstmt.setString(3,txtMiddleName.getText());
        pstmt.setString(4,txtLastName.getText());
        pstmt.setString(5,((JTextField)chooserBirthDate.getDateEditor().getUiComponent()).getText());
        pstmt.setString(6,txtPlacBirth.getText());

        String nations=combonation.getSelectedItem().toString();
        pstmt.setString(7,nations);
        pstmt.setString(8,txtInstituteNo.getText());


        pstmt.setObject(9,combostatus.getSelectedItem());
        pstmt.setBytes(10, person_image);

      pstmt.executeUpdate(Sql);

        JOptionPane.showMessageDialog(null, "Saving Successfull");

        }

        catch(Exception e){

          //JOptionPane.showMessageDialog(null, e);
          e.printStackTrace(); 
    }

When I try to inset data using the above code it throws an exception:

java.sql.SQLException: Parameter index out of range (10 > number of parameters, which is 9).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3813)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3795)

I have tried to look over it but I cant find the problem please help!!

when I use this instead I am able to insert data except for the picture: try{ String addrecords="insert into brothers(FirstName, MiddleName, LastName, BirthDate, BirthPlace, Nationality, InstituteNumber, Status, Picture) values('"+ fname +"', '" + mname +"', '" + lname +"', '" + dave +"', '" + place +"', '" + isa +"', '" + InstNo +"', '" + statusq +"', '" + photo +"')"; //wrapField(); st.executeUpdate(addrecords); } — Isaiah
– Isaiah, Commented Apr 24, 2013 at 15:56
You should change the title of the question to match the actual message of the exception that you put in the body of the question ! — Pierre Henry
– Pierre Henry, Commented Apr 25, 2013 at 7:00

fmodos · Accepted Answer · 2013-04-24 15:58:26Z

7

You are starting the insert at position 2 instead 1

 pstmt.setString(2,txtFirtsName.getText());

Change it to

pstmt.setString(1,txtFirtsName.getText());

Do the same for the others where the last one will be

pstmt.setBytes(9, person_image);

answered Apr 24, 2013 at 15:58

fmodos

4,5581 gold badge18 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Pierre Henry Over a year ago

Of course you are right, but either there is another problem, or the error message "Parameter index out of range (2 > number of parameters, which is 0)" is quite misleading. As far as I know you don't have to set the parameters in order (you could set 2, then 3, then 1) so it should crash when he attempts to set 10, not 2 ?

fmodos Over a year ago

Please read the exception again, it is "(10 > number of parameters, which is 9)" which means that he is trying to insert a parameter in position 10 but it only support 9 parameters. Also I didn't say that he need to set the parameters in order.

Isaiah Over a year ago

My table has ten columns: BrothersID,FirstName, MiddleName, LastName, BirthDate, BirthPlace, Nationality, InstituteNumber, Status, Picture. BrothersID is an auto increment meaning I don't have to enter anything on it!!! But I even tried by cheating it by creating a text filed that I dont use ...see //pstmt.setString(1,txtTrial.getText()); but still it didnt work

Pierre Henry Over a year ago

Sure, sorry, I didn't properly read the exception in the body of thequestion, and look at the title of the question !

Isaiah Over a year ago

Guys when I do as you have suggested the error changes to: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?,?,?,?,?,?)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

|

AlexVogel · Accepted Answer · 2014-06-18 12:18:40Z

3

The number of question marks ? must equal to the parameter.

If you have 7 parameter then there should be 7 question marks in your statement i.e insert into table name values(?,?,?,?,?,?,?)

edited Jun 18, 2014 at 12:18

AlexVogel

10.6k10 gold badges64 silver badges71 bronze badges

answered Jun 18, 2014 at 11:52

user3235752

311 bronze badge

Comments

Bill the Lizard · Accepted Answer · 2013-04-24 15:56:41Z

2

You only have 9 parameters in your SQL statement.

String Sql="INSERT INTO brothers(FirstName, MiddleName, LastName, BirthDate, BirthPlace, Nationality, InstituteNumber, Status, Picture) VALUES(?,?,?,?,?,?,?,?,?)";

When you commented out the first setString method call, you should have renumbered the parameter indices for the following calls. They should be numbered 1 - 9, not 2 - 10.

answered Apr 24, 2013 at 15:56

Bill the Lizard

407k213 gold badges579 silver badges892 bronze badges

2 Comments

Isaiah Over a year ago

Guys when I do as you have suggested the error changes to: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?,?,?,?,?,?)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

Isaiah Over a year ago

My table has ten columns: BrothersID,FirstName, MiddleName, LastName, BirthDate, BirthPlace, Nationality, InstituteNumber, Status, Picture. BrothersID is an auto increment meaning I don't have to enter anything on it!!! But I even tried by cheating it by creating a text filed that I dont use ...see //pstmt.setString(1,txtTrial.getText()); but still it didnt work

Toni Toni Chopper · Accepted Answer · 2013-04-24 15:58:15Z

0

The index of the first parameter should be 1 not 2, the index of the last parameter should be 9 not 10. When assigning N parameters, the index goes from 1 to N.

answered Apr 24, 2013 at 15:58

Toni Toni Chopper

1,8512 gold badges20 silver badges29 bronze badges

1 Comment

Isaiah Over a year ago

When I do as you have suggested the error changes to: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?,?,?,?,?,?)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

rao arsalan · Accepted Answer · 2016-03-09 16:31:24Z

0

use psmt.executeUpdate(); instead of pstmt.executeUpdate(Sql);

answered Mar 9, 2016 at 16:31

rao arsalan

1

Collectives™ on Stack Overflow

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

5 Answers 5

7 Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related