SQL Count() Invalid curser state

[John Martinson]

Okay,
I'm getting an Invalid cursir stat Error when I try to execute the following code.
myStmt = "Select COUNT(Closure) " +
" FROM moded" +
" WHERE " +
"(closure = \'OPEN\' AND " +
"[logged on] > #"+dBegin+"# AND " +
"[logged on] < #" +dEnd + "# AND " +
"field38 = \'" +catCount[i][0] +"\');";
System.out.println(myStmt);
rs = stmt.executeQuery(myStmt);
rs.beforeFirst();
rs.next();
System.out.println(rs.getInt(1)); <------
Error occurs here, and I don't know why. Should the rs be 1 int?

Thank you in advance
John Martinson

[Craig Jackson]

Could it be that the command:

 rs.next();
 

returned "false", which basically means that your query did not find any matches?

[John Martinson]

boolean whar = rs.next();
system.out.println(what);  

yeilds "true" so rs.next(); is not failing.

[John Martinson]

Problem resolved. No Idea what it was I just changed retyped my SQL statement to make it clearer to read and it worked. No idear what made it work but here's the code for those really demented people who might want to figure it out.

myStmt = "SELECT COUNT(Closure) from moded " +
"WHERE closure = 'OPEN' " +
"AND [logged on] > #"+dBegin+"#" +
"AND [logged on] < #"+dEnd+"#; ";
System.out.println(myStmt);
stmt.execute(myStmt);
rs = stmt.getResultSet();
//rs.beforeFirst();
rs.next();
String s = rs.getString(1);
System.out.println(s);  

[Jamie Robertson]

Old where clause: WHERE closure = \'OPEN\'
New where clause: WHERE closure = 'OPEN'

the old query could not find a row that matched closure = \'OPEN\', so as stated earlier rs.beforFirst() method would "bomb" out because no rows were returned. When you changed to closure = 'OPEN' it found a row, and everything worked as planned. you should always check (if ( rs.next() ) if a row exists before calling any methods on the ResultSet.
[ June 21, 2005: Message edited by: Jamie Robertson ]