Fetch value from database and populate it using the list into dropdown based on another dropdown value

Question

I have made a jsp page, and on the change of 1st dropdown, the values of the second dropdown must get changed accordingly.

Values of both the dropdown is fetched from the database.

Values of the 2nd dropdown is getting fetched as the value of the 1st dropdown is the foreign key in that table.

Using onchange event of dropdown 1 I am calling js function and passing it to the servlet to carry out the function.

I am very new to this concept and I am sure that I am making mistakes in my servlet code. Also I used firefox inspect, and according to it, the facno, is being transferred to the servlet, but there is no response from there and thats why the 2nd dropdown gets empty as soon as the value of 1st dropdown changes. Please help me to resolve the same.

JAVASCRIPT CODE:

function Fill() {

        var facno = document.getElementById("facname").value;
        var xhttp = new XMLHttpRequest();
        xhttp.overrideMimeType("text/html;charset=UTF-8");
        xhttp.onreadystatechange = function() {

            if (xhttp.readyState === 4 && xhttp.status === 200) {
                document.getElementById("subname").innerHTML = xhttp.responseText;

            }
        };

        xhttp.open("POST", "DropFill?facnoajax=" + facno, true);
        xhttp.send();
    }

JSP CODE:

DROPDOWN 1:

<%
                    Connection con = null;
                    con = DBConnection.createConnection();
                    PreparedStatement ps = null;
                    try {
                        String sql = "SELECT fac_no, fac_name FROM faculty_info";
                        ps = con.prepareStatement(sql);
                        ResultSet rs = ps.executeQuery();
                %>



                <div class="form-group row">
                    <label class="col-form-label col-md-3" for="facname">Faculty:</label>
                    <div class="col-md-4">
                        <select name="facname" id="facname" class="form-control"
                            onchange="Fill()" required>
                            <option value="" selected>Choose any:</option>

                            <%
                                while (rs.next()) {
                                        String facname = rs.getString("fac_name");
                                        String facno = rs.getString("fac_no");
                            %>
                            <option value="<%=facno%>"><%=facname%></option>
                            <%
                                }
                            %>
                        </select>
                    </div>
                </div>


                <%
                    } catch (SQLException sqe) {
                        out.println(sqe);
                    }
                %>

DROPDOWN 2:

<div class="form-group row" id="dd2">
                    <label class="col-form-label col-md-3" for="subname">Subject:</label>
                    <div class="col-md-4">
                        <select id="subname" name="subname" class="form-control" required>
                        <option value="" selected>Choose any:</option>

                        </select>
                    </div>
                </div>

SERVLET CODE:

public class DropFill extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public DropFill() {
        super();
        // TODO Auto-generated constructor stub
    }

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html;charset=UTF-8");


        try (PrintWriter out = response.getWriter()) {

            ArrayList<String> subname = new ArrayList<String>();
            ArrayList<String> subno = new ArrayList<String>();

            Connection con = null;
            con = DBConnection.createConnection();
            PreparedStatement ps = null;
            String facno = request.getParameter("facnoajax");
            try {
                String sql = "SELECT sub_name FROM subject_info WHERE fac_no='" + facno + "'";
                ps = con.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();

                while (rs.next()) {
                    subname.add(rs.getString("sub_name"));
                    subno.add(rs.getString("sub_no"));
                }

                for (int i = 0; i < subname.size(); i++) {
                    for (int j = 0; j <= subno.size(); j++)
                        response.getWriter().write("<select> <option value=" + subno.get(j) + ">" + subname.get(i)
                                + "</option> </select>");
                }

            }

            catch (SQLException sqe) {
                out.println(sqe);
            }

        }

    }
}

Simply write `response.getWriter().write("Hi");` after catch block and see if this comes back,Also check if the your browser console shows any error .Lastly ,you have use `post` in ajax i think you need to put your code inside `doPost` method of servlet try this as well and see. — Swati, Apr 01 '20 at 09:38
@Swati its not working, still no effect when putting the code dopost method. Also the message hi is not getting back even after writing it after the catch block. I checked the browser console, no errors are there. The network tab shows that facnoajax is being sent over with the desired value. — Kirito, Apr 01 '20 at 23:04

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

JSP

<td><select name="class_main" id="class_main"
                        onchange="this.form.submit();">
                            <option value="0">Select class</option>
                            <%
                                try {
                                    String url = "jdbc:mysql://localhost:3306/online_exam?useTimezone=ture&serverTimezone=UTC";
                                    Connection com = DriverManager.getConnection(url, "root", "");
                                    PreparedStatement ps = com.prepareStatement("select * from class");
                                    ResultSet rs = ps.executeQuery();
                                    while (rs.next()) {
                            %>
                            <option value="<%=rs.getInt("classid")%>"
                                <%if (request.getParameter("class_main") != null) {
                        if (rs.getInt("classid") == Integer.parseInt(request.getParameter("class_main"))) {
                            out.print("selected");
                        }
                    }%>><%=rs.getString("classname")%></option>
                            <%
                                }
                                    classid = request.getParameter("class_main");
                                    System.out.println("From class " + classid);
                                } catch (Exception e) {
                                    System.out.println(e.getMessage());
                                }
                            %>
                    </select></td>
                </tr>

                <tr>
                    <td><label>Choose Subject :</label></td>

                    <td><select name="subject" id="subject"
                        onchange="this.form.submit();">
                            <option value="0">Select Subject</option>
                            <%
                                try {
                                    String url = "jdbc:mysql://localhost:3306/online_exam?useTimezone=ture&serverTimezone=UTC";
                                    Connection com = DriverManager.getConnection(url, "root", "");
                                    PreparedStatement ps = com.prepareStatement("select * from subject where classid = ?");
                                    ps.setString(1, request.getParameter("class_main"));
                                    ResultSet rs = ps.executeQuery();
                                    while (rs.next()) {
                            %>
                            <option value="<%=rs.getInt("subjectid")%>"
                                <%if (request.getParameter("subject") != null) {
                        if (rs.getInt("subjectid") == Integer.parseInt(request.getParameter("subject"))) {
                            out.print("selected");
                        }
                    }%>><%=rs.getString("subjectname")%></option>
                            <%
                                }
                                    subjectid = request.getParameter("subject");
                                    System.out.println("from subject " + subjectid);
                                } catch (Exception e) {
                                    System.out.println(e.getMessage());
                                }
                            %>

                    </select></td>
                </tr>
                <tr>
                    <td><label>Choose Chapter :</label></td>

                    <td><select name="chapter" id="chapter"
                        onchange="this.form.submit();">
                            <option value="0">Select Chapter</option>

                            <%
                                try {
                                    String url = "jdbc:mysql://localhost:3306/online_exam?useTimezone=ture&serverTimezone=UTC";
                                    Connection com = DriverManager.getConnection(url, "root", "");
                                    PreparedStatement ps = com
                                            .prepareStatement("select * from chapter where classid = ? and subjectid = ?");
                                    ps.setString(1, request.getParameter("class_main"));
                                    ps.setString(2, request.getParameter("subject"));
                                    ResultSet rs = ps.executeQuery();
                                    while (rs.next()) {
                            %>
                            <option value="<%=rs.getInt("chapterid")%>"
                                <%if (request.getParameter("chapter") != null) {
                        if (rs.getInt("chapterid") == Integer.parseInt(request.getParameter("chapter"))) {
                            out.print("selected");
                        }
                    }%>><%=rs.getString("chaptername")%></option>
                            <%
                                }
                                    chapterid = request.getParameter("chapter");
                                    System.out.println("From chapter " + chapterid);
                                } catch (Exception e) {
                                    System.out.println(e.getMessage());
                                }
                            %>

</select></td>

I have created same dependend select fields for my project. Here it is — Ferin Patel, Mar 31 '20 at 17:11
Your way doesn't populate my second dropdown. Also using your way refreshes the whole page, because of which i lose data in other fields in my form. Like this, i have achieved the same earlier but it refreshes the page, and then was suggested to use ajax to avoid this condition and that is where i am facing problem in coding. — Kirito, Mar 31 '20 at 23:05

Fetch value from database and populate it using the list into dropdown based on another dropdown value

1 Answers1

JSP