How to build dynamic 'select' drop-down list?

cgttsp01
cgttsp01 used Ask the Experts™
on
How in JSP can I make a select drop-down list dynamic?  That is, I want my select drop-down populated by a query.
How do I output the query results to the drop-down list?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mick BarryJava Developer
Top Expert 2010

Commented:
Something like:

<select>
<%
ResultSet rs = statement.executeQuesy(sql);
while (rs.next())
{
   String item = rs.getString(1);
%>
   <option><%= item %></option>
<%
}
%>
Mick BarryJava Developer
Top Expert 2010

Commented:
And of course close the select :)

<select>
<%
ResultSet rs = statement.executeQuesy(sql);
while (rs.next())
{
  String item = rs.getString(1);
%>
  <option><%= item %></option>
<%
}
%>
</select>

Author

Commented:
What am I doing wrong here?  The 'select' drop-down populates with the same date in each option of the select box.  It looks like it creates the same date for every record in the DB2 table.

Here is my code.  Any suggestions on how I should fix it?

\********************************************************\
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>
<TR> <TD> CURDATE  </TD>
     <TD> TITLE </TD>
     <TD> TECHNICIAN </TD>
</TR>
<%
  Class.forName("ibm.sql.DB2Driver");
  java.sql.Connection connection = java.sql.DriverManager.getConnection("JDBC:db2os390:DB2TSG");
  java.sql.Statement statement = connection.createStatement();
  java.sql.ResultSet columns = statement.executeQuery("SELECT * FROM CBSTDTFI.STATUS");
  while(columns.next()) {
    String CURDATE  = columns.getString("CURDATE");
    String TITLE = columns.getString("TITLE");
    String TECHNICIAN = columns.getString("TECHNICIAN");     %>
      <TR>  <TD> <%= CURDATE  %> </TD>
            <TD> <%= TITLE %> </TD>
            <TD> <%= TECHNICIAN %> </TD>
      </TR>


<FORM ACTION="search.cfm" METHOD="POST" target="main">
<tr><td colspan="3"><select>
<%

while (CURDATE.next())
{

%>
 <option><%= CURDATE %></option>
<%
}
%>
</select>

<% } %></td></tr>
<INPUT TYPE="Submit" NAME="" VALUE="go">
</form>
</TABLE>
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Mick BarryJava Developer
Top Expert 2010

Commented:
Surprised that even ran due to this statement??

while (CURDATE.next())

as CURDATE is a String.
Mick BarryJava Developer
Top Expert 2010

Commented:
Your problem is you are creating a select list for *every* row in the result set.
If you need to display the row details in both a table and a select list then you'll need to save the required data from the row in some Collection for later use.

Author

Commented:
I don't need to display the row details.
Just the select drop-down.

I'm walking my way through trying to convert an existing web program into JSP, and as you probably picked up I'm new to JSP.

Java Developer
Top Expert 2010
Commented:
...
<%
 Class.forName("ibm.sql.DB2Driver");
 java.sql.Connection connection = java.sql.DriverManager.getConnection("JDBC:db2os390:DB2TSG");
 java.sql.Statement statement = connection.createStatement();
 java.sql.ResultSet columns = statement.executeQuery("SELECT * FROM CBSTDTFI.STATUS");
%>
<TR><TD>
<FORM ACTION="search.cfm" METHOD="POST" target="main">
<SELECT>
<%
 while(columns.next()) {
   String CURDATE  = columns.getString("CURDATE");
   String TITLE = columns.getString("TITLE");
   String TECHNICIAN = columns.getString("TECHNICIAN");
%>
<option><%= CURDATE %></option>
<%
}
%>
</select>
<INPUT TYPE="Submit" NAME="" VALUE="go">
</form>

</td></tr>
</TABLE>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial