exception java.sql.SQLException: Exhausted Resultset

[sing rocky]

WHEN I AM CLICKING ON THE LINK AND 2 PARAMETERS ARE PASSSED NAMELY supregno and yy to the next page the code for this page is below now on this next page it doesn't display the columns that i am trying to select in the select statement. please if there is any one out there please help me where i am going wrong.

 
 
<%
  
  int datasize=0;
  int n=0;
  int pagecount=0;
  int pagesize=20;
  int mypage1=0;
  int pageno=0;
  int count = 0;
  int a=0;
      int page_next=0;
      int page_prev=0;
  String page1=null;
  String newvrn=null;
    String vrn=null;
  String vat1="";
  String supregno=request.getParameter("supregno");
      int total_invoice=0;
  int total_vat=0;
 
  
  String TSHsum_vamt=null,TSHsum_tvamt=null,TSHsum_vpay=null,tot_out_usd=null,tot_in_usd=null;
      String USDsum_vamt=null,USDsum_tvamt=null,USDsum_vpay=null,tot_out_tsh=null,tot_in_tsh=null;
 

  String gg=null;
  String str=null;
 
if (request.getParameter("supregno")!=null)
{
session.setAttribute("supreg",supregno);
}
 
if (session.getAttribute("supreg")!=null)
{
vat1=(String)session.getAttribute("supreg");
}
 
if ((request.getParameter("month")!=null) && (request.getParameter("year")!=null) && (request.getParameter("mypage")==null)){
String mon=request.getParameter("month");
String yyyy=request.getParameter("year");
 
session.setAttribute("month",mon);
session.setAttribute("year",yyyy);
 
pageno=1;
a=pageno;


}
 
String mont=(String)session.getAttribute("month");
String yy=(String)session.getAttribute("year");
 

 
  str="select NVL(A.VAT_REGISTRATION_NO,'-') VAT, INITCAP(NVL(B.BUSINESS_NAME,'-')) BUSINESS,nvl(SUPPLIER_VAT_NO ,'-') SUP_VAT,INITCAP(NVL(SUPPLIER_NAME,'-')) SUPNAME, NVL(TAX_INVOICE_NO,'') TAXINV, TO_CHAR(DATE_OF_INVOICE,'DD/MM/YYYY') INVOICE_DATE,NVL(A.CURRENCY_TYPE,'-') CUR, TO_CHAR(NVL(INVOICE_VALUE,0),'999,999,999,999.99') INVOICE,nvl(INVOICE_VALUE,0) INVOICE_VALUE, TO_CHAR(NVL(VAT_AMOUNT,0) ,'999,999,999,999.99') VATAMOUNT,nvl(VAT_AMOUNT,0) VAT_AMOUNT from TRADER_SUPPLIER_DETAIL A, TRADER_REGISTRATION_MASTER B WHERE A.VAT_REGISTRATION_NO = B.VAT_REGISTRATION_NO  AND SUPPLIER_VAT_NO='"+vat1+"'"+" AND A.ACCOUNTING_YEAR = '"+ yy + "' AND SUPPLIER_VAT_NO NOT LIKE '07%' AND SUPPLIER_VAT_NO NOT LIKE '08%'";
 

//AND SUPPLIER_VAT_NO='"+vat1+"'"+" 
/*
str="select NVL(A.VAT_REGISTRATION_NO,'-') VAT, INITCAP(NVL(B.BUSINESS_NAME,'-')) BUSINESS,nvl(SUPPLIER_VAT_NO ,'-') SUP_VAT,INITCAP(NVL(SUPPLIER_NAME,'-')) SUPNAME, NVL(TAX_INVOICE_NO,'') TAXINV, TO_CHAR(DATE_OF_INVOICE,'DD/MM/YYYY') INVOICE_DATE,NVL(A.CURRENCY_TYPE,'-') CUR, TO_CHAR(NVL(INVOICE_VALUE,0),'999,999,999,999.99') INVOICE,nvl(INVOICE_VALUE,0) INVOICE_VALUE, TO_CHAR(NVL(VAT_AMOUNT,0) ,'999,999,999,999.99') VATAMOUNT,nvl(VAT_AMOUNT,0) VAT_AMOUNT from TRADER_SUPPLIER_DETAIL A, TRADER_REGISTRATION_MASTER B WHERE A.VAT_REGISTRATION_NO = B.VAT_REGISTRATION_NO AND ACCOUNTING_MONTH='"+ mont +"' AND SUPPLIER_VAT_NO='"+vat1+"'"+" AND A.ACCOUNTING_YEAR = '"+ yy + "' AND SUPPLIER_VAT_NO NOT LIKE '07%' AND SUPPLIER_VAT_NO NOT LIKE '08%'";
*/
 
/*------------------------------ORDER BY CODING STARTS HERE---------------------------------------*/
/*------------------------------ORDER BY CODING STARTS HERE---------------------------------------*/
/*------------------------------ORDER BY CODING STARTS HERE---------------------------------------*/
 
if ((request.getParameter("ord")!=null))
{
 
String h=request.getParameter("ord");
session.setAttribute("hh",h);
}
 
//----------------------------------------
 
if((session.getAttribute("hh")!=null))
{
pageno=1;
a=pageno;
 
 gg=(String)session.getAttribute("hh");
 
if(gg.equals("VAT"))
{
 
  str="select NVL(A.VAT_REGISTRATION_NO,'-') VAT, INITCAP(NVL(B.BUSINESS_NAME,'-')) BUSINESS,nvl(SUPPLIER_VAT_NO ,'-') SUP_VAT,INITCAP(NVL(SUPPLIER_NAME,'-')) SUPNAME, NVL(TAX_INVOICE_NO,'') TAXINV, TO_CHAR(DATE_OF_INVOICE,'DD/MM/YYYY') INVOICE_DATE,NVL(A.CURRENCY_TYPE,'-') CUR, TO_CHAR(NVL(INVOICE_VALUE,0),'999,999,999,999.99') INVOICE,nvl(INVOICE_VALUE,0) INVOICE_VALUE, TO_CHAR(NVL(VAT_AMOUNT,0) ,'999,999,999,999.99') VATAMOUNT,nvl(VAT_AMOUNT,0) VAT_AMOUNT from TRADER_SUPPLIER_DETAIL A, TRADER_REGISTRATION_MASTER B WHERE A.VAT_REGISTRATION_NO = B.VAT_REGISTRATION_NO  AND SUPPLIER_VAT_NO='"+vat1+"'"+" AND A.ACCOUNTING_YEAR = '"+ yy + "' AND SUPPLIER_VAT_NO NOT LIKE '07%' AND SUPPLIER_VAT_NO NOT LIKE '08%'";
 
}
 
/*
if(gg.equals("FINAL"))
{
  str="SELECT A.VAT_REGISTRATION_NO VAT,initcap(C.BUSINESS_NAME) PNAME,TO_CHAR(A.TOTAL_VAT_AMOUNT,'999,999,999,999.99') OUTPUTTAX,TO_CHAR(B.TOTAL_VAT_AMOUNT,'999,999,999,999.99') INPUTTAX, TO_CHAR(A.TOTAL_VAT_PAYABLE,'999,999,999,999.99') TTPAY, TO_CHAR(A.CREDIT_AMOUNT_FORWARD,'999,999,999,999.99') CREDPAY, TO_CHAR(A.FINAL_AMOUNT_TO_PAY,'999,999,999,999.99') FINAL, A.CURRECY_TYPE CUR,TO_CHAR(A.APPLICANT_DATE,'DD/MM/yyYY') SUBMIT_DATE FROM TRADER_RETURN_SALES A, TRADER_RETURN_PURCHASE B, TRADER_REGISTRATION_MASTER C WHERE A.VAT_REGISTRATION_NO = B.VAT_REGISTRATION_NO AND A.VAT_REGISTRATION_NO = C.VAT_REGISTRATION_NO AND A.ACCOUNTING_MONTH = B.ACCOUNTING_MONTH AND A.ACCOUNTING_YEAR = B.ACCOUNTING_YEAR AND  A.ACCOUNTING_MONTH = '"+ mont +"' AND A.ACCOUNTING_YEAR = '"+ yy + "' order by cur,A.FINAL_AMOUNT_TO_PAY";
}
*/
}
if(request.getParameter("mypage")!=null)
{
if((session.getAttribute("hh")!=null))
{
String pg =request.getParameter("mypage");
pageno=Integer.parseInt(pg);
a=pageno;
 }
}
 

 
if (request.getParameter("mypage")!=null)
{
if((session.getAttribute("hh")==null))
{
 
String pg =request.getParameter("mypage");
pageno=Integer.parseInt(pg);
a=pageno;
 

str="select NVL(A.VAT_REGISTRATION_NO,'-') VAT, INITCAP(NVL(B.BUSINESS_NAME,'-')) BUSINESS,nvl(SUPPLIER_VAT_NO ,'-') SUP_VAT,INITCAP(NVL(SUPPLIER_NAME,'-')) SUPNAME, NVL(TAX_INVOICE_NO,'') TAXINV, TO_CHAR(DATE_OF_INVOICE,'DD/MM/YYYY') INVOICE_DATE,NVL(A.CURRENCY_TYPE,'-') CUR, TO_CHAR(NVL(INVOICE_VALUE,0),'999,999,999,999.99') INVOICE,nvl(INVOICE_VALUE,0) INVOICE_VALUE, TO_CHAR(NVL(VAT_AMOUNT,0) ,'999,999,999,999.99') VATAMOUNT,nvl(VAT_AMOUNT,0) VAT_AMOUNT from TRADER_SUPPLIER_DETAIL A, TRADER_REGISTRATION_MASTER B WHERE A.VAT_REGISTRATION_NO = B.VAT_REGISTRATION_NO AND SUPPLIER_VAT_NO='"+vat1+"'"+" AND A.ACCOUNTING_YEAR = '"+ yy + "' AND SUPPLIER_VAT_NO NOT LIKE '07%' AND SUPPLIER_VAT_NO NOT LIKE '08%'";
 
}
}
 
/*------------------------------------ORDER BY CODING ENDS HERE-----------------------------------------------*/
/*------------------------------------ORDER BY CODING ENDS HERE-----------------------------------------------*/
/*------------------------------------ORDER BY CODING ENDS HERE-----------------------------------------------*/
 

/*Statement stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );*/
 
Statement stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
 
/*Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );*/
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
 
ResultSet rs2 = null;
ResultSet rs3 = null;
ResultSet rs4 = null;
 
//---------------------------------------------------------------------------------------------------
//Sum Of Shillings
//---------------------------------------------------------------------------------------------------
 
try
{

rs2 = stmt.executeQuery("select TO_CHAR(SUM(INVOICE_VALUE),'999,999,999,999.99') TOTAL_INVOICE,TO_CHAR(SUM(VAT_AMOUNT),'999,999,999,999.99') TOTAL_VAT from TRADER_SUPPLIER_DETAIL A, TRADER_REGISTRATION_MASTER B WHERE A.VAT_REGISTRATION_NO = B.VAT_REGISTRATION_NO  AND SUPPLIER_VAT_NO='"+vat1+"'"+" AND A.ACCOUNTING_YEAR = '"+ yy + "' AND SUPPLIER_VAT_NO NOT LIKE '07%'  AND SUPPLIER_VAT_NO NOT LIKE '08%'"); 
/*
rs2 = stmt.executeQuery("Select  TO_CHAR(TO_NUMBER(SUM(TOTAL_INPUT)),'999,999,999,999.99')  TOTAL_INPUT,TO_CHAR(TO_NUMBER(SUM(TOTAL_OUTPUT)),'999,999,999,999.99')  TOTAL_OUTPUT,TO_CHAR(TO_NUMBER(SUM(TOTAL_VAT_PAYABLE)),'999,999,999,999.99') TPAY from view_monthly_return where ACCOUNTING_month='"+mont+"'and ACCOUNTING_YEAR="+yy+ " and currecy_type = 'TSH' AND TOTAL_VAT_PAYABLE > 0");           
*/          
 
while (rs2.next()){
 
tot_in_tsh=rs2.getString("TOTAL_INVOICE");
tot_out_tsh=rs2.getString("TOTAL_VAT");

}

}
catch(Exception e)
{
out.println("Exception:"+e);
}
 

try
{
 
/*
rs3 = stmt.executeQuery("Select TO_CHAR(TO_NUMBER(SUM(TOTAL_INPUT)),'999,999,999,999.99')  TOTAL_INPUT,TO_CHAR(TO_NUMBER(SUM(TOTAL_OUTPUT)),'999,999,999,999.99') TOTAL_OUTPUT,TO_CHAR(TO_NUMBER(SUM(TOTAL_VAT_PAYABLE)),'999,999,999,999.99') TTPAY from view_monthly_return  where ACCOUNTING_month='"+mont+"'and ACCOUNTING_YEAR="+yy+ " and currecy_type = 'USD' AND TOTAL_VAT_PAYABLE > 0");
*/
 

rs3 = stmt.executeQuery("select TO_CHAR(SUM(INVOICE_VALUE),'999,999,999,999.99') TOTAL_INVOICE,TO_CHAR(SUM(VAT_AMOUNT),'999,999,999,999.99') TOTAL_VAT from TRADER_SUPPLIER_DETAIL A, TRADER_REGISTRATION_MASTER B WHERE A.VAT_REGISTRATION_NO = B.VAT_REGISTRATION_NO AND SUPPLIER_VAT_NO='"+vat1+"'"+" AND A.ACCOUNTING_YEAR = '"+ yy + "' AND SUPPLIER_VAT_NO NOT LIKE '07%'  AND SUPPLIER_VAT_NO NOT LIKE '08%'"); 
 
while (rs3.next()){

tot_in_tsh=rs2.getString("TOTAL_INVOICE");
tot_out_tsh=rs2.getString("TOTAL_VAT");
}
 

}
catch(Exception e)
{
//out.println("Exception:"+e);
}
 

 
try
{
 
ResultSet rs1 = stmt.executeQuery(str);           
 
 while(rs1.next())
{
      datasize=datasize+1;
}
 

}
catch(Exception e)
{
out.println("Exception:"+e);
}
 
int it = datasize%20;
 
if (it == 0){
pagecount=datasize/pagesize;
}else{
pagecount=datasize/pagesize;
pagecount++;
}
 

 

if (datasize==0) 
  {
out.println("<br><br><br><br><br><br><br><br><br><center><b><FONT SIZE=2 FACE=Georgia color=black>"+"No Data Found"+"<a href=javascript:history.go(-1) style='text-decoration: none'>   Back</a></font></b></center>");
  }
 
else
{
 try
{
   ResultSet rs = stmt.executeQuery(str);           
 

    
out.println("<br><br><br><table border=0 cellpadding=0 cellspacing=0 width='100%'><tr><td valign=middle align=center width=100% ><font color=black face=verdana size=2><b>VAT Returns received for "+mont+" "+yy+"</b></font></td></tr></table>");
 
out.println("<table border=0 cellspacing=0 cellpadding=0  align=center><tr><td valign=center'><font color=black face=verdana size=2><b>Total Returns received</b></font></td><td><font color=red =verdana size=2><b>  "+datasize+"</b></font></td></table><br>");

out.println("<table border=0 cellspacing=0 cellpadding=0 width = '100%'><tr><td><font color=black face=verdana size=2><b>                                                                                                & nbsp      Invoice Amount       VAT Amount</b></font></td></tr></table>");
 

/********************************************************************************************/
/********************************************************************************************/
 
out.println("<table border=1 bordercolor=#333366 align=center width = '60%'><tr><td align = left width = '60%'><font color=black face=verdana size=2>Total </font></td><td align = right width = '30%'><font color=black face=verdana size=2>"+tot_in_tsh+"</font></td><td align=right width = '30%'><font color=black face=verdana size=2>"+tot_out_tsh+"</font></td></tr></table><br>");
 
//-------------------------
 
out.println("<table border=0 cellspacing=2 cellpadding=2 width=100% align=center><tr><td class=darktr><center><font color='white'>VRN</font></center></td><td class=darktr><center><font color='white'>Business name</font></center></td><td class=darktr><center>Sno</center></td><td class=darktr><center><font color='white'>Supplier VRN</font></center></td><td class=darktr><center><font color='white'>Supplier name</font></center></td><td class=darktr width=2%'><center><font color='white'>Tax Invoice no</font></center></td><td class=darktr><center><font color='white'>Invoice Date</font></center></td><td class=darktr width=10% ><center>Currency</center></td><td class=darktr width=15% ><center>Invoice value</center></td><td class=darktr width=15% ><center>VAT amount<center></td></tr>");
 
int rec_no=0;
rec_no=((pageno-1)*pagesize)+1;
rs.absolute(rec_no); 
count =((pageno - 1)* pagesize);
newvrn="ABC";
do
{
n=n+1;
 
vrn=rs.getString("VAT");
 
if (newvrn.equals(vrn))
 

 
//out.println("<td class=tdt><a href=list.jsp?vatregno="+newvrn+">"+rs.getString("VAT")+"</td>");
{

 
out.println("<td></td>");
out.println("<td></td>");
}
else
{
newvrn=vrn;
 
if (n!=1)
{
 
        out.println("<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td class=tdt align =right width='25%'><B>Total</B></td><td class=tdt align =right width='15%'>"+total_invoice+"</td><td class=tdt align =right width='15%'>"+total_vat+"</td></tr>");
total_invoice=0;
total_vat=0;
}
 
out.println("<td class=tdt><a href=list.jsp?vatregno="+rs.getString("VAT")+">"+rs.getString("VAT")+"</td>");
out.println("<td class=tdt align =left width='35%'>"+rs.getString("BUSINESS")+"</td>");

}


out.println("<td class=tdt  >"+(count+ n)+"</td>");
//out.println("<td class=tdt align =left width='5%'>"+rs.getString("SUP_VAT")+"</td>");
//---------
//out.println("<td class=tdt align =left width='5%'>"+rs.getString("SUP_VAT")+"</td>");
out.println("<td class=tdt><a href=main_land_purchase_details.jsp?svatregno="+rs.getString("SUP_VAT")+">"+rs.getString("SUP_VAT")+"</td>");
//---------
out.println("<td class=tdt align =left width='35%'>"+rs.getString("SUPNAME")+"</td>");
out.println("<td class=tdt align =left width='2%'>"+rs.getString("TAXINV")+"</td>");
out.println("<td class=tdt align =left width='7%'>"+rs.getString("INVOICE_DATE")+"</td>");
 
if(rs.getString("CUR").equals("USD"))
{
out.println("<td class=tdt align =left width='3%'><CENTER><FONT COLOR='RED'>"+rs.getString("CUR")+"</FONT></CENTER></td>");
}
 
      if(rs.getString("CUR").equals("TSH"))
{
out.println("<td class=tdt align =left width='5%'><CENTER><FONT COLOR='GREEN'>"+rs.getString("CUR")+"</FONT></CENTER></td>");
}

out.println("<td class=tdt align =right width='15%'>"+rs.getString("INVOICE")+"</td>");

total_invoice=total_invoice+rs.getInt("INVOICE_VALUE");
 
out.println("<td class=tdt align =right width='15%'>"+rs.getString("VATAMOUNT")+"</td></tr>");
total_vat=total_vat+rs.getInt("VAT_AMOUNT");
 

 
}
while(rs.next());
 

out.println("</table>");
 
}
catch(Exception e)
{
out.println("exception "+e);
}
 
out.println("<br>");
 
/*
int k=0;
out.println("<table align=center><tr><td><font>");
 
    page_prev=a-1;
page_next=a+1;
 
if(page_prev==0)
{
page_prev=1;
    }
 
out.println("<a href=main_land_claims.jsp?mypage="+page_prev+" style='text-decoration: none'><b><FONT SIZE=2 COLOR=BLUE FACE=Couriernew>");
out.println("<IMG border = '0'SRC='larrow.gif'></img>");
out.println("<font></b><a/>     ");
 

while(k<pagecount)
{

k=k+1;
if (k==a){
 
out.println("<a href=main_land_claims.jsp?mypage="+k+" style='text-decoration: none'><b><FONT SIZE=2 COLOR=RED FACE=Couriernew>");
 
}else{
 
out.println("<a href=main_land_claims.jsp?mypage="+k+" style='text-decoration: none'><b><FONT SIZE=2 COLOR=BLACK FACE=Couriernew>");
 
}
out.println(""+k);
out.println("<font></b><a/>");
 
}
 

if(page_next > (pagecount))
{
page_next= pagecount; 

}
 

  out.println("    <a href=main_land_claims.jsp?mypage="+page_next+" style='text-decoration: none'><b><FONT SIZE=2 COLOR=BLUE FACE=Couriernew>");
 
out.println("<IMG border = '0' SRC='rarrow.gif'></img>");
out.println("<font></b><a/>");*/
%>
 

<%
}
%>
   

[Ad Rajesh]

Are you getting any exception while running this code.
Post the exceptions.

[Ad Rajesh]

Post the exception which you are getting while running the program.

[sing rocky]

The code that i pasted earlier is for the page that doesn't display the data under The following columns that i have designed in that code :-(VRN,Business name,Sno,Supplier VRN,Supplier name,Tax Invoice no,Invoice Date,Currency,Invoice value,VAT amount)in the above it displays the following message:-
exception java.sql.SQLException: Exhausted Resultset

Thanking you in advance,

Sing

[sing rocky]

THE FOLLOWING IS THE MESSAGE THAT IT DISPLAYS:-

exception java.sql.SQLException: Exhausted Resultset

NO OTHER ERROR OR EXCEPTION IS DISPLAYED APART FROM THE ABOVE ERROR.


Appreciate all your responses,

Regards,
Sing

[Ben Souther]

Sing,

Please note: typing with the caps-lock button down is the equivalent to yelling in a web forum and is considered rude.

Please don't do that.

-Ben

[sing rocky]

sorry, in case some one was offended .But the reason i wrote in capitals was because the error came in lowere case.

I really didn't mean to yell at any one.

cheers

Sing