July 24, 2008 07:47pm pdt

Member Login

Top Experts

Yearly

1rrz@87... 115,440
2Kuldeepc... 56,890
3mrcoffee... 53,385
4CEHJ 36,064
5cmalakar 31,268
6mohammedf 29,775
7objects 27,263
8summerian 23,300
9hielo 21,900
10asanga_su 21,425
11contactka... 20,000
12girionis 17,810
13araim 16,580
14karanw 14,955
15logudotcom 12,750

null

	[x] Attachment Details

JSP Select list values resetting after submit

Zones: Java Server Pages (JSP), JavaScript, Algorithms

Tags: JSP HTML javascript, IE6 IE7, select lists submit forms algorithms Tomcat 4.1.31

This is a follow up to the question answered in:

Fill a select list from a database view in JSP
http://www.experts-exchange.com/Programming/Languages/Java/J2EE/JSP/Q_23211660.html

Please see the entire original code segment there.

I have the need to expand the search criteria for my database queries by creating a filter on the original selection list. I've added a second select list that is populated by the selection of the first drop down. This works - sort of. The main problem is that the original select lists value is reset to the top of the column when the user selects the second submit button and this is passed to the final query - unless the user selects the value again.

Here is what I expect to happen:

1. The user is presented with a list of Service Types. They select one and then press the submit button.
2. The value is passed to intermediate query which populates a list of Service Numbers associated with that Service Type, They select one and then press _that_submit button.
3. The values of both the Service Type and Service Number are passed to the final query as the where clause which returns a table of all the related fields.

Here is an except of the code, note the changes I've made from the original post. I've been using the request.getParameterNames() really as a debugging tool, but seems I can use the getParmeterValues() method as a possible solution.

It seems to me my problem is the structure of my logic - I seem to be calling the second query at the wrong time or my use of the submit buttons is misplaced. So, this may be something solved by a cleaner algorithm and less of a coding problem. I've been researching the related questions concerning passing values between select lists and they seem to need to insert a javascript iterator either in the form section or in the formatting functions.

As before, given the limits of my knowledge, the timeframe of the project, and the environment, this will have to be a simple JSP solution. I'll try to get up to speed on beans, structs, Ruby and whatever is best practces - later. I'm using Netbeans as an IDE.

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

           
           
             <B>First, Enter a Service Type:</B>
    <FORM METHOD=post>
        <%= STQuery(connStr) %>
        <INPUT TYPE="submit" VALUE="Enter Service Type">
        <BR>
        <% String STsearchCondition = request.getParameter("STcond");
        if (STsearchCondition != null) { %>
            <BR>
            <B>Next, Enter a Service Number:</B>
            <BR>
            <%= SNQuery(connStr, STsearchCondition) %>
            <INPUT TYPE="submit" VALUE="Enter Service Number"> 
            <% String SNsearchCondition = request.getParameter("SNcond"); %>
            <HR>
            <BR>
            <%= STrunQuery(connStr,STsearchCondition,SNsearchCondition ) %>
         <% } %>
    <input type="reset" value="Reset" />
    </FORM>
    <HR>
 
    <B>Form Content</B><BR>
    <TABLE>
    <%  Enumeration parameters = request.getParameterNames();
        while(parameters.hasMoreElements()){
            String parameterName = (String)parameters.nextElement();
            String parameterValue = request.getParameter(parameterName); %>
    <TR>
        <TD><%=parameterName%></TD>
        <TD><%=parameterValue%></TD>
    </TR>
    <%  }   %>

           
         

Start your free trial to view this solution

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.5

Question Stats

Zone: Programming

Question Asked By: shadowbreeze

Solution Provided By: Kuldeepchaturvedi

Participating Experts: 2

Solution Grade: A

Translate:

Loading Advertisement...

Open Discussion

null

Comment by Kuldeepchaturvedi

Now on to doing it better and cleaner...!

1. It is never a good idea to declare methods inside a jsp page.. creating a java class and using it is a far better idea.
2. Database connections should not be opened and closed too frequently. You should resuse the connection, or better still, use Connectionpool.
3. Do not use Java methods to write out HTML code.. ( sb.appends that we have done in above code). It defeats the purpose of JSP. ( writing out HTML with some server side code, i.e java code, not vice versa)..

On any other day we would have written this code vastly diff than what we have here...

null

Comment by shadowbreeze

Works like a charm! I don't fully understand why we moved the declarations - I understand variable scoping in general but not sure about the specifics here. I'm going to look through the generated servlet code to see if that helps.

I am posting the completed code for future users. I'm about to enter the 'interesting' part of the project - integrating these results with existing pages. They are written primarily in javascript and a propriety macro language.

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:

           
           
             <%-- Import libaries required for sql and parameter lists --%>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
 
<%-- Query and result formatting functions --%>
<%!
private String STQuery(String connStr, String STcond) throws SQLException {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    try {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection(connStr,
                "user", "pwd");
 
        stmt = conn.createStatement();
 
        rset = stmt.executeQuery("SELECT SERVICE_TYPE FROM CWOF_SERVICE_TYPE_VW ORDER BY SERVICE_TYPE ASC");
 
        return (SThtmlSelect(rset, STcond));
    } catch (SQLException e) {
        return ("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
    } finally {
        if (rset!= null) rset.close();
        if (stmt!= null) stmt.close();
        if (conn!= null) conn.close();
    }
}
 
private String SNQuery(String connStr, String STcond, String SNcond) throws SQLException {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    try {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection(connStr,
                "user", "pwd");
 
        stmt = conn.createStatement();
 
        rset = stmt.executeQuery("SELECT DISTINCT(SERVICE_NUMBER) FROM CWOF_SERVICE_NUMBER_VW " +
                "WHERE SERVICE_TYPE=" + "'" + STcond + "'" + "ORDER BY SERVICE_NUMBER ASC");
 
        return (SNhtmlSelect(rset, STcond, SNcond));
    } catch (SQLException e) {
        return ("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
    } finally {
        if (rset!= null) rset.close();
        if (stmt!= null) stmt.close();
        if (conn!= null) conn.close();
    }
}
 
private String STrunQuery(String connStr, String STcond, String SNcond) throws SQLException {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    try {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection(connStr,
                "user", "pwd");
        stmt = conn.createStatement();
 
        rset = stmt.executeQuery("SELECT * FROM CWOF_SERVICE_NUMBER_VW "+
                (STcond.equals("") ? "" : "WHERE SERVICE_TYPE=" +"'"  + STcond + "'" +
                "AND SERVICE_NUMBER=" + "'" + SNcond + "'" ) + "ORDER BY SERVICE_NUMBER");
        return (STformatResult(rset));
    } catch (SQLException e) {
        return ("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
    } finally {
        if (rset!= null) rset.close();
        if (stmt!= null) stmt.close();
        if (conn!= null) conn.close();
    }
}
 
private String STformatResult(ResultSet rset) throws SQLException {
    StringBuffer sb = new StringBuffer();
    if (!rset.next())
        sb.append("<P> No matching rows.<P>\n");
    else {  sb.append("<table border=\"1\" cellpadding=\"5\"><B><tr><th>Service Number</th><th>User Defined ID</th><th>Subscriber ID</th><th>First Name</th><th>Last Name</th><th>Department Number</th><th>Account Number</th><th>Service Type</th><tr>");
    do {  sb.append("<td>" + rset.getString(1) +
            "<td>" + rset.getString(2) +
            "<td>" + rset.getString(3) +
            "<td>" + rset.getString(4) +
            "<td>" + rset.getString(5) +
            "<td>" + rset.getString(6) +
            "<td>" + rset.getString(7) +
            "<td>" + rset.getString(8) +
            "<tr></tr>" +
            "</tr>\n");
    } while (rset.next());
    sb.append("</B></TABLE>");
    }
    return sb.toString();
}
 
private String SThtmlSelect(ResultSet rset, String STcond) throws SQLException {
    StringBuffer sb = new StringBuffer();
    if(rset.next()){
        sb.append("<SELECT NAME='STcond' SIZE='1'>");
        do {
           if (STcond !=null && STcond.equals(rset.getString(1)) )
              sb.append("<OPTION SELECTED>" + rset.getString(1) +   "</OPTION>");
           else
               sb.append("<OPTION>" + rset.getString(1) +   "</OPTION>");
        } while (rset.next());
        sb.append("</SELECT>");
    }
    return sb.toString();
}
 
private String SNhtmlSelect(ResultSet rset, String STcond, String SNcond ) throws SQLException {
    StringBuffer sb = new StringBuffer();
    if(rset.next()){
        sb.append("<SELECT NAME='SNcond' SIZE='1'>");
        do {
            if( SNcond != null && SNcond.equals(rset.getString(1)))
                sb.append("<OPTION   SELECTED> " + rset.getString(1) + "</OPTION>");
            else 
                sb.append("<OPTION>" + rset.getString(1) +   "</OPTION>");
        } while (rset.next());
        sb.append("</SELECT>");
    }
    return sb.toString();
}
%>
 
<HTML>
<HEAD> <TITLE> The JDBCQuery JSP  </TITLE> 
 
 
 
</HEAD>
<BODY BGCOLOR=white>
    
 
<%-- Hard code connection string to DEV for now --%>
    <%
        String connStr=request.getParameter("connStr");
        if (connStr==null) {
            connStr=(String)session.getValue("connStr");
        } else {
            session.putValue("jdbc:oracle:thin:@<host>:1521:<SID>",connStr);
        }
        connStr = "jdbc:oracle:thin:@cwofprod.cio.sc.gov:1521:DEVL10";
 
    %>
 
 
 
    <FORM METHOD=post>
    <BR>
    <B>First, Enter a Service Type:</B>
    <% String STcond = request.getParameter("STcond");
    String SNcond = request.getParameter("SNcond"); %>
     <%= STQuery(connStr,STcond ) %>
        <INPUT TYPE="submit" VALUE="Enter Service Type">
        <BR>
       
        <%
        if (STcond != null) { %>
            <BR>
            <B>Next, Enter a Service Number:</B>
            <BR>
            <%= SNQuery(connStr, STcond, SNcond) %>
            <INPUT TYPE="submit" VALUE="Enter Service Number"> 
            
            <HR>
            <BR>
            <%= STrunQuery(connStr,STcond,SNcond ) %>
         <% } %>
   
    </FORM>
    <HR>
 
 <%-- Show parameter names and values - remove after testing --%>
    <B>Form Content</B><BR>
    <TABLE>
    <%  Enumeration parameters = request.getParameterNames();
        while(parameters.hasMoreElements()){
            String parameterName = (String)parameters.nextElement();
            String parameterValue = request.getParameter(parameterName); %>
    <TR>
        <TD><%=parameterName%></TD>
        <TD><%=parameterValue%></TD>
    </TR>
    <%  }   %>
    
</BODY>
</HTML>
           
         

Open in New Window

null

Comment by shadowbreeze

You must have been watching the instant I finished the grading! It took me longer to copy and post the code than it did for you to respond! Thank again and for the follow up suggestions.

We're doing some "rapid" (wink, wink ... nod, nod) prototyping to work out the kinks of the integration points. I have very limited access to the server side at the moment. I absolutely want to optimize my methods and procedures. I keep going back to the Sun tutorials for concepts and everywhere else for readable examples ;-]

null

Comment by Kuldeepchaturvedi

>>I don't fully understand why we moved the declarations - I understand variable scoping in general but not sure about the specifics here.

When we moved the variable at the top section, the sole intention was to have the variables in hand to use it. ( we used it for STQuery). Since the declaration was with in jsp Body, we have not changed any scope of any variable by moving them around.

20080723-EE-VQP-34 / EE_QW_2_20070628