selDept.jsp
<%@page import="java.sql.*"%>
<html>
<head>
<script language="javascript" type="text/javascript">
var xmlHttp
var xmlHttp
function showEmp(str){
if (typeof XMLHttpRequest != "undefined"){
xmlHttp= new XMLHttpRequest();
}
else if (window.ActiveXObject){
xmlHttp= new ActiveXObject("Microsoft.XMLHTTP");
}
if (xmlHttp==null){
alert("Browser does not support XMLHTTP Request")
return;
}
var url="selEmp.jsp";
url +="?count=" +str;
xmlHttp.onreadystatechange = stateChange;
xmlHttp.open("GET", url, true);
xmlHttp.send(null);
}
function stateChange(){
if(xmlHttp.readyState==4 || xmlHttp.readyState=="complete"){
document.getElementById("emp").innerHTML=xmlHttp.responseText
}
}
</script>
</head>
<body>
<select name='dept' onchange="showEmp(this.value)">
<option value="none">Select</option>
<%
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select * from dept");
while(rs.next()){
%>
<option value="<%=rs.getString("DEPT_NO")%>"><%=rs.getString("DEPT_NAME")%></option>
<%
}
%>
</select>
<br>
<div id='emp'>
<select name='emp' >
<option value='-1'></option>
</select>
</div>
</body>
</html>
selEmp.jsp
<%@page import="java.sql.*"%>
<%
String no=request.getParameter("count");
String buffer="<select name='emp' ><option value='-1'>Select</option>";
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select * from emp where DEPT_NO='"+no+"' ");
while(rs.next()){
buffer=buffer+"<option value='"+rs.getString(1)+"'>"+rs.getString("EMP_NAME")+"</option>";
}
buffer=buffer+"</select>";
response.getWriter().println(buffer);
}
catch(Exception e){
System.out.println(e);
}
%>
Data base tables are
dept
CREATE TABLE `dept` (
`DEPT_NO` int(100) default NULL,
`DEPT_NAME` varchar(255) default NULL
);
emp
CREATE TABLE `emp` (
`EMP_NO` int(10) NOT NULL auto_increment,
`EMP_NAME` varchar(100) default NULL,
`DESIGNATION` varchar(100) default NULL,
`JOINING_DATE` date default NULL,
`SALARY` int(100) default NULL,
`DEPT_NO` int(100) default NULL,
`DEPT_NAME` varchar(100) default NULL,
PRIMARY KEY (`EMP_NO`)
);
The above code is working for getting the data dynamically for database but my problem is in the list the same department names are repeatedly adding when ever we insert the same same name (i.e when ever in the registration i give for 4 employees same department name then that name appears for 4 times )actually it should not happen.When once that name is inserted another time it should not come only once it has to come and the persons who are working in that department has to come.
I hope you understand my question. if the posting of the question is not proper please forgive me.I rather tried to place it right. Please help me out and send the code as soon as possible. Thank you in advance