Solved

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

Posted on 2004-10-19
11
223 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
  • 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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
topping2 challenge 13 91
HSSFWorkbook cannot be resolved error 10 70
eclipse apache tomcat admin console 52 97
hibernate example using maven 12 42
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
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…
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 the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

777 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