Member Avatar for mimsc

I have an idea how to do this, but never did it before

heres my code:

<label for="weatherCity">City:</label>
<select name="weatherCity" id="Cities">
<option value="0"> </option>

here's my query:

SELECT distinct state
FROM ols_weather_current
ORDER BY 1 asc;

Connection connection;
PreparedStatement pstmt;
ResultSet rst2;


try { // execute the query
connection = DataBaseConnectionManager.getConnection();
String sqlQuery = "SELECT distinct state FROM ols_weather_current
    ORDER BY 1 asc";
pstmt = connection.prepareStatement(sqlQuery);
ResultSet rst2;

rst2 = pstmt.executeQuery(sqlQuery2);

%>
<tr>
<td> State:
<select>;
<% while (rst2.next ()) {

}  ("</select>");
%>

Im not sure how to set up the while statement...any help would be appreciated


thanx

  • 2 Contributors
  • 1 Reply
  • 110 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Sulley's Boo

Dani AI

Generated

Short answer: do the DB work in a servlet/DAO, build a List of state values, set it on the request, and let the JSP render the <select> with JSTL. — your approach is fine in concept, but the posted snippet has a few concrete problems (ResultSet declared twice, calling executeQuery with a non‑existent variable, and leaving resources open). @SulleysBoo posted a reference link above.

Example DAO (use your own connection provider or pooling — replace DB constants):

public class WeatherDAO {
    public List<String> getDistinctStates() throws SQLException {
        List<String> states = new ArrayList<>();
        String sql = "SELECT DISTINCT state FROM ols_weather_current ORDER BY state ASC";
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                String s = rs.getString("state");
                if (s != null) states.add(s.trim());
            }
        }
        return states;
    }
}

Servlet controller snippet to forward to JSP:

WeatherDAO dao = new WeatherDAO();
List<String> states = dao.getDistinctStates();
request.setAttribute("states", states);
request.getRequestDispatcher("/weatherForm.jsp").forward(request, response);

JSP rendering with JSTL (include JSTL jars and these taglibs):

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %>

<select name="weatherCity" id="Cities">
  <option value="">-- choose state --</option>
  <c:forEach var="st" items="${states}">
    <option value="${fn:escapeXml(st)}">${fn:escapeXml(st)}</option>
  </c:forEach>
</select>

Troubleshooting tips: make sure the JDBC driver/JSTL jars are on the classpath, test the DAO independently, use try-with-resources to avoid leaks, escape values to avoid XSS, and prefer connection pooling in production. This pattern keeps presentation and DB logic separated and avoids the scriptlet pitfalls seen in the original post.

Member Avatar for Sulley's Boo

:-/

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.