why rowset exhausted error coming?

[pmani kandan]

hai to all

In my Project i am getting data from one recordset .using this recordset value i can get another value, so i open new recordset object it gives an error . so i tried in anotherway like to create new connection new preparestatement and recrdset but it gives an error msg . how to i solve this problem .if any idea how to get two values from the table.

thnx
p.manikandan

[Shailesh Chandra]

can you paste snippet of code here !!! your problem is not very clear.

also paste the exception ,if there is any

[pmani kandan]

this is servlet file
call the method
if(action.equals("Edit")||action.equals("New")){
cntrTypeCheck(conn,PPNo,destPort,pol,conn1);}conn.commit();

public void cntrTypeCheck(conn,PPNo,destPort,pol,conn1){
....
....

pstmt = conn.prepareStatement("select ......");
rs = pstmt.executeQuery();
while(rs.next()){
flag = true;
cntNo = rs.getString(1);
msg+=cntNo;
---
---
pstmt1 = conn1.prepareStatement("select ....");
rs1 = pstmt.executeQuery();
while(rs1.next()){
String offHire = rs.getString(1);
}
msg=msg+offHire;
if(pstmt1!= null)pstmt1.close();
if(rs1 != null) rs1.close();
det = rs.getString(6);
if(det== null)
det=" ";
messg+=det+".";

}catch(Exception exc){


.....
.....
}

[Shailesh Chandra]

dear pmani,

first of all the way you are creating pstmt1 is not perfect.
the number of records in outer resulult set will cause the same number of times pstmt1 to be created instead of this you can create pstmt1 once and reuse it by calling method clearParameters() over statement.

can you paste the exception/error you are getting

[pmani kandan]

hai Shailesh Chandra

ok fine its working.now error was corrected .
ok thanks .

yester day you are said prepareStatement handling is not good . but now i am using in this way. if u send any sample codeing i can follow that format
ok
thank u
p.manikandan

[Shailesh Chandra]

Dear Manik

The things I noticed was that preparedstatement pstmt1 that was inside the rs.next loop was being creating muliple times which can be avoided


I have changed the method of your little bit. but actual implemetation of method is depends on your requirement.


if("Edit".equals(action)||"New".equals(action))
{

cntrTypeCheck(conn,PPNo,destPort,pol,conn1);
}
conn.commit();



public void cntrTypeCheck(conn,PPNo,destPort,pol,conn1)
{
....
....

PreparedStatement pstmt = null;
PreparedStatement pstmt1 = null;

pstmt = conn.prepareStatement("select ......");
/**
I changed conn1 to conn and moved this line out of your
loop rs.next()

Putting same inside the rs.next() was causing prepared statement
to be creates as number of time as much records are in result set

**/
pstmt1 = conn.prepareStatement("select ....");

rs = pstmt.executeQuery();

while(rs.next())
{
flag = true;
rs1 = null;
cntNo = rs.getString(1);
msg+=cntNo;

rs1 = pstmt1.executeQuery();
while(rs1.next())
{
String offHire = rs.getString(1);
}
msg=msg+offHire;

if(rs1 != null)
{
rs1.close();
}

det = rs.getString(6);
if(det== null)
det=" ";
messg+=det+".";

}
if(pstmt1!= null)
{
pstmt1.close();
}
[ October 12, 2004: Message edited by: Shailesh Chandra ]