July 25, 2008 02:33pm pdt

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

1. bloodredsun 1,155,574
2. objects 624,376
3. TimYates 598,746
4. rrz@87... 482,535
5. Kuldeepc... 363,280
6. kennethxu 357,436
7. mrcoffee... 226,243
8. boonleng 202,640
9. jaggernat 197,407
10. fargo 190,519
11. actonwang 189,622
12. suprapto45 155,308
13. cheekycj 109,407
14. CEHJ 81,383
15. shivaspk 78,767

03.03.2008 at 08:11PM PST, ID: 23211660

	[x] Attachment Details

[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.6

Fill a select list from a database view in JSP

Zone: Java Server Pages (JSP)

Tags: JSP HTML Database, IE6 IE7

Background: I have a help desk application I need to integrate with a billing system. Both are on Oracle databases. My first task is to get billing information from a database view for users to select. This will populate a web form that will be saved to a new help desk table. The help desk application is completely web based and I need to have the billing information selected from a web page. The web server uses Tomcat 4.1.31 running on RHL.

What I have so far:
A basic JSP page that creates a JDBC connection; a simple text input box to create the WHERE clause of the query; a result set from query; and a simple HTML table of the results.

What I need next:

Instead of the text box as input, I need to pull a list of Billing Code values from another database view into a select list (aka: drop down list). The user will then select a value and this will populate the WHERE clause to retrieve the related fields. These will eventually need to post to another page that includes help desk information - but that's for later.

The database views do not include any id / primary key fields - they are simple LOVs. These are maintained by the billing system DBAs.

I am new to JSP. I have searched for related examples but none have seemed to fit the problem or were way over my head. I don't have time to come up to speed on beans, JSTL, or other Java improvements unless it's the _only_ way to solve the problem in the shortest time.

Here is my code so far:

         
           
             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:

           
           
             <%@ page import="java.sql.*" %>
 
<HTML>
<HEAD> <TITLE> The JDBCQuery JSP  </TITLE> </HEAD>
<BODY BGCOLOR=white>
 
<%
connStr = "jdbc:oracle:thin:@<host>:1521:<SID>";
 
%>
 
<% String searchCondition = request.getParameter("cond"); 
   if (searchCondition != null) { %>
      <H3> Search results for : <I> <%= searchCondition %> </I> </H3>
      <%= runQuery(connStr,searchCondition) %>
      <HR><BR>
<% }  %>
 
<B>Enter a search condition:</B>
<FORM METHOD=get> 
<INPUT TYPE="text" NAME="cond" SIZE=30>
<INPUT TYPE="submit" VALUE="Enter Billing Code");
</FORM>
</BODY>
</HTML>
<%! 
  private String runQuery(String connStr, String cond) 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 DESCRIPTION, ITEM_CODE, INCOME_ACCOUNT FROM BILLING_CODE_VW "+ 
                                  (cond.equals("") ? "" : "WHERE " + cond ));
	return (formatResult(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 formatResult(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>Description</th><th>Item Code</th><th>Income Account</th></tr>"); 
	    do {  sb.append("<td>" + rset.getString(1) + 
			    "<td>" + rset.getString(2) + 
			    "<td>" + rset.getString(3) +
			"</tr>\n");
            } while (rset.next());
 	    sb.append("</B></TABLE>"); 
    }
    return sb.toString();
  }
%>

           
         

Start your free trial to view this solution

Question Stats

Zone: Programming

Question Asked By: shadowbreeze

Solution Provided By: TomasHelgi

Participating Experts: 1

Solution Grade: A

Translate:

Loading Advertisement...

20080236-EE-VQP-29 / EE_QW_2_20070628