Solved

Date Selection Problem

Posted on 2003-11-15
8
251 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
Technology Partners: 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!

 
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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Retrieving file from bytes array in spring mvc 8 62
own marker interface 1 58
mask rule in struts validation.xml 5 99
jsp insert to database example 2 66
Facebook has became the #1 social media platform. People share many funny videos there, yet you don't know how to download them? Now you can download Videos from Facebook in just 3 simple steps.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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