Group By Problem
Hello all,
I have created some jsp pages that show relevant information from a database and pojo's and servlets that deal with the actual requests. I am connecting to a MS Access 2000 database. However, I am having trouble with one of my SELECT queries. The query in the code below works without any group by clauses but when I add them I get the error:
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
The code:
publicstatic ArrayList getInvoiceSummary(Calendar startDate, Calendar endDate, String invoiceClient, String charged)
throws SQLException, Exception
{
ArrayList all =new ArrayList();
Database db =new AccessDatabase();
ResultSet rs =null;
SimpleDateFormat sdf =new SimpleDateFormat("dd/MMM/yyyy");
String ssdf1 =null;
String ssdf2 =null;
String queryString ="";
ssdf1 = sdf.format(startDate.getTime());
ssdf2 = sdf.format(endDate.getTime());
String error;
try
{
queryString ="SELECT tlkpClient.Client, tlkpIssue.IssueName,
tlkpInstruction.Instruction, tlkpActivity.Activity, tblData.Chargable,
tlkpChargeBand.ChargeDescription FROM tlkpChargeBand INNER JOIN
(tlkpActivity INNER JOIN (tlkpInstruction INNER JOIN (tlkpClient INNER JOIN
(tlkpIssue INNER JOIN tblData ON tlkpIssue.IssueID = tblData.IssueID) ON
tlkpClient.ClientNo = tblData.ClientNo) ON tlkpInstruction.InstructionID =
tblData.InstructionID) ON tlkpActivity.ActivityID = tblData.ActivityID) ON
tlkpChargeBand.ChargebandID = tblData.ChargebandID WHERE
(tblData.[Date] <= #" + ssdf2 + "# AND tblData.[Date] >= #" + ssdf1 + "#) AND
tlkpClient.Client=\'" + invoiceClient + "\'" + toCharge + " GROUP BY
tlkpClient.Client, tlkpIssue.IssueName, tlkpInstruction.Instruction,
tlkpActivity.Activity, tblData.Chargable, tlkpChargeBand.ChargeDescription";
rs = db.execute(queryString);
while(rs.next())
{
all.add(rs.getString("Client"));
all.add(rs.getString("IssueName"));
all.add(rs.getString("Instruction"));
all.add(rs.getString("Activity"));
String check = rs.getString("Chargable");
if(check.equalsIgnoreCase("1"))
{
all.add("Yes");
}
else
{
all.add("No");
}
all.add(rs.getString("ChargeDescription"));
}
}
catch(SQLException sqle)
{
error ="SQLException: Could not execute the getInvoiceSummary query.";
thrownew SQLException(sqle.toString());
}
catch(Exception e)
{
thrownew Exception(e.toString());
}
finally
{
if(rs !=null)
{
try
{
rs.close();
}
catch(Exception e){}
}
}
return all;
}
I spread the sql query over a number of lines to hopefully make it easier to read. Everything in this piece of code is working fine and the information is being displayed without the group by's. It is only when the group by's are used in the query it falls over.
Your help is greatly appreciated.
