how can I select project from dropdown then execute query catching the value of the project selected
I have the follow code using AJAX and JSP pages
admin.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>index</title>
<script>
function showState(str){
var xmlhttp;
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("domain").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getProjects.jsp?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>
Domain : <select onchange="showState(this.value)">
<%
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
return;
}
Connection connection = null;
System.setProperty("oracle.net.tns_admin", "C:/oracle/product/10.2.0/client_1/NETWORK/ADMIN");
String dbURL = "jdbc:oracle:thin:@qcd1";
try {
connection = DriverManager.getConnection(dbURL, "user", "password");
}
catch (SQLException e) {
e.printStackTrace();
return;
}
PreparedStatement stmt=null;
//select from all active domain in ALM
stmt=connection.prepareStatement("SELECT DISTINCT(DOMAIN_ID),DOMAIN_NAME FROM qcsiteadmin_db.projects ORDER BY 2");
ResultSet rs=null;
rs= stmt.executeQuery();
while(rs.next()){
%>
<option value="<%=rs.getString(1)%>"><%=rs.getString(2)%></option>
<%
}
%>
</select>
<div id="domain">
Project :
<select>
<option>Select Domain then Project</option>
</select>
</div>
</body>
</html>
The admin.jsp JSP display the list of my domains then other dropdown to select Project(depending of the domain selected) from getProjects.jsp THE CODE BELLOW (query it´s depend of the domain selected )
getProjects.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*"%>
<%!int i;%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Project Page</title>
<body>
Project :
<select>
<%
String str=request.getParameter("q");
i=Integer.parseInt(str);
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
return;
}
Connection connection = null;
System.setProperty("oracle.net.tns_admin", "C:/oracle/product/10.2.0/client_1/NETWORK/ADMIN");
String dbURL = "jdbc:oracle:thin:@qcd1";
try {
connection = DriverManager.getConnection(dbURL, "user", "password");
}
catch (SQLException e) {
e.printStackTrace();
return;
}
PreparedStatement stmt=null;
stmt=connection.prepareStatement("select DB_NAME,PROJECT_NAME from qcsiteadmin_db.projects where domain_id='"+i+"'order by 2");
ResultSet rs=null;
rs= stmt.executeQuery();
while(rs.next()){
%>
<option value="<%=rs.getString(1)%>"><%=rs.getString(2)%></option>
<%
}
%>
</select>
</body>
</html>
my connection database is working fine , the bellow table must be displaying when Project is selected from dropdown
Note: this is not a static table the table information depends of the value selected please check my page that display the table
getRoles.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<head>
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/static/style.css"/>
</head>
<body>
<div class="CSSTableGenerator">
<table>
<tr>
<td>
USERID
</td>
<td>
FULL NAME
</td>
<td>
EMAIL
</td>
<td>
ROLE
</td>
</tr>
<%
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
return;
}
Connection connection = null;
System.setProperty("oracle.net.tns_admin", "C:/oracle/product/10.2.0/client_1/NETWORK/ADMIN");
String dbURL = "jdbc:oracle:thin:@qcd1";
try {
connection = DriverManager.getConnection(dbURL, "user", "password");
}
catch (SQLException e) {
e.printStackTrace();
return;
}
PreparedStatement stmt=null;
stmt=connection.prepareStatement("SELECT us_username,full_name,email, GR_Group_Name from energy_energy_plm_db.users, energy_energy_plm_db.Groups,qcsiteadmin_db.users where substr(us_group, gr_group_id + 1, 1) = '1' and us_username=user_name and gr_group_name='TDAdmin' and us_username not like '%admin%' order by 1");
ResultSet rs=null;
rs= stmt.executeQuery();
while(rs.next()){
%>
<tr> <td> <%= rs.getString(1) %></td>
<td> <%= rs.getString(2) %></td>
<td> <%= rs.getString(3) %></td>
<td> <%= rs.getString(4) %></td> </tr>
<%
}
%>
</table>
</div>
</body>
</html>
I don´t know what´s next
