Member Avatar for bondito
bondito 0 Newbie Poster

Hi everyone, i am building a voting system and i am trying to count the number of votes for each candidate to be stored in a candidates table in the field voteresult, i have stored the results in a bean, and written the sql query statement in a jsp page. When i try the code the voteresult field was not updating or incrementing. Please i need a some help, any help or suggestion would be appreciated. Thanks

try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection c = DriverManager.getConnection("jdbc:mysql:///Voting");
Statement st = c.createStatement();
st.executeUpdate("update voters set vote1 = '" + Vote.getVote1()
+ "', vote2 = '" + Vote.getVote2() + "', vote3 = '" + Vote.getVote3()
+ "', vote4 = '" + Vote.getVote4() + "', vote5 = '" + Vote.getVote5()
+ "', vote6 = '" + Vote.getVote6()
+ "', timestamp = '" + Vote.getTimeStamp() + "', voted = '1' where username = '" + Vote.getUserName() + "'");
st = c.createStatement();
st.executeUpdate("update candidates set voteresult = voteresult + 1 where name = '" + Vote.getVote1().trim() + "'");
st = c.createStatement();
st.executeUpdate("update candidates set voteresult = voteresult + 1 where name = '" + Vote.getVote2() + "'");
st = c.createStatement();
st.executeUpdate("update candidates set voteresult = voteresult + 1 where name = '" + Vote.getVote3() + "'");
st = c.createStatement();
st.executeUpdate("update candidates set voteresult = voteresult + 1 where name = '" + Vote.getVote4() + "'");
st = c.createStatement();
st.executeUpdate("update candidates set voteresult = voteresult + 1 where name = '" + Vote.getVote5() + "'");
st = c.createStatement();
st.executeUpdate("update candidates set voteresult = voteresult + 1 where name = '" + Vote.getVote6() + "'");
st.close();
Edited by happygeek because: fixed formatting
  • 1 Contributor
  • 0 Replies
  • 109 Views

Dani AI

Generated

A few likely causes for the voteresult not changing: the UPDATE never matches a row (name mismatch, extra whitespace, case differences), the bean values are null/empty, an earlier SQL error prevents later updates from running, the voteresult column has an unexpected type or trigger, or the DB user lacks UPDATE rights. The code shown by builds many SQL strings and runs several updates in sequence, which makes debugging and error handling harder.

Recommended checks (in order):

  • Log the actual vote values and the SQL WHERE value before executing the update.
  • Check the integer return value from executeUpdate() to see whether any row was affected.
  • Catch and log SQLExceptions (stack traces) so errors aren’t silently swallowed.
  • Verify voteresult is a numeric column with a sensible default and that the candidate names in the table exactly match the values passed.
  • Test the same UPDATE directly in the MySQL client to confirm the WHERE clause can match.

A robust approach

  • Use a single PreparedStatement with a parameter instead of string concatenation.
  • Trim and skip null/blank votes.
  • Use candidate IDs (primary key) for the WHERE clause if possible (safer than matching names).
  • Wrap the set of updates in a transaction so either all updates succeed or none do.
  • Check executeUpdate()’s returned row count and log any zero-update cases.

Example pattern (replace connection details and supply a String[] votes from the bean):

String sql = "UPDATE candidates SET voteresult = voteresult + 1 WHERE name = ?";
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/Voting", user, pass);
try {
    conn.setAutoCommit(false);
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        for (String v : votes) {
            if (v == null) continue;
            v = v.trim();
            if (v.isEmpty()) continue;
            ps.setString(1, v);
            int updated = ps.executeUpdate();
            if (updated == 0) System.err.println("No candidate matched: " + v);
        }
    }
    conn.commit();
} catch (SQLException ex) {
    if (conn != null) conn.rollback();
    throw ex;
} finally {
    if (conn != null) conn.close();
}

Final notes: avoid putting JDBC code directly in JSP; move it to a servlet or DAO. Proper logging and checking executeUpdate() will quickly reveal whether the problem is name-matching, permissions, or an earlier exception.

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.