Problem updating access database from jsp
Hi all. I am trying to update a microsoft access database using a couple of JSP forms and a database connector class. In theorey, I have a homepage from which you can select an entry and then the update button, which takes you to the UpdateTeamForm.jsp and displays all the fields in textboxes (This works so far), then I want to be able to change the textfields and click the update button on that form, which should send the information to UpdateTeam.jsp which utilises the updateTeam method from the database connector to execute an SQL statement and update the information in the database. However, for some reason the object isn't being passed. Either between the UpdateTeamForm.jsp and the UpdateTeam.jsp or between the UpdateTeam.jsp and the database connector. I have posted the code for the JSPs and Classes involved below. Can anyone spot anything wrong?
UpdateTeamForm.jsp:
Code:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.sql.*" %>
<%@page import="FootballAdmin.DBConnector" %>
<jsp:useBean id="db" scope="page" class="FootballAdmin.DBConnector" />
<jsp:useBean id="team" scope="page" class="FootballAdmin.TeamDetails" />
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Admin Page - Update Team</title>
</head>
<body>
<h1>Update Team Details</h1>
<hr>
<%
String Team_Name = request.getParameter("Team_Name");
if (Team_Name == null || Team_Name.length() == 0)
response.sendRedirect("TeamNameError?message=You must select a team");
else {
db.createConnection();
team = db.selectTeamByTeamName(Team_Name);
%>
Please make the amendments you require: <br><br>
<form name="amend" method="post" action="UpdateTeam.jsp?Team_Name=<%=Team_Name%>" >
Team Name: <%=team.getTeamName()%><br><br>
Stadium:
<input type="text" name="Stadium" value="<%=team.getStadium()%>" /><br>
Points:
<input type="text" name="Points" value="<%=team.getPoints()%>" /><br>
<br><br>
<input type="submit" name="button" value="Amend" />
<input type="submit" name="button" value="Cancel" />
</form>
<%
} // end else
%>
</body>
</html>
UpdateTeam.Jsp:
Code:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.sql.*" %>
<%@page import="FootballAdmin.DBConnector" %>
<jsp:useBean id="db" scope="page" class="FootballAdmin.DBConnector" />
<jsp:useBean id="team" scope="page" class="FootballAdmin.TeamDetails" />
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<%
if (request.getParameter("button").equals("Cancel"))
response.sendRedirect("TeamAdmin.jsp");
else {
%>
<h1>Update Confirmation</h1>
<hr>
<jsp:setProperty name="team" property="*" />
<%
db.createConnection();
String message = db.updateTeam(team);
db.closeConnection();
if (message.length() != 0)
response.sendRedirect("DBError.jsp?message=" + message);
else {
%>
<br>
Team <%=team.getTeamName() %> has been updated.
<br><br>
<a href="TeamAdmin.jsp">Back to the admin home page</a>
<%
}
}
%>
</body>
</html>
Database connector:
Code:
package FootballAdmin;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
*
* @author greg
*/
public class DBConnector {
private String dbName = "SOFT225DB";
private Connection conn = null;
private ResultSet results = null;
public DBConnector() {
}
public void createConnection() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dbURL = "jdbc:odbc:" + dbName;
conn = DriverManager.getConnection(dbURL);
}
catch (Exception e) {
e.printStackTrace();
}
}
public TeamList selectAllTeams(){
TeamList teamlist = null;
try{
String strQuery = "SELECT Team_Name, Stadium, Points"+
" FROM Teams" +
" ORDER BY Team_Name";
PreparedStatement stmt = conn.prepareStatement(strQuery);
results = stmt.executeQuery();
teamlist = new TeamList(results);
}
catch (SQLException e){
e.printStackTrace();
}
return teamlist;
}
public TeamDetails selectTeamByTeamName(String strTeamName) {
TeamDetails team = null;
try {
String strQuery = "SELECT Team_Name, Stadium, Points " +
"FROM Teams" +
" WHERE Team_Name = ?";
PreparedStatement stmt = conn.prepareStatement(strQuery);
stmt.setString(1, strTeamName);
results = stmt.executeQuery();
if (results.next()) {
team= new TeamDetails(
results.getString(1),
results.getString(2),
results.getInt(3));
}
}
catch (SQLException e) {
e.printStackTrace();
}
return team;
}
public String updateTeam(TeamDetails team) {
int noOfUpdates = 0;
String message = "";
try {
String strQuery = "UPDATE Teams" +
" SET Stadium = ?, Points = ? " +
" WHERE Team_Name = ?";
PreparedStatement stmt = conn.prepareStatement(strQuery);
stmt.setString(1, team.getStadium());
stmt.setInt(2, team.getPoints());
stmt.setString(3, team.getTeamName());
noOfUpdates = stmt.executeUpdate();
}
catch (SQLException e) {
e.printStackTrace();
message = "SQL Exception: " + e.getMessage();
}
if (message.length() ==0 && noOfUpdates == 0)
message = "Record was not updated";
return message;
}
}
Any help would be much appreciated :)
Thanks,
Penhexy.
