Solved

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

Posted on 2008-06-11
8
153 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now