Solved

Dropdown box with oracle database using JSP

Posted on 2004-09-22
5
470 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Successful collaboration among team members is essential for the growth of your business. When employees work together on projects, share ideas and communicate effectively they get better results.
Part One of the two-part Q&A series with MalwareTech.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

628 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