Solved

Why Servlet is not updating database

Posted on 2009-04-01
7
655 Views
Last Modified: 2013-11-24
I'm using netbeans 6.5.1, oracle xe, in a Tomcat bundle. Netbeans is showing no errors of the servlett code. When I fill out the form from the browser the data isn't entering into the Student table. Could someone check my code or give me suggestions on how I can get this to work in netbeans. Again, I'm not getting erros!
*********SERVLET CODE***********
 

package UNNCPK;
 

import java.io.*;

import java.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

import java.util.*;
 

/**

 *

 * @author Valuede Customer

 */

public class StudentForm 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 {

    }
 

    public void doGet(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

                HttpSession session = request.getSession();

        response.setContentType("text/html;charset=UTF-8");

        PrintWriter out = response.getWriter();
 

        out.println("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Strict//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd'>");

        out.println("<html xmlns='http://www.w3.org/1999/xhtml' xml:lang='en' lang='en'>");

        out.println("<head>");

        out.println("<link rel='stylesheet' href='images/Techmania.css' type='text/css' />");

        out.println("<title>49ers Student Sign-up Form</title>");

        out.println("</head>");

        out.println("<body>");

        out.println("<div id='wrap'>");

        out.println("<div id='header'>");

        out.println("<h1 id='logo-text'>49ers<span class='gray'>Student</span></h1>");

        out.println("<h2 id='slogan'><a href='http://uncc.edu/'><img src='images/UNCCcrown.gif' width='70' height='41' alt='firefox' class='align-right' /></h2>");

        out.println("<div id='header-tabs'>");

        out.println("<ul>");

        out.println("<li id='current'><a href='LoginPage'><span>Home</span></a></li>");

        out.println("<li><a href='ClassOffering'><span>Classed Offered</span></a></li>");

        out.println("<li><a href='LoginPage'><span>Detail Schedule</span></a></li>");

        out.println("<li><a href='FacultyPage'><span>Faculty</span></a></li>");

        out.println("<li><a href='LoginPage'><span>Enrollment</span></a></li>");

        out.println("<li><a href='LoginPage'><span>Support</span></a></li>	");

        out.println("</ul>");

        out.println("</div>");

        out.println("</div>");

        out.println("<div id='content-wrap'>");

        out.println("<div id='main'>");

        out.println("<a name='TemplateInfo'></a>");

        out.println("<h1>Enter your Information</h1>");

        out.println("<h3>Student Form</h3>");

        out.println("<form action='ClassOffering' method = 'post'>");

        out.println("<p>");

        out.println("<label>Social Security Number</label>");

        out.println("<input name='SSN' value=' ' type='text' size='30' />");

        out.println("<label>First Name</label>");

        out.println("<input name='FIRSTNAME' value=' 'type= 'text' size= '30' />");

        out.println("<label>Last Name</label>");

        out.println("<input name='LASTNAME'  value='  'type= 'text' size='30' />");

        out.println("<label> Create Your User Name</label>");

        out.println("<input name='USERNAME'value='  'type= 'text' size='30' />");

        out.println("<label>Create Your Password</label>");

        out.println("<input name='PASSWORD' value='password 'type= 'text' size= '30' />");

        out.println("<label>City</label>");

        out.println("<input name='CITY' value='  ' type= 'text' size='30' />");

        out.println("<label>Major</label>");

        out.println("<input name='MAJOR' value='  ' type= 'text' size='30' />");

        out.println("<label>Minor</label>");

        out.println("<input name= 'MINOR' value='   ' type='text' size='30' />");

        out.println("<label>State</label>");

        out.println("<input name='STATE' value='  ' type='text' size='30' />");

        out.println("<label>Zip</label>");

        out.println("<input name='ZIP' value=' ' type='text' size='30'/>");

        out.println("<input class='button' type='submit' />");

        out.println("</p>");

        out.println("</form>");

        out.println("<br />");

        out.println("</div>");

        out.println("<div id='sidebar'>");

        out.println("<h1>Sidebar Menu</h1>");

        out.println("<ul class='sidemenu'>");

        out.println("<li><a href='LoginPage'>Home</a></li>");

        out.println("<li><a href='ClassOffering'>Classes Offered</a></li>");

        out.println("<li><a href='FacultyPage'>Faculty Support</a></li>");

        out.println("<li><a href=''>Your Schdele</a></li>");

        out.println("<li><a href=''>Enrollment</a></li>");

        out.println("</ul>");

        out.println("<h1>Search</h1>");

        out.println("<form method='post' id='search' action='#'>");

        out.println("<p>");

        out.println("<input name='search_query' class='textbox' type='text' /> ");

        out.println("<input name='search' class='searchbutton' value='Search' type='submit' />");

        out.println("</p>");

        out.println("</form>");

        out.println("<h1>Wise Words</h1>");

        out.println("<p>What do we put here</p>");

        out.println("<p class='align-right'>- Aristotle</p>");

        out.println("<h1>Support Styleshout</h1>");

        out.println("<p><a href='http://www.google.com/'>google</a> </p>");

        out.println("</div>");

        out.println("</div>");

        out.println("<div id='footer'>");

        out.println("<span id='footer-left'>&copy; 2009 <strong>Group 8</strong> ");

        out.println("Design by: <strong><a href='index.html'>Mario</a></strong> ");

        out.println("Valid: <a href='http://validator.w3.org/check?uri=referer'>XHTML</a>");

        out.println("<a href='http://jigsaw.w3.org/css-validator/check/referer'>CSS</a>");

        out.println("</span>");

        out.println("<span id='footer-right'>");

        out.println("<a href='http://www.uncc.edu'>UNCC Home</a> | ");

        out.println("<a href='https://selfservice.uncc.edu/pls/BANPROD/twbkwbis.P_GenMenu?name=homepage'>" +

                "Banner Self Service </a>");

        out.println("</span>");

        out.println("</div>");

        out.println("</div>");
 
 
 
 

            String SSN = request.getParameter("SSN");

            String FIRSTNAME = request.getParameter("FIRSTNAME");

            String LASTNAME = request.getParameter("LASTNAME");

            String USERNAME = request.getParameter("USERNAME");

            String PASSWORD = request.getParameter("PASSWORD");

            String CITY = request.getParameter("CITY");

            String MAJOR = request.getParameter("MAJOR");

            String MINOR = request.getParameter("MINOR");

            String STATE = request.getParameter("STATE");

            String ZIP = request.getParameter("ZIP");
 
 

        Connection conn = null;

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");
 

            conn = DriverManager.getConnection(

                    "jdbc:oracle:thin:@localhost:1521:xe[uncc on UNCC]",

                    "UNCC",

                    "UNCC");
 

            Statement stmt = (Statement)conn.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT COUNT (*) FROM STUDENT WHERE StdFirstName = '"+FIRSTNAME+"' AND StdLastName = '"+LASTNAME+"'");

             if (rs.next()==true)

             {

               stmt.executeUpdate("UPDATE STUDENT SET StdSNN = '"+SSN+"'AND StdFirstName ='"+FIRSTNAME+"'AND StdLastName='"+LASTNAME+"' AND StdUserName='"+USERNAME+"' StdPassword='"+PASSWORD+"' AND StdCity='"+CITY+"' AND StdMajor='"+MAJOR +"' AND StdMinor ='"+MINOR+"'AND StdState='"+STATE+"' StdZip='"+ZIP+"' WHERE StdFirstName = '"+FIRSTNAME+"' AND StdLastName = '"+LASTNAME+"'");

             } else

             { stmt.executeUpdate ("INSERT INTO STUDENT (StdSSN, StdFirstName, StdLastName, StdUserName, StdPassword, StdCity, StdMajor, StdMinor, StdState, StdZip, StdClass, StdAdvisor, StdGPA VALUES ('"+SSN+"', '"+FIRSTNAME+"', '"+LASTNAME+"', '"+USERNAME+"','"+PASSWORD+"','"+CITY+"','"+MAJOR +"','"+MINOR +"','"+STATE+"','"+ZIP+"', '', '', '')");

                }
 
 

            } catch(SQLException e) {

                out.println("SQLException: " + e.getMessage() + "<BR>");

                 while((e = e.getNextException()) != null)

                 out.println(e.getMessage() + "<BR>");

            } catch(ClassNotFoundException e) {

               out.println("ClassNotFoundException: " + e.getMessage() + "<BR>");

            } finally {

              //Clean up resources, close the connection.

              if(conn != null) {

                  try {

                     conn.close();

                 } catch (Exception ignored) {

                 }
 

                }
 

            }

            out.println("</body> ");

            out.println("</html>");

            out.close();

       }

     protected void doPost(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

        }

}
 
 

**************SQL TABLE***************************
 

create table Student

( StdSSN Char(11) NOT NULL,

  StdFirstName VARCHAR2(50) NOT NULL,

  StdLastName VARCHAR2(50) NOT NULL,

  StdUserName VARCHAR2 (25) NOT NULL,

  StdPassword VARCHAR2 (12) NOT NULL,

  StdCity VARCHAR2 (50) NOT NULL,

  StdMajor VARCHAR2(50),

  StdMinor VARCHAR2(50),

  StdState CHAR(2) NOT NULL,

  StdZip CHAR(10) NOT NUll, 

  StdClass CHAR(2),

  StdAdvisor VARCHAR2(50) NOT NULL,

  StdGPA DECIMAL (3,2),

  Constraint PKStudent Primary Key (StdSSN),

  Constraint FKDeptID FOREIGN KEY (StdMajor)

  REFERENCES Department (DeptID)

);

Open in new window

0
Comment
Question by:percivalevans
  • 3
  • 3
7 Comments
 
LVL 5

Expert Comment

by:trungnt8
ID: 24046100
Your form is POST, but your doPost method is empty?
0
 
LVL 3

Expert Comment

by:kingsob
ID: 24047602
as trungnt8 said, your goPost method is empty. the values returned by request.getParameter are all going to be null, and since your database table does not accept null values, it will likely throw some kind of exception

you are going to want someting like this... if it were me tho, i would seperate the html out into a seperate html file, and then just post it to your servlet


Derek
*********SERVLET CODE***********

 

package UNNCPK;

 

import java.io.*;

import java.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

import java.util.*;

 

/**

 *

 * @author Valuede Customer

 */

public class StudentForm 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 {

    }

 

    public void doGet(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

                HttpSession session = request.getSession();

        response.setContentType("text/html;charset=UTF-8");

        PrintWriter out = response.getWriter();

 

        out.println("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Strict//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd'>");

        out.println("<html xmlns='http://www.w3.org/1999/xhtml' xml:lang='en' lang='en'>");

        out.println("<head>");

        out.println("<link rel='stylesheet' href='images/Techmania.css' type='text/css' />");

        out.println("<title>49ers Student Sign-up Form</title>");

        out.println("</head>");

        out.println("<body>");

        out.println("<div id='wrap'>");

        out.println("<div id='header'>");

        out.println("<h1 id='logo-text'>49ers<span class='gray'>Student</span></h1>");

        out.println("<h2 id='slogan'><a href='http://uncc.edu/'><img src='images/UNCCcrown.gif' width='70' height='41' alt='firefox' class='align-right' /></h2>");

        out.println("<div id='header-tabs'>");

        out.println("<ul>");

        out.println("<li id='current'><a href='LoginPage'><span>Home</span></a></li>");

        out.println("<li><a href='ClassOffering'><span>Classed Offered</span></a></li>");

        out.println("<li><a href='LoginPage'><span>Detail Schedule</span></a></li>");

        out.println("<li><a href='FacultyPage'><span>Faculty</span></a></li>");

        out.println("<li><a href='LoginPage'><span>Enrollment</span></a></li>");

        out.println("<li><a href='LoginPage'><span>Support</span></a></li>	");

        out.println("</ul>");

        out.println("</div>");

        out.println("</div>");

        out.println("<div id='content-wrap'>");

        out.println("<div id='main'>");

        out.println("<a name='TemplateInfo'></a>");

        out.println("<h1>Enter your Information</h1>");

        out.println("<h3>Student Form</h3>");

        out.println("<form action='ClassOffering' method = 'post'>");

        out.println("<p>");

        out.println("<label>Social Security Number</label>");

        out.println("<input name='SSN' value=' ' type='text' size='30' />");

        out.println("<label>First Name</label>");

        out.println("<input name='FIRSTNAME' value=' 'type= 'text' size= '30' />");

        out.println("<label>Last Name</label>");

        out.println("<input name='LASTNAME'  value='  'type= 'text' size='30' />");

        out.println("<label> Create Your User Name</label>");

        out.println("<input name='USERNAME'value='  'type= 'text' size='30' />");

        out.println("<label>Create Your Password</label>");

        out.println("<input name='PASSWORD' value='password 'type= 'text' size= '30' />");

        out.println("<label>City</label>");

        out.println("<input name='CITY' value='  ' type= 'text' size='30' />");

        out.println("<label>Major</label>");

        out.println("<input name='MAJOR' value='  ' type= 'text' size='30' />");

        out.println("<label>Minor</label>");

        out.println("<input name= 'MINOR' value='   ' type='text' size='30' />");

        out.println("<label>State</label>");

        out.println("<input name='STATE' value='  ' type='text' size='30' />");

        out.println("<label>Zip</label>");

        out.println("<input name='ZIP' value=' ' type='text' size='30'/>");

        out.println("<input class='button' type='submit' />");

        out.println("</p>");

        out.println("</form>");

        out.println("<br />");

        out.println("</div>");

        out.println("<div id='sidebar'>");

        out.println("<h1>Sidebar Menu</h1>");

        out.println("<ul class='sidemenu'>");

        out.println("<li><a href='LoginPage'>Home</a></li>");

        out.println("<li><a href='ClassOffering'>Classes Offered</a></li>");

        out.println("<li><a href='FacultyPage'>Faculty Support</a></li>");

        out.println("<li><a href=''>Your Schdele</a></li>");

        out.println("<li><a href=''>Enrollment</a></li>");

        out.println("</ul>");

        out.println("<h1>Search</h1>");

        out.println("<form method='post' id='search' action='#'>");

        out.println("<p>");

        out.println("<input name='search_query' class='textbox' type='text' /> ");

        out.println("<input name='search' class='searchbutton' value='Search' type='submit' />");

        out.println("</p>");

        out.println("</form>");

        out.println("<h1>Wise Words</h1>");

        out.println("<p>What do we put here</p>");

        out.println("<p class='align-right'>- Aristotle</p>");

        out.println("<h1>Support Styleshout</h1>");

        out.println("<p><a href='http://www.google.com/'>google</a> </p>");

        out.println("</div>");

        out.println("</div>");

        out.println("<div id='footer'>");

        out.println("<span id='footer-left'>&copy; 2009 <strong>Group 8</strong> ");

        out.println("Design by: <strong><a href='index.html'>Mario</a></strong> ");

        out.println("Valid: <a href='http://validator.w3.org/check?uri=referer'>XHTML</a>");

        out.println("<a href='http://jigsaw.w3.org/css-validator/check/referer'>CSS</a>");

        out.println("</span>");

        out.println("<span id='footer-right'>");

        out.println("<a href='http://www.uncc.edu'>UNCC Home</a> | ");

        out.println("<a href='https://selfservice.uncc.edu/pls/BANPROD/twbkwbis.P_GenMenu?name=homepage'>" +

                "Banner Self Service </a>");

        out.println("</span>");

        out.println("</div>");

        out.println("</div>");

 

 

 

 

       }

     protected void doPost(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

            String SSN = request.getParameter("SSN");

            String FIRSTNAME = request.getParameter("FIRSTNAME");

            String LASTNAME = request.getParameter("LASTNAME");

            String USERNAME = request.getParameter("USERNAME");

            String PASSWORD = request.getParameter("PASSWORD");

            String CITY = request.getParameter("CITY");

            String MAJOR = request.getParameter("MAJOR");

            String MINOR = request.getParameter("MINOR");

            String STATE = request.getParameter("STATE");

            String ZIP = request.getParameter("ZIP");

 

 

        Connection conn = null;

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

 

            conn = DriverManager.getConnection(

                    "jdbc:oracle:thin:@localhost:1521:xe[uncc on UNCC]",

                    "UNCC",

                    "UNCC");

 

            Statement stmt = (Statement)conn.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT COUNT (*) FROM STUDENT WHERE StdFirstName = '"+FIRSTNAME+"' AND StdLastName = '"+LASTNAME+"'");

             if (rs.next()==true)

             {

               stmt.executeUpdate("UPDATE STUDENT SET StdSNN = '"+SSN+"'AND StdFirstName ='"+FIRSTNAME+"'AND StdLastName='"+LASTNAME+"' AND StdUserName='"+USERNAME+"' StdPassword='"+PASSWORD+"' AND StdCity='"+CITY+"' AND StdMajor='"+MAJOR +"' AND StdMinor ='"+MINOR+"'AND StdState='"+STATE+"' StdZip='"+ZIP+"' WHERE StdFirstName = '"+FIRSTNAME+"' AND StdLastName = '"+LASTNAME+"'");

             } else

             { stmt.executeUpdate ("INSERT INTO STUDENT (StdSSN, StdFirstName, StdLastName, StdUserName, StdPassword, StdCity, StdMajor, StdMinor, StdState, StdZip, StdClass, StdAdvisor, StdGPA VALUES ('"+SSN+"', '"+FIRSTNAME+"', '"+LASTNAME+"', '"+USERNAME+"','"+PASSWORD+"','"+CITY+"','"+MAJOR +"','"+MINOR +"','"+STATE+"','"+ZIP+"', '', '', '')");

                }

 

 

            } catch(SQLException e) {

                out.println("SQLException: " + e.getMessage() + "<BR>");

                 while((e = e.getNextException()) != null)

                 out.println(e.getMessage() + "<BR>");

            } catch(ClassNotFoundException e) {

               out.println("ClassNotFoundException: " + e.getMessage() + "<BR>");

            } finally {

              //Clean up resources, close the connection.

              if(conn != null) {

                  try {

                     conn.close();

                 } catch (Exception ignored) {

                 }

 

                }

 

            }

            out.println("</body> ");

            out.println("</html>");

            out.close();

        }

Open in new window

0
 

Author Comment

by:percivalevans
ID: 24061143
I'm new to adding a Java GUI interface to databases as you can see. The servlets that I'm using are from my partner for another class. Strange, he has on some servelts with the doGet and doPost like my original and like the one Derek suggested. Derek, I did copy and paste your servelt and corrected the errors. However when I tested I tested, no data entered the table. Maybe you guys have another suggest or maybe an ideal tutorial online? If I can get one to work, I can figure out my issue with one I think. No matter what thanks for your help and effort!
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 3

Expert Comment

by:kingsob
ID: 24065585
are you sure the action is correct for your form? it should post to whatever your servlet is called in your web.xml file

here is a simple example  
http://www.roseindia.net/servlets/ServletUserEnquiryForm.shtml
they have the html in a seperate file... however you could easily add this to the doGet method


Derek
0
 

Author Comment

by:percivalevans
ID: 24068296
I think we are almost there. I changed the post name and I get this error:

"SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client was: localhost:1521:xe[uncc on UNCC]"

I did a tsnping to my database and got a "Failed to resolved name" The "SID" is "xe[uncc on UNCC]" In netbeans 6.5, I have a connection to my database. I see the tables using the same connection descriptor in netbeans.
0
 
LVL 3

Assisted Solution

by:kingsob
kingsob earned 250 total points
ID: 24084026
im not familiar with oracle, so I don't think i'm going be able to help you with this problem

i looked around, and found this

http://www.adp-gmbh.ch/ora/misc/identifiers.html#sid

maybe it will help


Derek
0
 

Accepted Solution

by:
percivalevans earned 0 total points
ID: 24091822
I thank you for helping along with the servlet. Just in case anyone is reviewing this for troubleshooting, my issue was not fixed however I now know that my issue is with Oracle XE.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now