Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Date Selection Problem

In the database, the 'trans_date' field(datatype is Date/Time) contains the following dates for the 'account_no' 3....

11/15/03
11/15/03
11/15/03
11/16/03
11/17/03
11/18/03

if I enter 11/15/03 as start date, and 11/20/03 as end date.... the program doesn't go to the next page but redirects to Transaction.jsp with an error showing "End Date is incorrect".... I'm sorry for posting the code once again..... JNic thanx for ur suggestions but what should I do if I can't get it then

//TransAct.jsp
<%
  String sdate = request.getParameter("month") + "/" + request.getParameter("day") + "/" + request.getParameter("year");
  String edate = request.getParameter("month1") + "/" + request.getParameter("day1") + "/" + request.getParameter("year1");

  out.println(sdate + "<br>" + edate);
  PreparedStatement ps = con.prepareStatement("select * from transaction1 where account_no = " + request.getParameter("acc_no") + " and trans_date >= " + sdate + "");

  PreparedStatement ps1 = con.prepareStatement("select * from transaction1 where account_no = " + request.getParameter("acc_no") + " and trans_date <= " + edate + "");

  PreparedStatement ps2 = con.prepareStatement("select * from account where account_no = " + request.getParameter("acc_no") + " and loginid = '" + session.getAttribute("userID") + "'");

  PreparedStatement ps3 = con.prepareStatement("select * from transaction1 where account_no = " + request.getParameter("acc_no") + " and trans_date >= " + sdate + " and trans_date <= " + edate + "");

  ResultSet rs = ps.executeQuery();
  if(rs.next())
  {
    ResultSet rs1 = ps1.executeQuery();
    if(rs1.next())
    {
      ResultSet rs2 = ps2.executeQuery();
      if(rs2.next())
      {
        ResultSet rs3 = ps3.executeQuery();

        if(rs3.next())
        {
          out.println("<tr bgcolor=whitesmoke>");
          out.println("<td align=right>" + rs3.getInt("transaction_id") + "</td>");
          out.println("<td>" + rs3.getDate("trans_date") + "</td>");
          out.println("<td align=right>" + rs3.getInt("debit") + "</td>");
          out.println("<td align=right>" + rs3.getInt("credit") + "</td>");
          out.println("<td align=right>" + rs3.getInt("transaction_account_no") + "</td>");
          out.println("</tr>");
        }
      }else{
        response.sendRedirect("Transaction.jsp?login=bad&month=" + request.getParameter("month") + "&day=" + request.getParameter("day") + "&year=" + request.getParameter("year") + "&month1=" + request.getParameter("month1") + "&day1=" + request.getParameter("day1") + "&year1=" + request.getParameter("year1") + "&acc_no=" + request.getParameter("acc_no") + "");
      }
    }else{
      response.sendRedirect("Transaction.jsp?edate=bad&month=" + request.getParameter("month") + "&day=" + request.getParameter("day") + "&year=" + request.getParameter("year") + "&month1=" + request.getParameter("month1") + "&day1=" + request.getParameter("day1") + "&year1=" + request.getParameter("year1") + "&acc_no=" + request.getParameter("acc_no") + "");
    }
  }else{
    response.sendRedirect("Transaction.jsp?sdate=bad&month=" + request.getParameter("month") + "&day=" + request.getParameter("day") + "&year=" + request.getParameter("year") + "&month1=" + request.getParameter("month1") + "&day1=" + request.getParameter("day1") + "&year1=" + request.getParameter("year1") + "&acc_no=" + request.getParameter("acc_no") + "");
  }
%>

//Here is the error catching code in Transaction.jsp
<%
  String err = request.getParameter("login");
  String err1 = request.getParameter("edate");
  String err2 = request.getParameter("sdate");
  if( err != null )
  {
    out.println("<tr><td align=middle colspan=2><b><font color=red size=3>Account # doesn't exist</font></b></td></tr>");
  }else if( err1 != null )
  {
    out.println("<tr><td align=middle colspan=2><b><font color=red size=3>End Date is incorrect</font></b></td></tr>");
   }else if( err2 != null )
  {
    out.println("<tr><td align=middle colspan=2><b><font color=red size=3>Start Date is incorrect</font></b></td></tr>");
  }
%>

0
adnan_rais
Asked:
adnan_rais
  • 3
  • 2
1 Solution
 
adnan_raisAuthor Commented:
if I enter a wrong account no, then "Start Date is incorrect" error is displayed although account_no error must b shown

0
 
jimmackCommented:
The error is being returned because no records are being found that match your query (ps1).

Are you sure the format of your end date is correct?

Since you pass all the parameters through to Transaction.jsp anyway, you should display the dates in addition to the error message.
0
 
JNicCommented:
Look at your first preparedStatement:

PreparedStatement ps = con.prepareStatement("select * from transaction1 where account_no = " + request.getParameter("acc_no") + " and trans_date >= " + sdate + "");

You test it like this (I cut out all in the middle):

ResultSet rs = ps.executeQuery();
  if(rs.next())
  {
    ..... // all your code
 }  
else{
    response.sendRedirect("Transaction.jsp?sdate=bad&month=" + request.getParameter("month") + "&day=" + request.getParameter("day") + "&year=" + request.getParameter("year") + "&month1=" + request.getParameter("month1") + "&day1=" + request.getParameter("day1") + "&year1=" + request.getParameter("year1") + "&acc_no=" + request.getParameter("acc_no") + "");
  }

This redirect will happen if you get no results comparing with the entered date OR if you enter a wrong account number. Therefore you get an invalid date error when you enter a bad account_no.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JNicCommented:
What you could do is to test for a wrong account no before all the other tests:

Make a very first statement:
"select * from transaction1 where acc_no="+request.getParameter("acc_no")
And then do a test, - if this returns no results, you should make the program return a bad_acc error.

0
 
adnan_raisAuthor Commented:
Now note down the main problem plz

The 'trans_date' field in the Access database is fixed to Date/Time. I take 11/11/2003(mm/dd/yy) as input and this date exists in the database. But still the program jumps to the 'else' condition, i.e.

<%
   String sdate = request.getParameter("month") + "/" + request.getParameter("day") + "/" + request.getParameter("year");

PreparedStatement ps = con.prepareStatement("select * from transaction where account_no = " + request.getParameter("acc_no") + " and trans_date = " + sdate + "");

ResultSet rs = ps.executeQuery();
if(rs.next()){
   out.println("Hello");
}else{
   out.println("Bye");
}

//   The program jumps to else condition even if the input provided is correct or not.
%>

What's the solution for this problem?
0
 
JNicCommented:
The format of an SQL-date is normally: yyyy-mm-dd.

Are you sure this is not the case in your db? If I am right, you should change:

 String sdate = request.getParameter("month") + "/" + request.getParameter("day") + "/" + request.getParameter("year");

To

String sdate = request.getParameter("year")+"-"+request.getParameter("month")+"-"+request.getParameter("day");

Otherwise, I will suggest you try to do a sample print of the String. Then we can see if it look strange. So:

   String sdate = request.getParameter("month") + "/" + request.getParameter("day") + "/" + request.getParameter("year");

System.out.println(sdate); // Print the string

PreparedStatement ps = con.prepareStatement("select * from transaction where account_no = " + request.getParameter("acc_no") + " and trans_date = " + sdate + "");

ResultSet rs = ps.executeQuery();
if(rs.next()){
   out.println("Hello");
}else{
   out.println("Bye");
}


0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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