Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dropdown box with oracle database using JSP

Posted on 2004-09-22
5
Medium Priority
?
476 Views
Last Modified: 2006-11-17
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
Comment
Question by:hn9636
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
ronan_40060 earned 500 total points
ID: 12127195
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
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12131182
Hi hn9636
0
 
LVL 6

Assisted Solution

by:CodingExperts
CodingExperts earned 500 total points
ID: 12131237
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
In this article, the configuration steps in Zabbix to monitor devices via SNMP will be discussed with some real examples on Cisco Router/Switch, Catalyst Switch, NAS Synology device.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question