Results 1 to 2 of 2
  1. #1
    Penhexy is offline Member
    Join Date
    Mar 2011
    Posts
    1
    Rep Power
    0

    Default 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:
    Java 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:
    Java 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:
    Java 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.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,446
    Rep Power
    18

    Default

    First off I would replace UpdateTeam with a servlet.
    Using JSPs like that is, frankly, an abuse of them...and far harder to debug than a basic servlet. it would also highlight the error almost instantly.

    You are creating a team object, but you are never actually populating it with anything from the request. This:
    Java Code:
    <jsp:useBean id="team" scope="page" class="FootballAdmin.TeamDetails" />
    Simply creates a bean that the page can use. It doesn't take the values in the request and populate it.

    Couple of comments.
    Well done on using a PreparedStatement. That's a rarity round here.
    But...you're never closing your connection.
    Every time you come through here you will make a new connection to Access...eventually it'll stop working.
    I would open the connection and close it for each of those sql statements, personally.

Similar Threads

  1. Replies: 2
    Last Post: 02-11-2009, 04:43 AM
  2. Database access
    By Doctor Cactus in forum New To Java
    Replies: 1
    Last Post: 12-12-2008, 01:30 PM
  3. Updating database table from JTable
    By yesjava in forum New To Java
    Replies: 1
    Last Post: 08-16-2008, 10:16 PM
  4. Help needed with updating mysql database
    By SilentCodingOne in forum New To Java
    Replies: 1
    Last Post: 12-11-2007, 10:23 PM
  5. Replies: 1
    Last Post: 08-07-2007, 07:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •