Solved

SQL Query only returning rows after first row. If only one row then nothing returns.

Posted on 2004-10-19
11
227 Views
Last Modified: 2010-03-31
The code below should query the database and returns all records where SSN = the passed SSN. The problem is that it is only returning the rows after the first row found. If there is only one record in the database then nothing returns, if there are three records matching the SSN then only the last two are returned, etc, etc. If I remove the WHERE clause then all data is returned. What am I doing wrong here? SSN is stored as a varchar in a SQL 2K database.

RCMB

public class RiderReport extends HttpServlet {
      

      public String removeNull(Object value) {
            String ret = null;
                  ret = (value == null)? " " : value.toString();
            return ret;
      }

      public void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
              doGet(request, response);
      }

  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
            String SSN = request.getParameter("SSN");
            String sql = "Select Rank_Rate AS [Rate], LName AS [Last Name], FName AS [First Name], arrive_time AS [Arrival], depart_time AS [Departure] FROM pers_riders WHERE SSN = '" + SSN + "'";
            Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
       
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

                  out.println("<html><head><title>Personnel Database Report</title></head><body>");
                  out.println("<center>");
                  out.println("<script src=../scripts/xaramenu.js></script>");
                  out.println("<script menumaker src=../scripts/clearlake7.js></script>");
                  out.println("</center>");
                  out.println("<p><center><h2>RIDER HISTORY REPORT</h2></center></p>");
                  out.println("<table align=center border=1 width=100%><tr>");
       
        try{
           
                  conn = ConnectionManager.getConnection();
               
               //Create a statement for executing some SQL

               stmt = conn.createStatement();

            rs = stmt.executeQuery(sql);
                  
            rsm = rs.getMetaData();
                  
                  int colCount =  rsm.getColumnCount();
           
                  //print column names
                  for (int i = 1; i <=colCount; ++i){
               
                        out.println("<th width=20%>" + rsm.getColumnName(i) + "</th>");
                  }
           
                  out.println("</tr>");
         
                  while( rs.next()){
               
                         out.println("<tr>");
               
                        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                        SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                        sdf1.setLenient(false);
                        while (rs.next()) {
                               for (int i = 1;  i <=colCount; ++i) {
                                      String value = removeNull(rs.getString(i));
                                      try {
                                             Date d = sdf1.parse(value);
                                             value = sdf2.format(d);
                                      }
                                      catch(Exception e) {
                                             /* ignore - if its not a Date */
                                      }
                                      out.println("<td>" + value + "</td>");
                               }
                              out.println("</tr>");
                        }
                  }
                        
            } catch (Exception e){
           
                  throw new ServletException(e.getMessage());
                  
            } finally {
           
                  try{
               
                        rs.close();
                        conn.close();
                        
                  } catch (SQLException sqle){ }
           
           
            }
     
            out.println("</table><br><br>");

            out.println("</body>");
            out.println("</html>");
   
            out.close();

       } //doGet

}
0
Comment
Question by:rcmb
[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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 8

Accepted Solution

by:
kiranhk earned 500 total points
ID: 12349753
the problem is that u r calling rs.next() 2 times so it is moving to the second row fetched.
you can probably take out the first rs.next()

               
                     out.println("<tr>");
               
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {


hope this helps
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12349757
you have 2 rs.next() calls, one outside the SimpleDateFormat setup, and then another inside - the first call is only being used to check if processing should be done, but it is consuming a row...

restructure the code so that there is only one call to ResultSet.next() on each loop iteration...
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12349771
 while( rs.next()){
               
                     out.println("<tr>");
               
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {

can probably become:

                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {
                         out.println("<tr>");
                
0
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.

 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349781
This is a problem

while( rs.next()){
               
                     out.println("<tr>");
               
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349788
cjjclifford ,

you beat me. while reading other threads

you gave the answer
0
 
LVL 8

Expert Comment

by:kiranhk
ID: 12349797
       Just try with this code.
Take out the outer while loop after your print column names code and use the one below

   
                 out.println("<tr>");
           
                SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                sdf1.setLenient(false);
                while (rs.next())
                {
                      for (int i = 1;  i <=colCount; ++i)
                      {
                            String value = removeNull(rs.getString(i));
                            try
                            {
                                  Date d = sdf1.parse(value);
                                  value = sdf2.format(d);
                            }
                            catch(Exception e)
                            {
                                  /* ignore - if its not a Date */
                            }
                            out.println("<td>" + value + "</td>");
                      }
                     out.println("</tr>");
                     out.println("<tr>");
                }
                out.println("</tr>");
       
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349811
Even kiranhk  also beat me.
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349832

               
                     out.println("<tr>");
               
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {
                          for (int i = 1;  i <=colCount; ++i) {
                                String value = removeNull(rs.getString(i));
                                try {
                                      Date d = sdf1.parse(value);
                                      value = sdf2.format(d);
                                }
                                catch(Exception e) {
                                      /* ignore - if its not a Date */
                                }
                                out.println("<td>" + value + "</td>");
                          }
                         out.println("</tr>");
                    }
               }
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12349894
It might be a little more portable(*) if you use the MetaData to decide of the column contains a date, and just getTimestamp() for this column, rather than getString, and use a hardcoded date format? There's also slightly less work this way (no conversion from String -> Date in Java...)

(*) when I say portable, I'm guessing that while the (default?) date format is probably pretty standard, its concievable that this might be different on some databases...
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349922
While reading the comments,
I think
kiranhk and   cjjclifford  both deserves the points

Regards
Sudhakar
0
 
LVL 12

Author Comment

by:rcmb
ID: 12349928
I must say I really appreciate the quick responses. Cudos to kiranhk for being the first to respond. All answers were basically the same so I awarded to the first responder.

Thanks again,
RCMB
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This video teaches viewers about errors in exception handling.
Suggested Courses

623 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