Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DB Count Issue

Posted on 2006-05-08
13
Medium Priority
?
249 Views
Last Modified: 2010-04-01
hi experts,

I have a frame. Top page display search fileds boxes where user can type and search. And Bottom page display search records, by default it shows all records.
In the bottom page I have field called Total Records,which displays no # of records. When its default page my count query display total records, but I'm not getting how to display total records when it is searched. If search result is 5, total count should be 5. How can i make that work ???? Also can anyone tell me how to make firstname, lastname case sensitive free.

StringBuffer sb = new StringBuffer();
sb.append("SELECT USERID, FIRST_NAME, LAST_NAME FROM USERS WHERE ILOCKED='N'");

            if (request.getParameter("search") != null)
            {
            check = request.getParameter("search");
            String fname = request.getParameter("fname");
            String lname = request.getParameter("lname");      
            String any = "%";
                              
            if(fname!=null && !fname.equals(""))
                  {                  
                        sb.append(" AND FIRST_NAME LIKE '"+fname+any+"'");            
                  }
            if(lname!=null && !lname.equals(""))
                  {
                        sb.append(" AND LAST_NAME LIKE '"+lname+any+"'");
                  }                                                
            }
            sb.append(" ORDER BY LAST_NAME");
            
rs = stmt.executeQuery("SELECT COUNT(*) FROM USERS");            
                      if (rs.next())
                      {
                      i = rs.getInt(1);
                      }
                      rs.close();

        <input type="button" value="Total Records: <%=i%>" >
   
   
...
rs = stmt.executeQuery(sb);      
while (rs.next())
{
          //DISPLAY RECORDS IN TABLE FORMAT BELOW
}


THX,
PH
0
Comment
Question by:princehyderabad
  • 6
  • 5
  • 2
13 Comments
 
LVL 11

Accepted Solution

by:
fargo earned 500 total points
ID: 16637569
No need to make two time queries. Use the following and see the comments
 
StringBuffer sb = new StringBuffer();
sb.append("SELECT USERID, FIRST_NAME, LAST_NAME FROM USERS WHERE ILOCKED='N'");

          if (request.getParameter("search") != null)
          {
          check = request.getParameter("search");
          String fname = request.getParameter("fname");
          String lname = request.getParameter("lname");    
          String any = "%";
                         
          if(fname!=null && !fname.equals(""))
               {              
                    sb.append(" AND FIRST_NAME LIKE '"+fname+any+"'");          
               }
          if(lname!=null && !lname.equals(""))
               {
                    sb.append(" AND LAST_NAME LIKE '"+lname+any+"'");
               }                                        
          }
          sb.append(" ORDER BY LAST_NAME");
         
rs = stmt.executeQuery(sb);     // exceute the query with search criteria

rs.last();  // go to the last of result set
int rowCount = rs.getRow(); // get the row count
rs.beforeFirst(); // make it again back to before first
        <input type="button" value="Total Records: <%=rowCount%>" >
   
// iterate the result now
while (rs.next()) {
          //DISPLAY RECORDS IN TABLE FORMAT BELOW
}

0
 

Author Comment

by:princehyderabad
ID: 16641319
Error:
java.sql.SQLException: Invalid operation for forward only resultset : last
0
 

Author Comment

by:princehyderabad
ID: 16641337
By the way I'm using Oracle
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:princehyderabad
ID: 16641411
Okay I added this:
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

and it work for search query. But when there is default (no search query) its showing wrong count. Its showing 88 as total count but actual total count is 153.
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 500 total points
ID: 16641567
what db you are using?

>>how to make firstname, lastname case sensitive free

     rewrite your query as sth like using lower funtion or other function:
   
          sb.append(" AND LOWER(FIRST_NAME) LIKE '"+fname.toLowerCase()+any+"'");    
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16641636
>>rs = stmt.executeQuery("SELECT COUNT(*) FROM USERS");  
      you might need to append all criterias behind it. This query will return all users besides ones you search on.

   
0
 

Author Comment

by:princehyderabad
ID: 16641651
Thanks actonwang. Your codes fine.  I'm using Oracle

Only issue now is display of total count which is showing wrong count, but if its search query ie (if (request.getParameter("search") != null) is true ) total count is showing correct.
0
 

Author Comment

by:princehyderabad
ID: 16641678
actonwang, look at fargo code its working fine as I said, but only when (if (request.getParameter("search") != null) is true ) total count is correct, otherwise not.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16641926
try to issue :

SELECT USERID, FIRST_NAME, LAST_NAME FROM USERS WHERE ILOCKED='N'
/

in your sql*plus directly. does it return 88.

>>StringBuffer sb = new StringBuffer();
>>sb.append("SELECT USERID, FIRST_NAME, LAST_NAME FROM USERS WHERE ILOCKED='N'");

    it will execute this if request.getParameter("search") == null
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 500 total points
ID: 16641932
>> try to issue :

>>SELECT USERID, FIRST_NAME, LAST_NAME FROM USERS WHERE ILOCKED='N'
>>/


   instead, issue:

   select count(*) from users
   /

   select count(*) from users where ilocked = 'N'
   /

   and tell me what you got?

0
 
LVL 11

Expert Comment

by:fargo
ID: 16642129
was away. The last post by actonwang regarding the queries..please issue those query and let us know the count. According to the mentioned code results can't be wrong.
0
 

Author Comment

by:princehyderabad
ID: 16642283
select count(*) from users
   / 153

   select count(*) from users where ilocked = 'N'
   /88

GOOD CATCH ;O)
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16642297
yup :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

LinkedIn blogging is great for networking, building up an audience, and expanding your influence as well. However, if you want to achieve these results, you need to work really hard to make your post worth liking and sharing. Here are 4 tips that ca…
Last month Marc Laliberte, WatchGuard’s Senior Threat Analyst, contributed reviewed the three major email authentication anti-phishing technology standards: SPF, DKIM, and DMARC. Learn more in part 2 of the series originally posted in Cyber Defense …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month20 days, 14 hours left to enroll

810 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