hai friends,
I need your help to finish my project.
i am doing project with
jsp and oracle10g.
here the following files i attached here. please try this and solve my prob.
" in jsp 'insert.jsp' i want to use the two text box which can be fillup by the user from and to date.
after that .. it has to interact with the database . (have several columns). particularly i want to get it..PUNCH_DATE column.
i do know how to fetch the dates with the database by a query..
Note:
there is optional in user giving date..
1. i need month and year drop down box
2. if not possible (drop down box) go for the two text box
here the code:
1."input .html"
2."insert.jsp"
3."empnew.jsp"
4.database description
<html>
<head><title>VALIDATION</title></head>
<script language="javascript">
function check()
{
var s=f.EMPCODE.value;
if(s=="")
alert("enter your dno in textbox");
else
document.f.submit();
}
</script>
<body>
<form action="insert.jsp" name="f">
<table >
<tr><td><div align="center">EMPLOYEE NO</div></td><td><div align="center">
<input name="EMPCODE" type="text" maxlength="8">
</div></td></tr>
<tr>
<td>
<div align="center">
<input type="button" value="Submit" onClick="check()">
</div></td>
<td>
<div align="left">
<input name="" type="reset" value="Reset">
</div></td></tr>
</table>
</body>
</form>
</html>
2.*********
<%@ page contentType="text/html" language="
java" import="java.sql.*" errorPage="" %>
<html>
<head>
<title>EMP DATABASE</title>
<script type="text/javascript" src="calendarDateInput.js">
function send()
{
var from,to;
document.f.submit();
}
</script>
<style type="text/css">
<!--
.style1 {color: #FF00FF}
-->
</style>
</head>
<body>
<form name="f" method="post" action="empnew.jsp">
<%
//for ORACLE
String url="
jdbc:oracle:thin:@localhost:1521:XE";
String no=request.getParameter("EMPCODE");
//String date=request.getParameter("PUNCH_DATE");
//String query="Select * from act_stipend where EMPCODE="+no+" && "+date+" ";
//for access
String query="Select EMPNAME,SHIFT1 from bhel where EMPCODE="+no;
String ename="",ed="";
try{
//for oracle
//String url="jdbc:oracle:thin:@localhost:1521:XE";
Class.forName("oracle.jdbc.driver.OracleDriver");
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//for oracle
Connection con =DriverManager.getConnection(url,"system","gana");
//for access
//Connection con =DriverManager.getConnection("jdbc:odbc:NovemberAPA");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(query);
if(rs.next())
{
ename=rs.getString(1);
ed=rs.getString(2);
}
rs.close();
}
catch(SQLException e)
{
out.println(e.getMessage());
}
catch(ClassNotFoundException r)
{
out.println(r.getMessage());
}
%>
<table border="1" >
<tr >
<td ><div align="center">
<h2><strong>APPERENDICE ATTENDANCE </strong></h2>
</div></td>
</tr>
<tr>
<td ><strong>EMPLOYEE CODE</strong></td>
<td ><div align="center">
<input type="text" name="empno" value="<%=no%>">
</div></td>
</tr>
<tr>
<td><strong>EMPLOYEE NAME </strong></td>
<td><label>
<div align="center">
<input type="text" name="empname" value="<%=ename%>">
</div>
</label></td>
</tr>
<tr>
<td><strong>EMPLOYEE DEPT </strong></td>
<td><label>
<div align="center">
<input type="text" name="empdno" value="<%=ed%>">
</div>
</label></td>
</tr>
<tr>
<td><strong>PUNCH DATE </strong></td>
<td><div align="center"><strong>
<label></label>
<label></label>
MM </strong>
<select name="mmm">
<option value="01">JAN</option>
<option value="02">FEB</option>
<option value="03">MAR</option>
<option value="04">APR</option>
<option value="05">MAY</option>
<option value="06">JUN</option>
<option value="07">JUL</option>
<option value="08">AUG</option>
<option value="09">SEP</option>
<option value="10">OCT</option>
<option value="11">NOV</option>
<option value="12">DEC</option>
</select>
<strong> 20 YY</strong>
<select name="yy">
<option value="09">09</option>
<option value="10">10</option>
</select>
</div></td>
</tr>
<tr>
<td><strong>ENTER FROM DATE (<span class="style1">DD/MMM/YY</span>) </strong></td>
<td><div align="center"><strong>
<input type="text" name="datfrm">
</strong></div></td>
</tr>
<tr>
<td><strong>ENTER TODATE (<span class="style1">DD/MMM/YY</span>)</strong></td>
<td><div align="center"><strong>
<input type="text" name="datfrm2">
</strong></div></td>
</tr>
<tr>
<td> <div align="right">
<input type="submit" name="Submit" value="Submit" onClick="send()" >
</div></td>
<td><div align="center">
<input type="reset" name="Submit2" value="Reset">
</div></td>
</tr>
</table>
</form>
</body>
</html>
3.*********
<html>
<head><title>EMPLOYEE ATTENDANCE</title></head>
<script language="javascript" >
function update1(val)
{
document.f.cr.value=val
//alert(document.f.cr.value);
document.f.submit()
}
</script>
<body bgcolor="#CCCCCC">
<form name="f" action="updateemp.jsp" >
<input type="hidden" name="cr" value="">
<%@page language="java" %>
<%@page import="java.sql.*,mypac.*,java.util.*,java.text.*" %>
<%
//for ORACLE
String no=request.getParameter("empno");
//String d1=request.getParameter("dd1");
//String dat=request.getParameter("orderdate");
String m1=request.getParameter("mmm");
String y1=request.getParameter("yy");
String dt1=request.getParameter("datfrm");
String dt2=request.getParameter("datfrm2");
//String inputdate=d1+"-"+m1+"-"+y1;
//String date=request.getParameter("PUNCH_DATE");
//String query="Select * from act_stipend where EMPCODE="+no+" && "+date+" ";
//for access
MyUtil muobj=new MyUtil();
/*
SimpleDateFormat df=new SimpleDateFormat("dd-MM-yy");
java.util.Date t;
out.println(inputdate);
try {
t = df.parse(inputdate);
out.println(t);
} catch (ParseException e) {
out.println("unparseable using " + df);
}*/
try{
//for oracle
//String query="select * from bhel where empcode='"+no+"' and PUNCH_DATE between '01-"+dt+"' and '31-"+dt2+"'";
//String query="select * from act_stipend where EMPCODE="+no+" and MYDATE="+to_date('d1','dd/mm/yy')+"";
//String query="select * from bhel where empcode='"+no+"' and PUNCH_DATE='"+dt1+"'";
//String query ="SELECT * FROM bhel WHERE EMPCODE='"+no+"' and PUNCH_DATE BETWEEN '"+to_char(' "+dt1+" ', 'dd/mmm/yy')+"' AND '"+to_char (' " +dt2+" ', 'dd/mmm/yy')+"' ";
String query ="select * from bhel where empcode=" "'"+no+"'" and punch_date +" between to_date(' "+dt1+" ' ,'dd/mmm/yy ') and to_date(' " +dt2+ " ' , 'dd/mmm/yy ')";
String url="jdbc:oracle:thin:@localhost:1521:XE";
Class.forName("oracle.jdbc.driver.OracleDriver");
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//for oracle
Connection con =DriverManager.getConnection(url,"system","gana");
//for access
//Connection con =DriverManager.getConnection("jdbc:odbc:NovemberAPA");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(query);
%>
<table border="1" >
<tr>
<th>
EMPCODE </th>
<th>
EMPNAME</th>
<th>
PUNCH_DATE</th>
<th>
SHIFT1</th>
<th>
IN_PUNCH1 </th>
<th>
OUT_PUNCH1 </th>
<th>
SHIFT2</th>
<th>
IN_PUNCH2 </th>
<th>
OUT_PUNCH2 </th>
<th>
ABS_HRS </th>
<th>
SHORT_ABS </th>
<th>
HA </th>
<th>
HA1 </th>
<th>
COFF</th>
<th>
ABSENT_HRS</th>
<th>
MODIFICATION</th>
</tr>
<%
int i=0;
while(rs.next())
{
i++;
out.println("<tr>");
out.println("<td><input type=text value='"+rs.getString(1)+"' name='t"+i+"1' readonly></td>");
out.println("<td><input type=text value='"+rs.getString(2)+"' name='t"+i+"2' readonly></td>");
out.println("<td><input type=text value='"+rs.getDate(3)+"' name='t"+i+"3' readonly></td>");
out.println("<td><input type=text value='"+rs.getString(4)+"' name='t"+i+"4' readonly></td>");
out.println("<td><input type=text value='"+rs.getString(5)+"' name='t"+i+"5' ></td>");
out.println("<td><input type=text value='"+rs.getString(6)+"' name='t"+i+"6' ></td>");
out.println("<td><input type=text value='"+rs.getString(7)+"' name='t"+i+"7' ></td>");
out.println("<td><input type=text value='"+rs.getString(8)+"' name='t"+i+"8' ></td>");
out.println("<td><input type=text value='"+rs.getString(9)+"' name='t"+i+"9' ></td>");
out.println("<td><input type=text value='"+rs.getString(10)+"' name='t"+i+"10' ></td>");
out.println("<td><input type=text value='"+rs.getString(11)+"' name='t"+i+"11' ></td>");
out.println("<td><input type=text value='"+rs.getString(12)+"' name='t"+i+"12' ></td>");
out.println("<td><input type=text value='"+rs.getString(13)+"' name='t"+i+"13' ></td>");
out.println("<td><input type=text value='"+rs.getDate(14)+"' name='t"+i+"14' readonly></td>");
out.println("<td><input type=text value='"+rs.getDate(15)+"' name='t"+i+"15' readonly></td>");
out.println("<td><INPUT TYPE=BUTTON VALUE=UPDATE onclick=update1('"+i+"') /></td>");
out.println("</tr>");
}
rs.close();
}
catch(SQLException e)
{
out.println(e.getMessage());
}
catch(ClassNotFoundException r)
{
out.println("ClassNotFound");
}
%>
</table>
</form>
</body>
</html>
****************
SQL> desc act_stipend
Name Null? Type
------------------------------- -------- ----
EMPCODE NOT NULL VARCHAR2(7)
EMPNAME VARCHAR2(30)
PUNCH_DATE NOT NULL DATE
SHIFT1 VARCHAR2(2)
IN_PUNCH1 VARCHAR2(5)
OUT_PUNCH1 VARCHAR2(5)
SHIFT2 VARCHAR2(1)
IN_PUNCH2 VARCHAR2(5)
OUT_PUNCH2 VARCHAR2(5)
ABS_HRS VARCHAR2(5)
SHORT_ABS VARCHAR2(5)
HA1 VARCHAR2(2)
HA2 VARCHAR2(2)
COFF DATE
create table bhel(
EMPCODE VARCHAR2(7) NOT NULL,
EMPNAME VARCHAR2(30),
PUNCH_DATE DATE NOT NULL ,
SHIFT1 VARCHAR2(2),
IN_PUNCH1 VARCHAR2(5),
OUT_PUNCH1 VARCHAR2(5),
SHIFT2 VARCHAR2(2),
IN_PUNCH2 VARCHAR2(5),
OUT_PUNCH2 VARCHAR2(5),
ABS_HRS VARCHAR2(5),
SHORT_ABS VARCHAR2(5),
HA1 VARCHAR2(2),
HA2 VARCHAR2(2),
COFF DATE
Absent_hrs number(3));
insert into bhel (EMPNAME,PUNCH_DATE,SHIFT1,IN_PUNCH1,OUT_PUNCH1,SHIFT2,IN_PUNCH2,OUT_PUNCH2,ABS_HRS,SHORT_ABS,HA1,HA2values('&EMPNAME','&PUNCH_DATE','&SHIFT1','&IN_PUNCH1','&OUT_PUNCH1','&SHIFT2','&IN_PUNCH2','&OUT_PUNCH2','&ABS_HRS','&SHORT_ABS','&HA1','&HA2','&COFF');
insert into bhel values(
7010002,
'NARAYANAN',
'10-JAN-10',
'GS',
'',
'',
'GS',
'',
'',
'',
'ML',
'AB',
'AB',
''
);
please try and tell me...
query is not working...