Fill second dropdown on the selection of first dropdown

Question

I'm trying to fill the dropdown on run time from the database, whenever user selects the city from the dropdown, then in the next dropdown, it will fill the respective values from the database. How can I achieve this thing in JSP?

Here is what I have done so far:

 <div class="label">City : </div>
 <div class="control">
 <select name="city" id="city">
 <%
 try {
 ResultSet rs = state.executeQuery("SELECT CITY_ID,CITY_NAME FROM CITY ORDER BY CITY_NAME");
 while (rs.next()) {
 %>
 <option value="<%=rs.getString(1)%>"><%=rs.getString(2) + " (" + rs.getString(1) + ")"%></option>
  <%
                                                                                                        }
                                                                                                    } catch (Exception ex) {
                                            %>
                                            <option value="ERROR">CITY NOT AVAILABLE</option>
                                            <%                                                        }
                                            %>
                                        </select>
                                    </div>
                                    <div style="clear:both;height:0px;"></div>
                                    <div class="label">Report To : </div>
                                    <div class="control">
                                        <select name="report_to" id="report_to">
                                            <%
                                                        try {
                                                            ResultSet rs = state.executeQuery("SELECT HOUSE_ID,HOUSE_ADD FROM HOUSE WHERE CITY_ID='PNP' ORDER BY HOUSE_ID");
                                                            while (rs.next()) {
                                            %>
                                            <option value="<%=rs.getString(1)%>"><%=rs.getString(2) + " (" + rs.getString(1) + ")"%></option>
                                            <%
                                                                                                        }
                                                                                                    } catch (Exception ex) {
                                            %>
                                            <option value="ERROR">HOUSE NOT AVAILABLE</option>
                                            <%                                                        }
                                            %>
                                        </select>
                                    </div>
                                    <div style="clear:both;height:0px;"></div>

Answer 1

First step would be to add onchange="submit()" to the first dropdown, so that the form will be "automatically" submitted by JavaScript. You can then just fill the second dropdown based on the submitted value of the first dropdown which you retrieve as request parameter the usual way.

You might only want to get rid of scriptlets and add a servlet so that your JSP is a bit more maintainable.

See also:

• Populating cascading dropdown lists in JSP/Servlet (shows 3 different ways along with concrete examples)

Answer 2

For this to work either the users first selection (City) must be sent to the server which could then look up the selections available for that city and then re-render the page. Or, you could look up all Houses for all cities and then have a javascript that switched the content of the Houses selection box based on what selection is made in the first one.

As a side note, unless you are doing a very simplistic page (and you do not expect it will need to be maintained) it is bad design to mix your db access logic with your view generation logic...