how to identify specific name attributes from an html select tag in a while loop into jsp

Question

hi, iam retrieving data from the database using a while loop en pouring it out in table format as u can see below, along with a column that has a select tag included in the while loop,now the options for the select tag are;(pending and cleared),which at choice i want to use to update the respective database table,but the trouble is,when i press the update button,it updates all the values instead of the specific chosen.plz help.

html code

<tr>
    <td width=50><div align="center"><%=datez%></div></td>
    <td width=50><div align="center"><%=cashrecid%></div></td>
    <td width=50><div align="center"><%=fname%></div></td>
    <td width=50><div align="center"><%=lname%></div></td>
    <td width=50><div align="center"><%=cashout%></div></td>
    <td width=50><div align="center"><%=purpose%></div></td>
    <td width=50><div align="center"><%=project%></div></td>
    <td width=50><div align="center">
        <select name="select" size="1" id="select">
            <option value="pending">pending</option>
            <option value="cleared">cleared</option>
        </select>
    </div></td>
  </tr>

jsp code

<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%! public int cashrecid; %>

<%

String uname=(String)session.getAttribute("theName");
String status=request.getParameter("select");
Connection con1=DriverManager.getConnection("jdbc:odbc:pettyz","sa","pass@word");
PreparedStatement stmt1=con1.prepareStatement("select cashrecid from cashrequisitions where status='pending' ");
ResultSet rs1=stmt1.executeQuery();

while(rs1.next()) {
    cashrecid=rs1.getInt(1);
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con=DriverManager.getConnection("jdbc:odbc:pettyz","sa","pass@word");
    PreparedStatement stmt=con.prepareStatement("update cashrequisitions set status=? where cashrecid=?");
    stmt.setString(1,status);
    stmt.setInt(2,cashrecid);
    stmt.executeUpdate();
}

rs1.close();
stmt1.close();
con1.close();

%>

Answer 1

I don't understand the actual problem/requirement.

But at least I can see the following: Your first SQL query is retrieving all cashrequisitions from the DB which have a status of "pending". Your second query updates the status for all of those obtained cashrequisitions to the selected value. So if you select "pending", effectively nothing will change. But if you select "cleared", effectively everything will be set to "cleared". You'll have no one row left with status "pending". Further on the uname variable is hanging a bit round.

Don't you need the uname in your first query? What's the functional requirement after all? Do you maybe only want to change the status for the particular uname which is also a column of the cashrequisitions table? If so, you need to replace the both SQL queries with that:

preparedStatement = connection.prepareStatement("UPDATE cashrequisitions set status=? where uname=?");
preparedStatement.setString(1, status);
preparedStatement.setString(2, uname);

This query will effectively update the status of all cashrequisitions with the specified uname to the specified status value. If you have a hard time in SQL (which looks so), I can recommend you this basic SQL tutorial.

That said, your code is a bit .. uh .. unusual. You normally write Java code in Java classes, not in JSP files. Ignore those old fashioned 90's tutorials. The use of scriptlets is highly discouraged. The way you write the JDBC code is also far from efficient and very sensitive to resource leaks. To get the picture how to do the basic stuff the proper way, you may find this article useful.