Solved

Date Selection Problem

Posted on 2003-11-15
8
248 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
report generation frameworks 1 64
bean mapper frameworks 5 60
oracle fusion vs MQ 2 113
how to add new optional parameter to JSP 1 41
SEO can be a real minefield to navigate, but there are three simple ways to up your SEO game just be re-assessing your content output.
Each year, investment in cloud platforms grows more than 20% (https://www.immun.io/hubfs/Immunio_2016/Content/Marketing/Cloud-Security-Report-2016.pdf?submissionGuid=a8d80a00-6fee-4b85-81db-a4e28f681762) as an increasing number of companies begin to…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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