Not able to retrieve records from mySQL table
[nobr]Hi!
I am not able to retrieve records from mySQL table. I want to retrieve records between specific dates. I have two files statement.html and statement.jsp. I am not getting any errors but NO records are shown when page is displayed. Please help, thank you.
statement.html
<html>
<head>
<SCRIPT type="text/javascript">
// Get today's current date.
var now =new Date();
// Array list of days.
var days =new Array('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday');
// Array list of months.
var months =new Array('January','February','March','April','May','June','July','August','September','October','November','December');
// Calculate the number of the current day in the week.
var date = ((now.getDate()<10) ?"0" :"")+ now.getDate();
// Calculate four digit year.
function fourdigits(number){
return (number < 1000) ? number + 1900 : number;
}
// Join it all together
tod = days[now.getDay()] +", " +
months[now.getMonth()] +" " +
date +", " +
(fourdigits(now.getYear())) ;
// Print out the data.
function disableFromTo()
{
if ((document.myform.diwi[0].checked==true && document.myform.cach[0].checked==true) || (document.myform.diwi[1].checked==true && document.myform.cach[2].checked==true) || (document.myform.diwi[0].checked==true && document.myform.cach[2].checked==true))
{
document.myform.fromto.disabled=true;
document.myform.chequeNo.disabled=true;
document.myform.yeno[0].disabled=true;
document.myform.yeno[1].disabled=true;
document.myform.cdate.disabled=true;
}
if ((document.myform.diwi[0].checked==true && document.myform.cach[1].checked==true) || (document.myform.diwi[1].checked==true && document.myform.cach[1].checked==true))
{
document.myform.fromto.disabled=false;
document.myform.chequeNo.disabled=false;
document.myform.yeno[0].disabled=false;
document.myform.yeno[1].disabled=false;
document.myform.cdate.disabled=false;
}
if (document.myform.diwi[1].checked==true && document.myform.cach[0].checked==true)
{
document.myform.fromto.disabled=false;
document.myform.chequeNo.disabled=false;
document.myform.yeno[0].disabled=true;
document.myform.yeno[1].disabled=true;
document.myform.cdate.disabled=true;
}
}
function disableCDate()
{
if (document.myform.yeno[1].checked==true)
{
document.myform.cdate.disabled=true;
}
}
function checkDate(input)
{
var validformat=/^\d{2}\/\d{2}\/\d{4}$///Basic check for format validity
var returnval=false
if(input.value=="")
alert("Please enter a date.")
if (!validformat.test(input.value))
alert("Invalid Date Format. Please correct and submit again.")
else
{
//Detailed check for valid date ranges
var dayfield=input.value.split("/")[0]
var monthfield=input.value.split("/")[1]
var yearfield=input.value.split("/")[2]
var dayobj =new Date(yearfield, monthfield-1, dayfield)
if ((dayobj.getMonth()+1!=monthfield)||(dayobj.getDate()!=dayfield)||(dayobj.getFullYear()!=yearfield))
alert("Invalid Day, Month, or Year range detected. Please correct and submit again.")
else
returnval=true
}
if (returnval==false)
input.select()
return returnval
}
function validateCheque(input)
{
var num=input.value
if (isNaN(num))
{
alert ("Please enter a valid Cheque number.");
returnfalse;
}
if (num.length != 6)
{
alert ("Cheque number must be of 6 digits.");
returnfalse;
}
returntrue;
}
function validateAmount(input)
{
if (isNaN(input.value))
{
alert ("Please enter a valid amount.");
returnfalse;
}
if (parseFloat(input.value) > 9999999.99 || parseFloat(input.value) < 0)
{
alert ("Amount must be greater than 0 and less than 9999999.99.");
returnfalse;
}
returntrue;
}
function startTime()
{
var today=new Date()
var h=today.getHours()
var m=today.getMinutes()
var s=today.getSeconds()
// add a zero in front of numbers<10
if (m <= 9){
m ="0" + m;
}
if (s <= 9){
s ="0" + s;
}
if (h > 12){
h = h - 12;
add =" p.m.";
}else{
h = h;
add =" a.m.";
}
if (h == 12){
add =" p.m.";
}
if (h == 00){
h ="12";
}
document.getElementById('txt').innerHTML="<b><p ALIGN=\"right\">"+tod+""+"<br><b ALIGN=\"right\">"+((h<=9) ?"0" + h : h)+":"+m+":"+s+add+"
</b>"t=setTimeout('startTime()',500)
}
function checkTime(i)
{
if (i<10)
{i="0" + i}
return i
}
</script>
</head>
<body onload="startTime()" bgcolor="#d0d0d0">
<div id="txt" style="font-family:verdana;font-size:70%;font-color:SlateGray"></div>
<center>
<h1 style="font-family:verdana;">Get Statement between the Dates</h1>
<form name="myform" action="statement.jsp" method="post">
<p style="font-family:verdana;font-size:50%;font-color:LightSlateGray">
<table border="1" cellpadding="5">
<tr><td>Date From(DD/MM/YYYY):</td><td><input type="text" name="fromDate" onBlur="checkDate(fromDate)"></td></tr>
<tr><td>Date To(DD/MM/YYYY):</td><td><input type="text" name="toDate" onBlur="checkDate(toDate)"></td></tr>
</table><br>
<input type="Submit" value="Submit">
</center>
</form>
</body>
</html>
statement.jsp
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.text.*" %>
<%
String connectionURL ="jdbc:mysql://localhost:3306/vtek?user=root&password=root";
Connection con=null;
Statement stmt=null;
ResultSet rset=null;
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection(connectionURL,"root","root");
String fromDate = request.getParameter("fromDate");
String toDate = request.getParameter("toDate");
SimpleDateFormat sdf =new SimpleDateFormat("dd/MM/yyyy");
java.sql.Date fDate =new java.sql.Date(sdf.parse(fromDate).getTime());
java.sql.Date tDate =new java.sql.Date(sdf.parse(toDate).getTime());
out.println(fDate);
out.println(tDate);
stmt = con.createStatement ();
[i]rset = stmt.executeQuery ("select edate, particulars, chequeno, fromto, deposit, withdrawal, cleared,cdate, details from passbook" +" WHERE edate BETWEEN " + fDate +" and " + tDate +" order by edate desc");[/i]
out.println("<blockquote><html><body bgcolor=\"CC99FF\"></blockquote>");
out.println("<center>");
out.println("<blockquote><table border=\"1\" cellpadding=\"5\" ></blockquote>");
out.println("<blockquote><tr bgcolor=\"CCCCFF\" style=\"font-family:verdana;font-size:75%;font-weight:bold\"><td>Date</td><td>Particulars</td><td>Cheque No.</td><td> From/To</td><td>Deposit</td><td>Withdrawal</td><td>Cleared</td><td>Cleared Date</td><td>Details</td></tr></blockquote>");
while (rset.next())
{
out.println("<tr bgcolor=\"CCFFFF\" style=\"font-family:verdana;font-size:55%;font-weight:bold\"><td>");
out.println(rset.getDate(1) +"</td><td>" + rset.getString (2) +"</td><td>" + rset.getInt(3) +"</td><td>" + rset.getString (4) +"</td><td>" +rset.getDouble(5) +"</td><td>" + rset.getDouble(6) +"</td><td>" + rset.getInt(7) +"</td><td>" + rset.getDate(8)+"</td><td>" + rset.getString(9));
out.println("</td></tr>");
}
out.println("<blockquote></table></blockquote>");
out.println("</center>");
out.println("<blockquote></body></html></blockquote>");
}//Close try
catch (Exception e){
out.println(e);
}//close catch
finally{
//Clean up activities
try
{
if(rset!=null) rset.close();
if(stmt!=null) stmt.close();
if(con!=null) con.close();
}
catch(Exception e)
{
//Handle the exception
out.println("Finally Exception: " + e);
}
}//Close finally
%>
[/nobr]
