Access Data from Database By Submitting The Combo Box Values

Question

vij123 0 Newbie Poster

14 Years Ago

i am making a program in a JSP. i have used some java script code in this program.

by this program i can find out the result for between years for any class.
i have three combo box for class_name, from_year, to_year. for class_name, Its value is 1 to 12. when i select class 11 or 12, an combox appear for subject having values Arts, comerce and science will be appeared.

in database for class 1 to 10 having subject values is AllSubject. and for class 11 or 12 having subject values arts, commerce and science.

my problem how i make the query which send for class 1 to 10 , assume subject values is all and for class 11 or 12 subject values arts, commerce and science with others combox values.

Here is JSP's files:

display.jsp:

<html>
<head>
<body>
<form name="form" >
<table align="center">

<script language="javascript">
function sendData(){
var sch=document.form.school.value;
var cla=document.form.class_name.value;
var y1=document.form.from_year.value;
var y2=document.form.to_year.value;
window.open("show.jsp?class_name="+cla+"&&from_year="+y1+"&&to_year="+y2+"&&school="+sch);
alert(sch+" "+cla+ " "+y1+" "+y2);
}

function hide(){
if (document.getElementById){
document.getElementById('combo').style.visibility = 'hidden';
}
}
function call(class_name){
var val = class_name.options[class_name.selectedIndex].text;
if((val=='11')||(val=='12')){
document.getElementById('combo').style.visibility = 'visible';
var arr = new Array();
arr[11] = new Array("Art","Commerce","Science");
arr[12] = new Array("Art","Commerce","Science");
var comboValue = class_name.value;
document.forms["form"].elements["combo2"].options.length=0;
for (var i=0;i<arr[comboValue].length;i++)
{
var option = document.createElement("option");
option.setAttribute('value',i+1);
option.innerHTML = arr[comboValue][i];
document.forms["form"].elements["combo2"].appendChild(option);
}
}
else{
document.getElementById('combo').style.visibility = 'hidden';
}
}
</script>
<body onload="hide();">

<p></p><br><br><br><br><br><br>
<tr><td style="width: 11px"> <span>Class</td> <td><select name="class_name" onchange="call(this); >
<%

for( int i=1;i<=12;i++){
%>
<option value="<%=i%>"><%=i%></option>
<%
}
%>
</select><td>
<select id="combo" name="combo2">
</select></td>

<tr><td style="width: 11px"> <span>From Year</td><td><select name="from_year">
<%
for(int j=2000;j<=2010;j++){
%>
<option value="<%=j%>"><%=j%></option>
<%
}
%>
</select></td></tr>
<tr><td style="width: 11px"> <span>To Year</td><td><select name="to_year">
<%
for(int k=2000;k<=2010;k++){
%>
<option value="<%=k%>"><%=k%></option>
<% } %>
</select></td></tr><td></td>
<tr><td style="width: 11px"> <span><input type="button" value="Result" onclick="sendData();"></td></tr>
<tr><td><input type="hidden" name="school" value="<%=request.getParameter("school")%>"></td></tr>
</table>
</form>
</body>
</head>
</html>

show.jsp:

<%@ page language="java" import="java.lang.*" import="java.sql.*" import="java.io.*" import="java.net.*"%>
<html>
<body>
<%
String school=request.getParameter("school");
String class_name=(String)request.getParameter("class_name");
String from_year=(String)request.getParameter("from_year");
String to_year=(String)request.getParameter("to_year");
%>
<table align="center" border="1" align="center">
<br>
<tr align="center">
<td width="150"height="30"><font size="+1">Year</td></font>
<td width="150"height="30"><font size="+1">Class</td></font>
<td width="150"height="30"><font size="+1">Subject</td></font>
<td width="150"height="30"><font size="+1">Total Students</td></font>
<td width="150"height="30"><font size="+1">Passout Students</td></font>
<td width="150"height="30"><font size="+1">Marks Obtained 60 To 80</td></font>
<td width="150"height="30"><font size="+1">Marks Obtained over 80</td></font>
<td width="150"height="30"><font size="+1">Overall Result</td></font>
<td width="150"height="30"><font size="+1">School</td></font>
</tr>

<%
Connection con = null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ;
con = DriverManager.getConnection("jdbc:odbc:sql;user=sa;password=1234");
System.out.println("Connection created");
Statement st=con.createStatement();
System.out.println("st going to execute");
String query="SELECT * FROM Result WHERE year BETWEEN '"+from_year+"' AND '"+to_year+"' and school='"+school+"'";
System.out.println("now data are selecting");
ResultSet rs=st.executeQuery(query);
System.out.println("now data in rs.....");
System.out.println("now going to rs block............");
while(rs.next())
{
%>
<tr align="center">
<td width="150"height="30"><%out.println(rs.getString(1));%>
<td width="150"height="30"><%out.println(rs.getString(2));%>
<td width="150"height="30"><%out.println(rs.getString(3));%>
<td width="150"height="30"><%out.println(rs.getString(4));%>
<td width="150"height="30"><%out.println(rs.getString(5));%>
<td width="150"height="30"><%out.println(rs.getString(6));%>
<td width="150"height="30"><%out.println(rs.getString(7));%>
<td width="150"height="30"><%out.println(rs.getInt(8));%>
<td width="150"height="30"><%out.println(rs.getString(9));%>
</tr>
<%
}
rs.close();
con.close();
}catch(Exception e){out.println(e);}
%>
</table>
</body>
</html>

combox box for subject remain hide when i select class 1 to 10. but if i select class 11 or 12 then
value for subject is Arts, Commerce and Science. how i make query from database base on the subject.

javascript

2 Contributors
4 Replies
144 Views
4 Days Discussion Span
Latest Post 14 Years Ago Latest Post by vij123

parry_kulk 14 Junior Poster

14 Years Ago

You will need to pass the value of subject to show.jsp

One approach could be to modify the sendData() function as shown below

function sendData(){
var sch=document.form.school.value;
var cla=document.form.class_name.value;
var y1=document.form.from_year.value;
var y2=document.form.to_year.value;
var sub="AllSubject";  //set to all subjects by default
//Now check if the combo is visible. If yes than get its value instead of 'AllSubject'
if(document.getElementById('combo').style.visibility=='visible'){
   sub=document.form.combo.value;
}
window.open("show.jsp?class_name="+cla+"&&from_year="+y1+"&&to_year="+y2+"&&school="+sch+"&&sub="+sub); //Pass the subject as request parameter
alert(sch+" "+cla+ " "+y1+" "+y2);
}

Now in show.jsp read the subject using

String subject=(String)request.getParameter("sub");

and use it in the query

String query="SELECT * FROM Result WHERE year BETWEEN '"+from_year+"' AND '"+to_year+"' and school='"+school+"' and subject='"+subject+"'";

Hope this helps

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

vij123 0 Newbie Poster · Answer 1 · 2010-04-14T16:13:27+00:00

thanks for your reply but it does nothing when i click to Result button.

You will need to pass the value of subject to show.jsp

One approach could be to modify the sendData() function as shown below

function sendData(){
var sch=document.form.school.value;
var cla=document.form.class_name.value;
var y1=document.form.from_year.value;
var y2=document.form.to_year.value;
var sub="AllSubject";  //set to all subjects by default
//Now check if the combo is visible. If yes than get its value instead of 'AllSubject'
if(document.getElementById('combo').style.visibility=='visible'){
   sub=document.form.combo.value;
}
window.open("show.jsp?class_name="+cla+"&&from_year="+y1+"&&to_year="+y2+"&&school="+sch+"&&sub="+sub); //Pass the subject as request parameter
alert(sch+" "+cla+ " "+y1+" "+y2);
}

Now in show.jsp read the subject using

String subject=(String)request.getParameter("sub");

and use it in the query

String query="SELECT * FROM Result WHERE year BETWEEN '"+from_year+"' AND '"+to_year+"' and school='"+school+"' and subject='"+subject+"'";

Hope this helps

parry_kulk 14 Junior Poster Team Colleague · Answer 2 · 2010-04-15T14:50:04+00:00

What I had mentioned was regarding how to get the 'subject'.

The problem in code when you click on Result is that there are javascript errors.
The line

var sch=document.form.school.value;

is trying to read the form input element school. But it is not present. Dont get confused in accessing JSP variables/parameters within Javascript.
Javascript do not have access to JSP variables. It comes into action when the dynamic page is rendered by the servlet container and sent to the client.
So now you can either have a form field for school as shown below

<input type="hidden" name="school" value="<%=request.getParameter("School")%>">

Or
you can assign this value in javascript itself by
replacing the lines

<% String school=request.getParameter("School");%>
var sch=document.form.school.value;

with

var sch='<%=request.getParameter("School")%>';

Also using Firefox with the Firebug plugin will help you in getting Javascript errors.

vij123 0 Newbie Poster · Answer 3 · 2010-04-15T20:43:32+00:00

thanks for your reply.
now i am facing null pointer exception.
i am trying to find out.

What I had mentioned was regarding how to get the 'subject'.
The problem in code when you click on Result is that there are javascript errors.
The line
var sch=document.form.school.value;
is trying to read the form input element school. But it is not present. Dont get confused in accessing JSP variables/parameters within Javascript.
Javascript do not have access to JSP variables. It comes into action when the dynamic page is rendered by the servlet container and sent to the client.
So now you can either have a form field for school as shown below
<input type="hidden" name="school" value="<%=request.getParameter("School")%>">
Or
you can assign this value in javascript itself by
replacing the lines
<% String school=request.getParameter("School");%>
var sch=document.form.school.value;
with
var sch='<%=request.getParameter("School")%>';
Also using Firefox with the Firebug plugin will help you in getting Javascript errors.