How to fix column index out of range SQLException

Question

When I submit a request, I get below error.

Errorno is Nil Error String Is Query Problem..... java.sql.SQLException: Column Index out of range, 2 > 1. 

This is the code in my java method.

 PreparedStatement pstm=con.prepareStatement("select period from stu_attendancemaster where classid=? and absentdt>=? and absentdt<=?"); pstm.setInt(1,classid); pstm.setDate(2,fromdt); pstm.setDate(3,todt); System.out.println("qry for prd "+pstm.toString()); rs=pstm.executeQuery(); System.out.println("after qry for prd "+pstm.toString()); if(rs.next()) { stame = new Stu_AttendanceMasterEntity(rs.getInt(1), rs.getDate(2), rs.getInt(3), rs.getString(4), rs.getInt(5), rs.getString(6), rs.getTimestamp(7), rs.getString(8), rs.getTimestamp(9),rs.getString(10),rs.getInt(11),rs.getString(12)); } else { flag=false; errorstring=FN + P1 +" Class Name: " + Dep_ClassMasterDB.getClassname(classid) +" From Date: " +DateUtility.displayDate(fromdt,0) +" To Date: " +DateUtility.displayDate(todt,0) +N + V +DNE; } } catch(Exception e) { flag=false; errorstring="Query Problem..... "+e; 

Answer 1

The error in this statement is:

 PreparedStatement pstm=con.prepareStatement("select period from stu_attendancemaster where classid=? and absentdt>=? and absentdt<=?"); 

you need to select all 12 fields in the selected query.

Example: (I assume that you have 12 fields in the stu_attendancemaster table). Do it:

  PreparedStatement pstm=con.prepareStatement("select * from stu_attendancemaster where classid=? and absentdt>=? and absentdt<=?"); 

if not, you can change the query request as follows

 select `colName1`, `colName2`, `colName3`, `colName4`, `colName5`, `colName6`, `colName7`, `colName8`, `colName9`, `colName10`, `colName11`, `colName12`, from stu_attendancemaster where classid=? and absentdt>=? and absentdt<=? 

Note : colName* must be your actual column name in the table.

EDIT . If you only need the period from the query: just rs.getInt(1) and delete rs.getInt(2) to rs.getInt(12)

Rule of thumb : the number of columns in the select clause and ResultSet.getXXX() should be the same.

Answer 2

You select one column in your application and then access multiple columns in the ResultSet. To fix the problem, you must select from your database what you later want to read from the ResultSet.

Answer 3

Operator

select:

 ("select period from stu_attendancemaster where classid=? and absentdt>=? and absentdt<=?"); 

however, you get more fields than the period in your result set

 rs.getInt(1), rs.getDate(2), rs.getInt(3), rs.getString(4), rs.getInt(5), rs.getString(6), rs.getTimestamp(7), rs.getString(8), rs.getTimestamp(9),rs.getString(10),rs.getInt(11),rs.getString(12)); 

you cannot get this data from a result set when you simply select a period .