CODEWITHSUNDEEP
htmldatabasejspdrop-down-menu
heretolearn
heretolearn

Reputation: 6545

Retriving rows from database based on value of select in HTML

I am new to JSP and need help. I want to create a webpage in JSP with a drop down. And to use the value of the selected drop down to retrieve the rows from the database. Is there a way to do this. If yes, then how can we do it. And if no, then also kindly let me know other possibilities. Examples would be highly appreciated.

I have tried to use events such as onclick but not able to get the desired output.

Thanks in advance.

JSP Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ page import="java.sql.*"%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>LIBRARY</title>
</head>
<body>
<center>CATALOG</center>
<select name="subject" size="1" id="subject">
<option value="MATHS">MATHS</option>
<option value="PHY">PHYSICS</option>
<option value="CHEM">CHEMISTRY</option>
<option value="BIO" selected>BIOLOGY</option>             
</select>
</div>
<% 
String str = request.getParameter("subject");
try {
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost/books";
Connection con = DriverManager.getConnection(url,"#","#");
Statement st=con.createStatement();
String QueryString = "SELECT * from catalog WHERE subject='"+str+"'";
ResultSet rs = st.executeQuery(QueryString);
%>
<table class="sortable" id="catalog" width="100%" border="0">
    <thead>
        <tr>
            <th scope="col" class="sr">S. No.</th>
            <th scope="col" class="subject">SUBJECT</th>
            <th scope="col" class="tt">TITLE</th>
            <th scope="col" class="Auth">AUTHOR</th>
            <th scope="col" class="price">PRICE</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <td colspan="5" class="left">&nbps;/td>
        </tr>
    </tfoot>
    <tbody>
        <%
int i=1;
while (rs.next()) {
%>
        <tr>
            <td><%=i%></td>
            <td><%=rs.getString(1)%></td>
            <td><%=rs.getString(2)%></td>
            <td><%=rs.getString(3)%></td>
            <td><%=rs.getString(4)%></td>
        </tr>
        <% i++;} %>
        <%
// close all the connections.
rs.close();
st.close();
con.close();
} catch (Exception ex) {
out.println("Unable to connect to database.");
}
%>
    </tbody>
</table>
</body>
</html>

i am not sure what i am doing wrong. So if there are any mistakes do let me know. Thanks

Upvotes: 0

Views: 1947

Answers (1)

Jacob
Jacob

Reputation: 14731

For your drop down you could try something like the following

<select name="drops" size="1" id="drops">
<option value="Audi">Audi</option>
<option value="BMW">BMW</option>
<option value="Corolla">Corolla</option>
<option value="Benz">Benz</option>
<option value="Others" selected>Others</option>                  
</select>

To get the selected value use the following

String str = request.getParameter("drops");

this will get the selected value from drop down and you could use to fetch database records.

Regards

Edit 1

I have created a test jsp with sample values. You could check whether
request.getParameter() is null or not. If it is not null, then proceed with your database query.

Regards

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
    <title>Request Details--</title>
  </head>
  <body>
  <form name="myform" method="post" action="drops.jsp">
  <select name="drops" size="1" id="drops">
<option value="Audi">Audi</option>
<option value="BMW">BMW</option>
<option value="Corolla">Corolla</option>
<option value="Benz">Benz</option>
<option value="Others" selected>Others</option>                  
</select>
 <input type="Submit" value="Click -me"></input>
</form>
<%         
            if(request.getParameter("drops")!=null){
            String str=request.getParameter("drops");              
            out.println(str);     
            }
            %>
  </body>
</html>

Upvotes: 1

Related Questions