• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

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-
0
hn9636
Asked:
hn9636
  • 2
2 Solutions
 
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now