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

[Sachindra Pratap]

code:
---------------------------------------------------------------
String query="INSERT INTO opr_rt_info VALUES ('"+"?"+"','"+
"?"+"','"+"?"+"','"+"?"+"','"+"?"
+"','"+"?"+"')";
System.out.println(query);
pstmt = conn.prepareStatement(query);
pstmt.setString(1,opr_name1); // Exception is thrown
pstmt.setString(2,opr_mode1);
pstmt.setString(3,rt_name1);
pstmt.setString(4,rt_ch_no1);
pstmt.setString(5,rt_ch_freq1);
pstmt.setString(6,rt_selection_flag1);
int res1=pstmt.executeUpdate(query);
--------------------------------------------------------------------end


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

Exception is thrown on instruction

pstmt.setString(1,opr_name1);

if anybody has solution then please help me out.
Regards Sachindra

[Paul Sturrock]

You are using a sql string like this:

INSERT INTO opr_rt_info VALUES ('?','?','?','?','?','?')

which will insert six question marks into your table.

If you want to use a PreparedStatement (which is a good thing) get rid of the quotes round the question marks.
[ August 21, 2008: Message edited by: Paul Sturrock ]

[Sachindra Pratap]

Thankyou Paul,
I did same what you suggested ,and it worked.

regards Sachindra

[imran sujoy]

I'm facing the same problem.

My java code:

   
String sqlStatement="INSERT INTO User VALUES (?,?,?,?,?,?)";
    try{
    	if(ds!=null)
         con=giveConnection(ds);
    	if(con!=null){    	
        pstmt = con.prepareStatement(sqlStatement);        
        String fname=registerForm.getFname().trim().toUpperCase();
        String lname=registerForm.getLname();
        if(!lname.equals(null))
            lname=lname.trim().toUpperCase();
        else
            lname="";
        userExist = checkDuplicateUser(fname, con);
        
        if(userExist!=0)
            affectedRow = -1;
        else{
        pstmt.setString(1, fname);
        pstmt.setString(2, lname);
        pstmt.setString(3, "user");
        pstmt.setInt(4, 0);
        pstmt.setString(5, PasswordEncryptor.getInstance().encrypt(registerForm.getPassword()));
        pstmt.setString(6, registerForm.getEmail());
        log.debug("sqlStatement: "+sqlStatement);
        affectedRow=pstmt.executeUpdate();
        con.commit();
        }
        }
      }catch(SQLException sq){
    	  log.debug("inside sql exception of add user");
    	  sq.printStackTrace();}
      finally{
        try{
           pstmt.close();
           con.close();
        }catch(SQLException sq){
        	log.debug("inside sql exception of add user:2");
        	
        }
       }
    return affectedRow;
    }
 

Server log:

 
java.sql.SQLException: Parameter index out of range (1 > number of parameters, w
hich is 0).
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
        at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3
326)
        at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3
310)
        at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:414
6)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(Delegat
ingPreparedStatement.java:132)
        at com.onlinelibrary.dao.OnlineLibraryDAO.addUser(OnlineLibraryDAO.java:
65)
        at com.onlinelibrary.action.RegisterLibraryAction.execute(RegisterLibrar
yAction.java:65)
   

mysql table structure:

  
mysql> desc user;
+-------------+----------------------+------+-----+---------+-------+
| Field       | Type                 | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| fname       | varchar(20)          | NO   | PRI |         |       |
| lname       | varchar(15)          | YES  |     | NULL    |       |
| designation | enum('admin','user') | YES  |     | NULL    |       |
| issues      | smallint(5) unsigned | YES  |     | NULL    |       |
| password    | varchar(100)         | YES  |     | NULL    |       |
| email       | varchar(100)         | YES  |     | NULL    |       |
+-------------+----------------------+------+-----+---------+-------+
6 rows in set (0.33 sec)

  

I'm getting exception at:

 
pstmt.setString(1, fname); 
   

I've already registered one user with this. But after that now I'm getting this exception.

Can somebody kindly help?

Thanks

[imran sujoy]

===SOLVED===

Please forget about it. I found why the error was occurring. I was actually using one single PreparedStatement for two different methods (silly me), and when the flow returned from the called method, it still was the first preparedstatement rather than the new one, hence it couldn't found the parameter in the proper indexed position.