dynamic retrival of data from mysql database to dropdownlist in jsp

Hello, Am having problem in my project... i want to retrive the data from mysql database to the dropdownlist in jsp. Example: 1st i want to display the list of dept from the database to the dropdown list... if i select the particular dept say CSE from the dropdown, then the corresponding details of CSE should be retrived from the database and display in the textbox as default value... pls its urgent.. help me frnds..

View Answers

August 11, 2012 at 5:09 PM

Here is an example that retrieve the dept name from the mysql database to the dropdownlist.if the user select the particular dept from the dropdown, then the corresponding details of the department should be retrieved from the database and display in the textboxes.

1)selectname.jsp:

<%@page import="java.sql.*"%>
<html>
<head>
<script type="text/javascript">
function showEmp(name){ 
 if(document.getElementById("address").value!="-1"){
 xmlHttp=GetXmlHttpObject()
if (xmlHttp==null){
 alert ("Browser does not support HTTP Request")
 return
 }
var url="getvalue.jsp"
url=url+"?name="+name
xmlHttp.onreadystatechange=stateChanged 
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}
else{
 alert("Please Select Employee Id");
}
}
function stateChanged(){ 
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete") { 
var showdata = xmlHttp.responseText; 
var strar = showdata.split(":");
document.getElementById("no").value= strar[1];
document.getElementById("manager").value= strar[2];
 }
 } 
}

function GetXmlHttpObject(){
var xmlHttp=null;
try{
 xmlHttp=new XMLHttpRequest();
 }
catch (e) {
 try {
  xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
  }
 catch (e)  {
  xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
 }
return xmlHttp;
}
</script>
</head>
<body>
<form name="dept">
<br><br>
<table border="0" width="400px" align="center" bgcolor="#CDFFFF">
<div id="mydiv"></div>
   <tr><td><b>Select Dept</b></td><td> 
   <select name="name" onchange="showEmp(this.value);">
   <option value="-1">Select</option> 
   <%
   Class.forName("com.mysql.jdbc.Driver").newInstance();

Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test","root","root");
String query = "select * from dept";

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while(rs.next())
{
%>
<option value="<%=rs.getString("deptname")%>"><%=rs.getString("deptname")%></option>
<%
}
%>
 </select>
</td></tr>
<tr><td ><b>No Of Employees:</b></td><td>
<input  type="text" name="no" id="no" value=""></td></tr>
<tr><td><b>Manager:</b></td><td>
<input  type="text" name="manager" id="manager" value=""></td></tr>

</table>
</form>    
<table border="0" width="100%" align="center">
<br>
<br>
</table>
</body>
</html>

August 11, 2012 at 5:10 PM

continue....

2)getvalue.jsp:

<%@page import="java.sql.*"%>
<%
String dname = request.getParameter("name").toString();
String data="";

int sumcount=0; 
Statement st;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();

Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test","root","root");
String query = "select * from dept where deptname='"+dname+"'";

st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while(rs.next())
{
data = ":" + rs.getString("noOfEmployees") +":"+  rs.getString("manager");
}
out.println(data);
}
catch (Exception e) {
e.printStackTrace();
}
%>

3)For the above code, we have created following table:

CREATE TABLE `dept` (                                 
           `deptid` bigint(255) NOT NULL auto_increment,       
           `deptname` varchar(255) default NULL,   
           `noOfEmployees` int(255) default NULL, 
           `manager` varchar(255) default NULL,
           PRIMARY KEY  (`deptid`));

August 16, 2012 at 12:24 PM

Thank u frnd... but dis coding is not working... if i choose the deptname from the dropdown, am not able to get the values in the textbox.. could u pls help me for that....

August 16, 2012 at 1:23 PM

Thank u.. i got the output..

January 23, 2013 at 1:41 AM

This part ("<option value="<%=rs.getString("deptname")%>">");

gives an error: no data found where as displaying the same in table goes fine.

I want to retrieve 1 column's data from mysql , display the fetched values in a combo-box(or drop down list) of jsp form!

As a matter of fact, even the `print("");

is working fine

But I have to use the selected value for next jsp page as well. PS:I'm using mysql5.1,tomcat4.1,jdk 1.5.0

January 23, 2013 at 1:42 AM

print("<option><%=rs.getString("deptname")%></option>");

is working fine i meant *

September 29, 2014 at 2:01 AM

I tried this code but it's not displaying the data in the text fields. Please help me out.