• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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

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
g118481
Asked:
g118481
  • 4
  • 4
1 Solution
 
objectsCommented:
you never set counter;

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

0
 
g118481Author Commented:
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
 
objectsCommented:
if (rs.next())
{
   counter = rs.getInt(1);
   if (counter==1)
   {
        // found a match
   }
   else
   {
        // no match
   }
}

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
g118481Author Commented:
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
 
objectsCommented:
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
 
objectsCommented:
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
 
g118481Author Commented:
Great!

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

Thanks
0
 
g118481Author Commented:
Thank you for the excellent advice!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now