I'm working on creating a dynamic database web page that allows user to enter the info on the input text and database should show it as well as their main page (teaching.jsp) However, there's no error when I execute it but the database is not updated with anything, it just stays null.
Teaching.jsp
<%@page import="java.sql.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
<%
%>
<%
String target1 = null;
String target2 = null;
String performance1 = null;
String performance2 = null;
int score1 = 0;
int score2 = 0;
String USER = "username";
String PASS = "password";
String staffID = (String)session.getAttribute("user");
Class.forName("org.apache.derby.jdbc.ClientDriver");
Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/projectreborn", USER, PASS);
PreparedStatement ps = con.prepareStatement("Select * from teaching where STAFF_ID = ? and area = ?");
ps.setString(1, staffID);
ps.setString(2, "Review & Revamp (R&R) Process");
ResultSet rsa = ps.executeQuery();
if (rsa.next()) {
target1 = rsa.getString("TARGET");
performance1 = rsa.getString("PERFORMANCE");
score1 = rsa.getInt("SCORE");
}
ps.setString(1, staffID);
ps.setString(2, "New Programme");
ResultSet rs2 = ps.executeQuery();
if (rs2.next()) {
target2 = rs2.getString("TARGET");
performance2 = rs2.getString("PERFORMANCE");
score2 = rs2.getInt("SCORE");
}
%>
</head>
<body>
<div class="sidebar">
<a href="Homepage.jsp">Home Page</a>
<a href="Teaching.jsp">Teaching</a>
<a href="Logout">Logout</a>
</div>
<form method="POST" action="UpdateInfo">
<table border="1">
<tr>
<td>Area</td>
<td>Target</td>
<td>Actual Performance</td>
<td>Measurement</td>
<td>Rating Scale</td>
<td>KPI Score</td>
</tr>
<tr>
<td>Review & Revamp (R&R) Process</td>
<td><textarea name="target1"><%=target1%></textarea></td>
<td><textarea name="performance1"><%=performance1%></textarea></td>
<td><p>(1) No. of programmes for which R&R has been conducted.</p>
<p>(2) Active participation in the annual programme review process and the 3-yearly programme revamp.</p></td>
<td>
<p>5-Active involvement and significant contribution</p>
<p>4-Frequent involvement and some contribution</p>
<p>3-Partial involvement and some contribution</p>
<p>2-Minimal involvement and some contribution</p><p>1-Some contribution</p>
<p>0-No involvement</p>
</td>
<td>
<select name="score1">
<option value="<%=score1%>" hidden selected><%=score1%></option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</td>
</tr>
<tr>
<td> New Programmes </td>
<td> <textarea name="target2"><%=target2%></textarea></td>
<td>
<textarea name="performance2"><%=performance2%></textarea>
</td>
<td>
<p>(1) Active involvement in the new programme development</p>
<p>(2) Additional points for staff who have been leading the MQA/MOHE preparation task force within the faculties</p>
</td>
<td>
<p>5-Active involvement and significant contribution</p>
<p>4-Frequent involvement and some contribution</p>
<p>3-Partial involvement and some contribution</p>
<p>2-Minimal involvement and some contribution</p>
<p>1-Some contribution</p>
<p>0-No involvement</p>
</td>
<td><select name="score2">
<option value="<%=score2%>" hidden selected><%=score2%></option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</td>
</tr>
</table>
<input type="submit" class="btn" value="Update Info">
</form>
</body>
</html>
UpdateInfo.java <--- servlet
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
/**
*
* @author Michael
*/
public class UpdateInfo extends HttpServlet {
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code>
* methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
try (PrintWriter out = response.getWriter()) {
/* TODO output your page here. You may use following sample code. */
HttpSession session = request.getSession();
String staffID = (String)session.getAttribute("user");
String target1 = request.getParameter("target1");
String target2 = request.getParameter("target2");
String performance1 = request.getParameter("performance1");
String performance2 = request.getParameter("performance2");
String score1 = request.getParameter("score1");
int score1Int = Integer.parseInt(score1);
String score2 = request.getParameter("score2");
int score2Int = Integer.parseInt(score2);
try {
Class.forName("org.apache.derby.jdbc.ClientDriver");
String USER = "username";
String PASS = "password";
Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/projectreborn", USER, PASS);
PreparedStatement ps = con.prepareStatement("update staff set target=?, performance=?, score=? where staff_id=? and area=?");
ps.setString(1, target1);
ps.setString(2, performance1);
ps.setInt(3, score1Int);
ps.setString(4, staffID);
ps.setString(5, "Review & Revamp(R&R) Process");
ps.executeUpdate();
PreparedStatement ps1 = con.prepareStatement("update staff set target=?, performance=?, score=? where staff_id=? and area2=?");
ps1.setString(1, target2);
ps1.setString(2, performance2);
ps1.setInt(3, score2Int);
ps1.setString(4, staffID);
ps1.setString(5, "New Programme");
ps1.executeUpdate();
PreparedStatement ps2 = con.prepareStatement("update staff set score2=? where staff_id=?");
ps2.setInt(1, score1Int+score2Int);
ps2.setString(2, staffID);
ps2.executeUpdate();
out.println("<script type=\"text/javascript\">");
out.println("alert('Updated!');");
out.println("window.location.href = \"Homepage.jsp\";");
out.println("</script>");
} catch (Exception e){
out.println("<script type=\"text/javascript\">");
out.println("alert('Failed to Enter Data');");
out.println("window.location.href = \"Homepage.jsp\";");
out.println("</script>");
}
}
}
// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
/**
* Handles the HTTP <code>GET</code> method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Handles the HTTP <code>POST</code> method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Returns a short description of the servlet.
*
* @return a String containing servlet description
*/
@Override
public String getServletInfo() {
return "Short description";
}// </editor-fold>
}
My database table is STAFF with - STAFF_ID (Varchar 30 pk) - PASSWORD (Varchar 30) - NAME (Varchar 30) - SCORE (Varchar 30) - SCORE2 (Varchar 30) - TARGET (Long Varchar) - PERFORMANCE (Long Varchar) - PERFORMANCE2 (Long Varchar) - AREA (Varchar 255) - AREA2 (Varchar 255)