Dropdown box with oracle database using JSP

I need the code of how to write jsp page which contains 1 dropdown box, if user selects that box, onChange event will fire and it will get data from database(oracle from the server) and the jsp page is refresing and the data in the remaining 5 textboxes are populated based on the data from database.

thanks,
-jspcode-
hn9636Asked:
Who is Participating?
 
ronan_40060Commented:
hello
hn9636
here is a sample example with Oracle database which gets you started

<%@ page import="java.util.*, java.sql.*" %>
<%!
private static String sqlBox1 = "select country from tableone";
private static String sqlBox2 = "select city from tabletwo where country = ?";

%>
<%
Class.forName("oracle.jdbc.driver.OracleDriver()");
Connection con = null;
Statement stat = null;
PreparedStatement ps = null;
try {

con = DriverManager.getConnection("jdbc:oracle:thin"+
   "@<ip_address>:<port>:<sid>","user", "password");
stat = con.createStatement();
ResultSet rs = null;
%>
<html>
<head>
<script language="JavaScript">
<!--
function getSelected( selectbox ) {
    for (i=0;i<selectbox.length;i++)
       if (selectbox.options[i].selected) return selectbox.options[i];
}

function update( level ) {
     var action = "?box1="+getSelected(form1.box1).text;
     if( level >= 2 ) action += "&box2="+getSelected(form1.box2).text;
     location.replace(action);
}
// -->
</script>
</head>
<body>

<form name="form1">

box1:<p>
<select name="box1" onchange="javascript:update(1)">
<%
    String sel1 = request.getParameter( "box1" );

    rs = stat.executeQuery( sqlBox1 );
    while( rs.next() ) {
         String option = rs.getString(1);
          if( sel1 == null ) sel1 = option;
%>
<option <%=option.equals(sel1)?"selected":""%>><%=option%></option>
<%
     }
     rs.close();
%>
</select><p>

box2:<p>
<select name="box2" onchange="javascript:update(2)">
<%
     String sel2 = request.getParameter( "box2" );

     ps = con.prepareStatement( sqlBox2 );
     ps.setString( 1, sel1 );
     rs  = ps.executeQuery();
    while( rs.next() ) {
         String option = rs.getString(1);
          if( sel2 == null ) sel2 = option;
%>
       <option <%=option.equals(sel1)?"selected":""%>><%=option%></option>
<%  }
    rs.close();
     ps.close();
%>
</select><p></p>



<%
} finally {
     if( stat != null ) try { stat.close(); } catch( Exception e ) {}
     if( ps != null ) try { ps.close(); } catch( Exception e ) {}
     if( con != null ) try { con.close(); } catch( Exception e ) {}
}
%>

</form>
</body>
</html>
0
 
CodingExpertsCommented:
Hi hn9636
0
 
CodingExpertsCommented:
Hi hn9636

well this can be achieved using a very simple code ....
Enclose your drop down in a form tag.

<form name="xyz" action="" method="post">
           <%
            String url = "jdbc:oracle:thin:@localhost:1521:ORADB";
            String sql = " ";// your sql goes here ....
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection (url,"username","password");
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery(sql);
          %>
    <select name="dropMe" class="selectBox" onChange="submitForm()" >
    <%  
            rset = stmt.executeQuery(sql);
           while(rset.next())
           {
           %>
              <option value= <%= rset.getString("colName") %> >
              </option>
            <%
            }
            rset.close();
            stmt.close();
            conn.close();
          %>
    %>

  <script language="JavaScript">
    function submitForm()
    {
        document.xyz.submit();
        return false;
    }
  </script>

Good Luck
CodingExperts
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.