Solved

Date Selection Problem

Posted on 2003-11-15
8
244 Views
Last Modified: 2010-04-01
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
Comment
Question by:adnan_rais
  • 3
  • 2
8 Comments
 

Author Comment

by:adnan_rais
ID: 9756989
if I enter a wrong account no, then "Start Date is incorrect" error is displayed although account_no error must b shown

0
 
LVL 15

Expert Comment

by:jimmack
ID: 9757086
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
 
LVL 1

Expert Comment

by:JNic
ID: 9757097
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:JNic
ID: 9757124
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
 

Author Comment

by:adnan_rais
ID: 9812661
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
 
LVL 1

Accepted Solution

by:
JNic earned 20 total points
ID: 9813499
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A procedure for exporting installed hotfix details of remote computers using powershell
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now