?
Solved

How to Find out total no of Entries(COUNT(*)) from a recordset in JSP

Posted on 2006-05-16
5
Medium Priority
?
366 Views
Last Modified: 2008-02-01
Hi,

 Im working on a Jsp application and trying to find access Sql Server Database.
My Problem is that im trying to execute a query and as a result needs to find out the total no of entries
from the recordset. My syntax is as follows:

<%  

String sSQL1 =" SELECT Count(*)";

sSQL1 =sSQL1 + "FROM CKMS_Admin.[Incident Header - Closed] INNER JOIN CKMS_Admin.[Incident Footer - Closed] ON CKMS_Admin.[Incident Header - Closed].Id = CKMS_Admin.[Incident Footer - Closed].Id WHERE (";

sSQL1 =sSQL1+ "DatePart(month,CKMS_Admin.[Incident Header - Closed].[Close Date]) ="+ Mont;

sSQL1 =sSQL1 + ") AND (";

sSQL1 =sSQL1 + "DatePart(year,CKMS_Admin.[Incident Header - Closed].[Close Date]) ="+ Yr;

sSQL1 =sSQL1 + ") AND (";

sSQL1 =sSQL1 +"CKMS_Admin.[Incident Header - Closed].Impact = 'CRITICAL'";

sSQL1 =sSQL1 + ") AND (";

sSQL1 =sSQL1 +"CKMS_Admin.[Incident Header - Closed].Service IN ('GNS.AFR', 'GNS.XAFR', 'ANS.COST', 'ANS.COST_NONCRIT', 'ENS.COST', 'ENS.COST_NONCRIT', 'LNS.COST', 'LNS.COST_NONCRIT', 'NNS.COST', 'NNS.COST_NONCRIT'";
                     
sSQL1 =sSQL1 + ")) AND (";

sSQL1 =sSQL1 + "CKMS_Admin.[Incident Footer - Closed].[RowByDesc]="+ quote + Row + quote ;

sSQL1 =sSQL1 + ")";


 
 
  %>    



                                   
<%
      
        ResultSet Rs = oConn.executeQuery(sSQL1);
            int count=0;
              while (Rs.next())
                      {

                        count+=1;

                         out.println("the count: " + count);

                        }


%>



Somehow the query isnt working and i dont get the correct count. Its always either 0 or 1.Dont know whats the problem
However whenever i run the same query in Sql Server i get the right results but it doesnt work with the JSP application.
Please help with this asap as its ver important.Willing to give full 500 points to the person who answers it.
0
Comment
Question by:ammartahir1978
  • 3
  • 2
5 Comments
 
LVL 14

Accepted Solution

by:
boonleng earned 1500 total points
ID: 16690938
You are using the sql "SELECT Count(*) ...", this will only return 1 row.
so you dont need to loop the resultset, just directly print the 1st row.
<%
     
        ResultSet Rs = oConn.executeQuery(sSQL1);
        If (Rs.next()) {
                out.println("the count: " + Rs.getInt(0));
        }
        Rs.close();
%>
0
 
LVL 14

Expert Comment

by:boonleng
ID: 16690984
Sorry, should be Rs.getInt(1)
0
 

Author Comment

by:ammartahir1978
ID: 16691011
Tried using your code but i get the following exception

Exception thrown on line '597' from page 'G:\\data\\jrun\\default8\\app10\\rows_scorecard.jsp'.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Column index 0 is out of range.

Also what is Rs.getInt(0)) doing here. I mean what does it gets.
Im new into jsp so dont know that much
0
 

Author Comment

by:ammartahir1978
ID: 16691025
Ok i have tried using 1 as well but dont get the right result
0
 

Author Comment

by:ammartahir1978
ID: 16691071
<%
      
       
             
            ResultSet Rs = oConn.executeQuery(sSQL1);
                 
                 
                  if(Rs.next())
                  {
                     
                     out.println("the count: " + Rs.getInt(1));
                       
                }

           

             
             Rs.close();
                      


%>


Tried using this but the output i get is either 0 or 1 nothing else.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question