data should not repeat in the drop down list when it is loading dynamically from database

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("oracle.jdbc.driver.OracleDriver").newInstance();  
 Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","manager");  
 Statement stmt = con.createStatement();  
 ResultSet rs = stmt.executeQuery("Select * from deptm");
 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>


setEmp.jsp


<%@page import="java.sql.*"%>
<%
String no=request.getParameter("count");  
 String buffer="<select name='emp' ><option value='-1'>Select</option>";  
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();  
 Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","manager");  
 Statement stmt = con.createStatement();  
 ResultSet rs = stmt.executeQuery("Select * from empl where DEPTNO='"+no+"' ");  
   while(rs.next()){
   buffer=buffer+"<option value='"+rs.getString(1)+"'>"+rs.getString("EMPNAME")+"</option>";  
   }  
 buffer=buffer+"</select>";  
 response.getWriter().println(buffer); 
 }
 catch(Exception e){
     System.out.println(e);
 }
 %>

the data tables used for this 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 data is coming from the database but when we give same department name it is repeting i.e if we enter mba for 2 times it is repeating 4 2 times.It should not repeat those many times if once the department is created it should come only once and The name of the persons working in that department should come and when new department is added it should appear. if u there is any wrong in posting my code please forgive me im new to us it and i tried almost to display it right. Please could any one correct this and send me the code. Thank you in advance

View Answers

May 23, 2011 at 12:50 PM

1)selDept.jsp:

 <%@page import="java.sql.*"%>
 <%@page import="java.util.*"%>

 <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");
      Set<String> set=new HashSet<String>();
      while(rs.next()){
          set.add(rs.getString("DEPT_NAME"));
      }
      Iterator iterator = set.iterator();     
      while(iterator.hasNext()){
          String st=iterator.next().toString();
      %>
      <option value="<%=st%>"><%=st%></option>  
      <%
      }
     %>
      </select>  
      <br>  
      <div id='emp'>  
      <select name='emp' >  
      <option value='-1'></option>  
      </select>  
      </div>  
      </body> 
      </html>

2)selEmp.jsp:

<%@page import="java.sql.*"%>
<%
String dname=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_NAME='"+dname+"' ");  
   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 should not repeat in the drop down list when it is loading dynamically from database

Questions by Category