Results are out! Find what you need...fast. Get quick advice or join the chat
Hey there! Sign in to join this conversationNew here? Join for free

JSP Dynamic combo box

Announcements Posted on
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    Greetings,

    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:odbc: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>
    • 0 followers
    Offline

    ReputationRep:
    You 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

    Code:
    document.FormComboCategory.Category.options[document.FormComboCategory.Category.selectedIndex].text
    in a chunk of java code, which simply won't work.

    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.

    Code:
    String category = request.getParameter("Category");
    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).

    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.
    • 0 followers
    Offline

    ReputationRep:
    Hey, 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.
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    Thanks for the advice, apologies for the messy code im using notepad thats why the formatting aint there.

    I will be stressing over this later
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    Greetings,

    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("jdbc:odbc:FAQ"); 
                        }
                            catch (Exception e1)  {System.out.print(e1);}
                        {
                            try
                                {  
                                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
                                    conn2 = DriverManager.getConnection("jdbc:odbc:Driver={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>
    • 0 followers
    Offline

    ReputationRep:
    That 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.
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    Thanks 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
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    Oh Im intrigued how I do this, can you expand a little further ..

    if statement in to add the attribute selected="selected"
    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 !!

    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
    • 0 followers
    Offline

    ReputationRep:
    Well if they said you didn't need prior knowledge of Java for this, and haven't taught you any, then they are just plain silly.
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    I'm getting error message:-

    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
    I assume it's because Im checking the value of the option box is null before the option box has been defined.

    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("jdbc:odbc:FAQ"); 
    					}
    						catch (Exception e1)  {System.out.print(e1);}
    					{
    						try
    							{  
    								Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
    								conn2 = DriverManager.getConnection("jdbc:odbc:Driver={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>
    							<%}
    		} %>
    Do my requirements make sense to you?
    • 0 followers
    Offline

    ReputationRep:
    OptionCategoryValue=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.
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    Points 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?
    • 0 followers
    Offline

    ReputationRep:
    Yes.

    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.
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    Thanks for all your help, Im gonna step away from the computer for a while and then get back and re-read it all!!

    Cheers
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    (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.
    I think I must have had a lobotomy

    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
    • 0 followers
    Offline

    ReputationRep:
    I'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("jdbc:odbc:FAQ");
                }
                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>
    • Thread Starter
    • 0 followers
    Offline

    ReputationRep:
    Just wanted to say a big thanks Baron for all the help you gave me, I understand alot more now about JSP/Java.

    Cheers
    Rob
    • 0 followers
    Offline

    ReputationRep:
    You're Welcome.

Reply

Submit reply

Register

Thanks for posting! You just need to create an account in order to submit the post
  1. this can't be left blank
    that username has been taken, please choose another Forgotten your password?
  2. this can't be left blank
    this email is already registered. Forgotten your password?
  3. this can't be left blank

    6 characters or longer with both numbers and letters is safer

  4. this can't be left empty
    your full birthday is required
  1. By joining you agree to our Ts and Cs, privacy policy and site rules

  2. Slide to join now Processing…

Updated: March 27, 2008
New on TSR

Exclusive Nick Clegg interview

Your questions answered by the deputy prime minister

Article updates
Useful resources
Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.