Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2004-10-19
11
Medium Priority
?
232 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 2000 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

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…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

588 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