Solved

Dropdown box with oracle database using JSP

Posted on 2004-09-22
5
461 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
5 Comments
 
LVL 9

Accepted Solution

by:
ronan_40060 earned 125 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 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
A customer recently asked me about anti-malware and the different deployment options available for his business. Daily news about cyberattacks, zero-day vulnerabilities, and companies that suffered a security breach made him wonder if the endpoint a…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now