How to fix Column Index out of range SQLException

Question

When i'm passing a query i'm getting 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

Error is in this statement:

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

you have to select all the 12 fields in your select query.

Ex: (I am assuming that you have 12 fields in your table stu_attendancemaster) Do this:

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

if not you can modify your query statement like this

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

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

EDIT : In case if you need only period from the query: just have rs.getInt(1) and remove rs.getInt(2) to rs.getInt(12)

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

Answer 2

You select one column in your statement and then access more than one column in your ResultSet. To fix your problem, you have to select from your database what you later want to read from the ResultSet.

Answer 3

select statement is:

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

however you are getting more fields than period in your resultSet

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 these data from the resultset when you are just selecting period.