JSP Dynamic combo box
From C++ to PHP, debugging to webhosting; help and discussion about writing your latest program to running your website. NOT for help when your PC won't work.
| Announcements | Posted on | |
|---|---|---|
| TSR launches Learn Together! - Our new subscription to help improve your learning | 16-05-2013 | |
-
- Reputation:
- Thread Starter
- Junior Member
- Location: Stoke-on-trent
- Posts: 59
JSP Dynamic combo boxGreetings,
Im doing a uni module involving jsp (maybe not the best choice for me since i dont have ANY java background!!).
Anyway from research Ive written jsp to connect to a ms access database and populate TWO combo boxes. Unfortunately they are not dynamic.
I need to check if the first combo box has changed value and if it has then pass it on to the next combo box.
My problems are now as follows:-
I dont know how to write the if statement to say only re-query the database to populate comobox 1 if no selection has been made else a selection has been made and pass the value of combo box 1 into the SQL query used by combo box 2 (Ive had a stab at this!, stab in the dark!!)
Im hope that made some sense, if not hopefully the code will say it better.
At the moment i belive i select a value, re-calls the same jsp page and as I have no if statement it simply repopulates them both every time.
Any help or guidance would be appreaciated!!
Thanks
Rob
Code:<!-- the % tag below is what is called a scriptlet tag - allows java to be embedded in the jsp --> <%@ page import="java.util.*" %> <%@ page language="java" %> <%@ page import="java.sql.*" %> <HTML> <H1>FAQ</H1> <H3>Category choice</H3> <Form Name ="FormComboCategory" FORM ACTION="wk465682UserMenu.jsp" METHOD="POST"> <% String CategoryCombo = null;%> <SELECT NAME="Category" id= Category onChange="location.href='wk465682UserMenu.jsp?option='+this.value;"> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); Connection conn = DriverManager.getConnection("jdbc
FAQ");
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("SELECT DISTINCT CATEGORY FROM FAQ" );%>
<OPTION VALUE='nochoice'>Please choose a category</OPTION>";
<%while(rs.next()) {
CategoryCombo = rs.getString("Category");%>
<OPTION value ="optcategory"><%out.println(CategoryCombo);%>
</OPTION>
<% } %>
</SELECT>
<BR><BR>
<H3>Question selection</H3>
<%
String QuestionCombo = null;%>
<SELECT NAME="Question" id = Question>
<OPTION VALUE='nochoice'>Please choose a question</OPTION>";
<%ResultSet ss = statement.executeQuery("SELECT * FROM FAQ WHERE CATEGORY = ( '" +
document.FormComboCategory.Category.options[document.FormComboCategory.Category.selectedIndex].text
+ "')");
while(ss.next()) {
QuestionCombo = ss.getString("Question");%>
<OPTION value="optquestion"><%out.println(QuestionCombo);%>
</OPTION>
<% }
//document.FormComboCategory.Category.options[document.FormComboCategory.Category.selectedIndex].text
%>
</SELECT>
</FORM>
</HTML> -
- Reputation:
- Vengeful, Imperial Overlord of The Student Room
- Location: London
- Posts: 4,863
Re: JSP Dynamic combo boxYou code is a little hard to read. I would work on formatting it a bit better.
A script which calls itself will need to repopulate each select box everytime regardless, because the entire page is entirely rerendered each time. The only way to avoid repopulating the select boxes is to use something like AJAX, or to return all the possible data and then don't have the script call itself, but have javascript handle the selections.
I don't think your current code will compile as you seem to be getting confused with java and javascript. You have the following javascript
in a chunk of java code, which simply won't work.Code:document.FormComboCategory.Category.options[document.FormComboCategory.Category.selectedIndex].text
Here are some hints for what you need to do to have a simple script that calls itself.
First of you need to get the selection from the first combo box.
Check if this is null. If it is null, then it means that the user has not made a selection on the first combo box (this probably means it is the first time the page has loaded).Code:String category = request.getParameter("Category");
The first combo box is populated from the database everytime the script runs. If category is not null then when you are outputting the options of this combo box, remember to output the selected option tag with selected="selected".
If category is not null then run a query to populate the second combo box. If category is null, then leave the second combo box empty.
This method requires no javascript and therefore is very robust.Last edited by Baron; 25-02-2008 at 17:13. -
- Reputation:
- Full Member
- Location: Scarborough
- Posts: 136
Re: JSP Dynamic combo boxHey, sorry if I've got the wrong end of the stick, but you seem to be talking about Javascript right? Java & Javascript are 2 different languages (both by Sun Microsystems, but both completely different). just thought I'd point that one out. Afraid I find it really difficult to read your code - I think you need to look at how you use indentations, and maybe be a bit more consistent with your upper & lower cases. I've just started a module in javascript, but haven't got as far as connecting to databases yet - only web pages. If you want a really good book to read, try DOM scripting.
Sorry I couldn't be of much assistance.
Last edited by electrogear; 25-02-2008 at 15:22. -
- Reputation:
- Thread Starter
- Junior Member
- Location: Stoke-on-trent
- Posts: 59
Re: JSP Dynamic combo boxGreetings,
Well Ive started from scratch and tidied up my code, just to reiterate my goals :-
Display TWO selection option boxes (cascading).
If the FIRST selection option box changes then reload the jsp using onchange event (not changing the option box value on relaod i.e. not re-running the SQL query).
ONLY on initial entry should the form FIRST selection option box should be populated using JDBC SQL.
The SECOND selection option box can then be populated from the value of the FIRST selection option box.
Then when the selection is made from the SECOND selection option box display the correspong record details
My code was attempting to check the value of the option box and if it was null i.e. the first time the user had entered the screen then populate the Option value ELSE just forward its current Value which could be picked up by the SECOND selection box.
However I dont know how to check the Option value as I get the following error message:-
Originally Posted by
An error occurred at line: 12 in the jsp file: /examples/wk465682UserMenu.jsp
Generated servlet error:
The method getparameter(String) is undefined for the type HttpServletRequest
An error occurred at line: 12 in the jsp file: /examples/wk465682UserMenu.jsp
Generated servlet error:
OptionCategoryValue cannot be resolved
Code:<!-- the % tag below is what is called a scriptlet tag - allows java to be embedded in the jsp --> <%@ page import="java.util.*" %> <%@ page language="java" %> <%@ page import="java.sql.*" %> <HTML> <H1>FAQ</H1> <% String OptionCategory = null;%> <H3>Please choose a category</H3> <FORM ACTION="wk465682UserMenu.jsp" METHOD="POST"> <SELECT NAME="category" onChange="location.href='wk465682UserMenu.jsp?option='+this. value;"> <% Connection conn = null, conn1 = null, conn2 = null; if (request.getparameter("OptionCategoryValue") == null) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); conn1 = DriverManager.getConnection("jdbcFAQ");
}
catch (Exception e1) {System.out.print(e1);}
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
conn2 = DriverManager.getConnection("jdbcDriver={Microsoft Access Driver*.mdb)};DBQ=C:/ProgramFiles/Apache Software Foundation/Tomcat 6.0/webapps/2008-sem2/wk465682/FAQ.mdb");
}
catch (Exception e2) {System.out.print(e2);}
}
if(conn1 == null) conn = conn2;
else conn = conn1;
PreparedStatement mystatement = conn.prepareStatement("SELECT category from category");
ResultSet result = mystatement.executeQuery();
while(result.next()) {
OptionCategoryValue=result.getString(1); %>
<OPTION VALUE=OptionCategoryValue><%out.println(OptionCategory);%></OPTION>
<%}
} %>
</SELECT>
</FORM>
</HTML> -
- Reputation:
- Vengeful, Imperial Overlord of The Student Room
- Location: London
- Posts: 4,863
Re: JSP Dynamic combo boxThat error message is saying that getparameter(String) is not a method of the HttpServletRequest Object.
I think you mean to use getParameter(String) with an uppercase 'P'.
But in terms of the problem in general, do you mean to have two select boxes on the page at the same time? You current script will only ever show one select box.
If you want two select boxes on the page, then I'm not sure why you are so keen not to have the first SQL statement run a second time? As I mentioned earlier the technique you are trying to use involves your JSP calling itself to rerender the entire page, so you will have to repopulate all selects boxes every time the script runs (i.e. in your case you will always need you script to output two select boxes). If you don't want to rerun the SQL query then you need to either use a different technique to what you are currently doing or explain where you want to get the categories from (if not the database) when the script runs the second time after the user has made the first selection?
Regardless of where the categories come from, if you use this technique, the way you keep the first select box selected to the same value as it was before the form is submitted, you have to put an if statement in to add the attribute selected="selected" to the the appropriate option tag while you are creating the category select box the second time the script runs.
On a side note you proabably only want one Connection Object. You can run all you SQL statements from the one Connection and it will make it simpler. -
- Reputation:
- Thread Starter
- Junior Member
- Location: Stoke-on-trent
- Posts: 59
Re: JSP Dynamic combo boxThanks for the reply, much appreciated
I spotted the reason for the error after and also spotted I hadn't assigned the INPUT VALUE correctly either.
The first selection box is populated with (Select * from category) a table of distinct catergories.
The second selection box is populated with (Select * from FAQ where category = FIRST Selection box value).
My idea, was the first Selection box once loaded initially wont ever need to be repopulated as ONCE you have all the categories in there you dont need to re-select them.
Once you have selected questions I want it to display the contents of table FAQ and have a button to 'View another question' reloading the jsp again.
This is to prevent the user from changing the values of the combo boxes so they become out of sync i.e. selectiong a category after a question has been selected, so the question therefore doesnt belong to it.
Or to simplfy I want to make things as simple as possible regarding validation.
Indeed it only has ONE selection box cus I re-wrote it and wanted to build the code up step by step.
The connection string malarky is becasue Im working from home and have to demo it at uni, so I took code detailed in the uni module for such an event where I need to run it from either location.
Again thanks for all your help appreciated!
Rob -
- Reputation:
- Thread Starter
- Junior Member
- Location: Stoke-on-trent
- Posts: 59
Re: JSP Dynamic combo boxOh Im intrigued how I do this, can you expand a little further ..
The module Im studying is all about Java and the WWW using JSP, it was recommended that a prior knowledge of Java isnt needed .. hmmmmmmm !!if statement in to add the attribute selected="selected"
I've managed to get all my other JSP working and was initially confused why HTML forms cant have an INPUT type of integer as having to parse everything was unnatural to me.
Cheers
Rob -
- Reputation:
- Thread Starter
- Junior Member
- Location: Stoke-on-trent
- Posts: 59
Re: JSP Dynamic combo boxI'm getting error message:-
I assume it's because Im checking the value of the option box is null before the option box has been defined.org.apache.jasper.JasperExceptio n: Unable to compile class for JSP
An error occurred at line: 12 in the jsp file: /examples/wk465682UserMenu.jsp
Generated servlet error:
OptionCategoryValue cannot be resolved
Do my requirements make sense to you?Code:<FORM ACTION="wk465682UserMenu.jsp" METHOD="POST"> <SELECT NAME="category" onChange="location.href='wk465682UserMenu.jsp?option='+this.value;"> <% Connection conn = null, conn1 = null, conn2 = null; if (request.getParameter("OptionCategoryValue") == null) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); conn1 = DriverManager.getConnection("jdbcFAQ");
}
catch (Exception e1) {System.out.print(e1);}
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
conn2 = DriverManager.getConnection("jdbcDriver={Microsoft Access Driver*.mdb)};DBQ=C:/ProgramFiles/Apache Software Foundation/Tomcat 6.0/webapps/2008-sem2/wk465682/FAQ.mdb");
}
catch (Exception e2) {System.out.print(e2);}
}
if(conn1 == null) conn = conn2;
else conn = conn1;
PreparedStatement mystatement = conn.prepareStatement("SELECT category from category");
ResultSet result = mystatement.executeQuery();
while(result.next()) {
OptionCategoryValue=result.getString(1); %>
<OPTION VALUE="OptionCategoryValue"><%out.println(OptionCategory);%></OPTION>
<%}
} %> -
- Reputation:
- Vengeful, Imperial Overlord of The Student Room
- Location: London
- Posts: 4,863
Re: JSP Dynamic combo boxOptionCategoryValue=result.getSt ring(1) ;
should be
String OptionCategoryValue=result.getSt ring(1);
and really the variable name should start with a lower case letter
String optionCategoryValue=result.getSt ring(1);
Your requirements make sense, but as I've eplained will not be technically possible doing it this way. Just run the SQL statement for the first select box everytime the script is run and keep things simple. -
- Reputation:
- Thread Starter
- Junior Member
- Location: Stoke-on-trent
- Posts: 59
Re: JSP Dynamic combo boxPoints noted, thanks.
I am however still confused, When the category is changed (FIRST selection box) and the onChange reloads the jsp it will then re-populate the option using SQL therefore overwriting the users category choice? -
- Reputation:
- Vengeful, Imperial Overlord of The Student Room
- Location: London
- Posts: 4,863
Re: JSP Dynamic combo boxYes.
The onchange is a clientside javascript event that happens in the users browser. This event has no direct connection to the JSP whatsoever. All it does is cause the users browser to request the serverside JSP which will generate all of the html for the page from scratch. It is essentianlly the same as having a hyperlink on a webpage pointing to itself - when you click it, the entire page is downloaded again.
This will not override the users choice because if the user has made a choice, you will have the script output html for the first select box which keeps the selection. -
- Reputation:
- Thread Starter
- Junior Member
- Location: Stoke-on-trent
- Posts: 59
Re: JSP Dynamic combo boxI think I must have had a lobotomy(Original post by Baron)
This will not override the users choice because if the user has made a choice, you will have the script output html for the first select box which keeps the selection.
Running my jsp I make a selection which triggers the onchange event and then it reloads the form and it doesnt keep the selection.
Within the URL onChange it states
http://localhost:8080/examples/wk465...nCategoryValue
Which Im not sure If I should expect the ACTUAL option value to be there (e.g. Email or Ftp) or the name of the option box as shown above (OptionCategoryValue)
So its running the select query again at present, can you help cure me of my stupidity here!!!
Thanks again
Rob -
- Reputation:
- Vengeful, Imperial Overlord of The Student Room
- Location: London
- Posts: 4,863
Re: JSP Dynamic combo boxI've played about with your exisiting code to give the structure of how this page should work. There were a couple little problems with the script which I hadn't picked up on, but I've briefly explained what I changed in comments.
I'm unable to test this code as I don't have an oracle db with your tables. I also didn't know what SQL you needed to run for your second select box, so you need to sort that out. Until you do that, you will probably get a runtime SQLException after making the selection on the first select box.
Code:<!-- the % tag below is what is called a scriptlet tag - allows java to be embedded in the jsp --> <%@ page import="java.util.*" %> <%@ page language="java" %> <%@ page import="java.sql.*" %> <HTML> <H1>FAQ</H1> <H3>Please choose a category</H3> <!-- changed this to use GET method. You can use POST if you wish, however the selected options will not appear in the browser address bar--> <FORM ACTION="wk465682UserMenu.jsp" METHOD="get"> <% //The connection to the database we will use Connection conn = null; //The selected category by the user. Note that if this is the first time //the user loaded the page, and they have not made a selection this will be null. String userSelectedCategory = request.getParameter("userSelectedCategory"); //Try to create the connection to the database try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); conn = DriverManager.getConnection("jdbcFAQ");
}
catch(Exception e1)
{
System.out.print(e1);
}
//If the connection to the database is null, we can't do anything!
if(conn != null)
{
//I changed the onChange javascript event to just submit the form. Your
//old one was trying to send stuff as GET parameters by changing the browser location.
//I think this is a better way of doing it.
%>
<SELECT NAME="userSelectedCategory" onChange="this.form.submit();">
<%
//Create a statement to populate the first select box which will contain the catgeories
//This statement will run everytime the page loads.
PreparedStatement mystatement = conn.prepareStatement("SELECT category from category");
ResultSet result = mystatement.executeQuery();
//Create a blank first option so the onChange event works.
out.println("<option value=\"-\">-</option>");
//Loop though all the categories in the database
while(result.next())
{
//This is a category from the database
String optionCategoryValue = result.getString(1);
//Construct the option tag in a String variable
String optionTag = "<OPTION VALUE=\"" + optionCategoryValue + "\"";
//If the user selection equals this selection, make it selected.
//If the user has not made the selection yet, then userSelectedCategory will be null
//and the equals() method will return false
if(optionCategoryValue.equals(userSelectedCategory))
{
optionTag += " selected=\"selected\"";
}
//close the option tag
optionTag += ">" + optionCategoryValue + "</OPTION>";
//printout the option tag
out.println(optionTag);
}
//Close the result set and statment to free up resoures
result.close();
mystatement.close();
%>
</SELECT>
<%
//If the user has made the selection, then create the second select box
//Otherwise this is the first time the page has loaded and we will not
//print out the second select box and instead wait for the users category selection.
if(userSelectedCategory != null)
{
%>
<SELECT NAME="secondSelectBox" onChange="this.form.submit();">
<%
//This is where you populate the second select box. This statement will only run when the first selection has been made.
//I have no idea where you want to get the
//data from in the database, but presumably it will invlove a SQL statement which uses the users
//first category selection. Something along the lines of this:
PreparedStatement mySecondStatement = conn.prepareStatement("SELECT something FROM something WHERE something = '" + userSelectedCategory + "'");
ResultSet mySecondResult = mySecondStatement.executeQuery();
while(mySecondResult.next())
{
String somethingString = result.getString(1);
out.println("<OPTION VALUE=\"" + somethingString + "\">" + somethingString + "</OPTION>");
}
//Close the result set and statment to free up resoures
mySecondResult.close();
mySecondStatement.close();
%>
</SELECT>
<%
}
//Close the database connection to free up resoures
conn.close();
}
%>
</FORM>
</HTML>Last edited by Baron; 16-03-2008 at 22:51.
Sorry I couldn't be of much assistance.
