Solved

No matter what I do, can not get the record count.  Help!

Posted on 2008-06-11
8
158 Views
Last Modified: 2010-07-27
I have a query that I have tested and know works correctly.
What I want this code to do, is return the count, which will be "0" or "1"
and if it is "0", then do the re-direct.

The problem I am having, is that the query is finding the result to be "1" (for one record), but the counter says it is "0"
I have racked my head with this for half a day now, trying several ways, but still cannot get the correct results with finding the correct count and outputting the correct result.

Can someone help me with this?


<%@ page import="java.sql.*,javax.sql.*,javax.naming.*"%>
<%@ page errorPage = "errpage.jsp" %>
<%@ page import="java.util.*" %>
<%@ page import="javax.servlet.http.Cookie" %>
<% String db2username=(String)request.getRemoteUser(); %>
<%
try {
javax.sql.DataSource ds;
java.sql.Connection conns = null;
Context ic = new InitialContext();
ds = (javax.sql.DataSource) ic.lookup("java:comp/env/jdbc/mydb2");
conns = ds.getConnection();
conns.setAutoCommit(false);
  java.sql.Statement stats = conns.createStatement();
 
java.sql.ResultSet rs = stats.executeQuery("SELECT COUNT(*) COUNT FROM MYTABLE WHERE GROUPS LIKE '%ICM%' AND ID='"+db2username+"' FOR FETCH ONLY WITH UR");
 
 
Object[] COUNT = null;
int counter = 0;
while(rs.next()) {
  if(COUNT == null) {
 
String redirectURL = "cdspec.jsp?&pre_page=control5";
response.sendRedirect(redirectURL); 
 
%>
<% } else { %>
 FOUND A MATCH.  NOW DO SOMETHING HERE.
<% } %>
 
<% } // close while loop %>
<%
rs.close();
stats.close();
conns.close();
 
} catch (SQLException e) {
    }
 
%>

Open in new window

0
Comment
Question by:g118481
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 92

Expert Comment

by:objects
ID: 21765725
you never set counter;

if (rs.next())
{
   counter = rs.getInt(1);

0
 
LVL 1

Author Comment

by:g118481
ID: 21765766
I do not understand your statement.
Please put it in my code snippet above so I can understand your suggestion, and so I will have a visual of exactly what to change.

Thx
0
 
LVL 92

Expert Comment

by:objects
ID: 21765776
if (rs.next())
{
   counter = rs.getInt(1);
   if (counter==1)
   {
        // found a match
   }
   else
   {
        // no match
   }
}

0
Independent Software Vendors: 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!

 
LVL 1

Author Comment

by:g118481
ID: 21765816
I put your code suggestion into my code snippet below.
Is this what you mean?
<%@ page import="java.sql.*,javax.sql.*,javax.naming.*"%>
<%@ page errorPage = "errpage.jsp" %>
<%@ page import="java.util.*" %>
<%@ page import="javax.servlet.http.Cookie" %>
<% String db2username=(String)request.getRemoteUser(); %>
 
<%--/////////////////////////////////////////////////////////////////////////////////////////////--%>
<%
try {
javax.sql.DataSource ds;
java.sql.Connection conns = null;
Context ic = new InitialContext();
ds = (javax.sql.DataSource) ic.lookup("java:comp/env/jdbc/mydb2");
conns = ds.getConnection();
conns.setAutoCommit(false);
  java.sql.Statement stats = conns.createStatement();
 
java.sql.ResultSet rs = stats.executeQuery("SELECT COUNT(*) COUNT FROM MYTABLE WHERE GROUPS LIKE '%ICM%' AND ID='"+db2username+"' FOR FETCH ONLY WITH UR");
 
if (rs.next())
{
   counter = rs.getInt(1);
   if (counter==1)
   {
 
String redirectURL = "cdspec.jsp?&pre_page=control5";
response.sendRedirect(redirectURL); 
 
} else { %>
 
 FOUND A MATCH.  NOW DO SOMETHING HERE.
 
<% } %>
<% } // close while loop %>
<%
rs.close();
stats.close();
conns.close();
 
} catch (SQLException e) {
    }
 
%>

Open in new window

0
 
LVL 92

Expert Comment

by:objects
ID: 21765827
if count is 1 have you found a match?
the way u have it, u execute "FOUND A MATCH.  NOW DO SOMETHING HERE." if count is *not* equal to 1

When did u want to execute that code?

0
 
LVL 92

Accepted Solution

by:
objects earned 500 total points
ID: 21765843
just read your code again :)

looks like it should be:

if (rs.next())
{
   int counter = rs.getInt(1);
   if (counter==0)
   {
 
String redirectURL = "cdspec.jsp?&pre_page=control5";
response.sendRedirect(redirectURL);
 
} else { %>
 
 FOUND A MATCH.  NOW DO SOMETHING HERE.
 
<% } %>
<% } // close while loop %>
<%

0
 
LVL 1

Author Comment

by:g118481
ID: 21768866
Great!

Your last suggestion works perfect.
I really appreciate your time an efforts!

Thanks
0
 
LVL 1

Author Closing Comment

by:g118481
ID: 31466421
Thank you for the excellent advice!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

726 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