Solved

Date Selection Problem

Posted on 2003-11-15
8
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

First of all let me say that the only language that I speak is English, but in answering questions here I often come across people whose English skills are not the best and I’d like to be able to communicate better with them, and the following descr…
Let's recap what we learned from yesterday's Skyport Systems webinar.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

628 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