Click here to Skip to main content
14,391,232 members
Rate this:
Please Sign up or sign in to vote.
See more:
My code does not compile when I inserted the other tables and Im not really good at SQL statements and such. Is it possible if I can use a single query for inserting into multiple tables?

Code below

What I have tried:

<%@ page import ="java.sql.*" %>
<%
    String id = request.getParameter("accid");
    String surname = request.getParameter("SURNAME");
    String firstname = request.getParameter("FIRSTNAME");
    String middlename = request.getParameter("MIDDLENAME");
    String nameextension = request.getParameter("NAMEEXTENSION");
    String birthdate = request.getParameter("BIRTHDATE");
    String birthplace = request.getParameter("BIRTHPLACE");
    String sex= request.getParameter("SEX");
    String civilstatus= request.getParameter("CIVILSTATUS");
    String weight = request.getParameter("WEIGHT");
    String height = request.getParameter("HEIGHT");
    String bloodtype = request.getParameter("BLOODTYPE");
    String gsisno = request.getParameter("GSISNO");
    String pagibigidno = request.getParameter("PAGIBIGIDNO");
    String philhealthno = request.getParameter("PHILHEALTHNO");
    String sssno = request.getParameter("SSSNO");
    String tinno = request.getParameter("TINNO");
    String agencyemployeeno = request.getParameter("AGENCYEMPLOYEENO");
    String citizenship = request.getParameter("CITIZENSHIP");
    String residentialaddress = request.getParameter("RESIDENTIALADDRESS");
    String razipcode = request.getParameter("RAZIPCODE");
    String permanentaddress = request.getParameter("PERMANENTADDRESS");
    String pazipcode = request.getParameter("PAZIPCODE");
    String telephoneno = request.getParameter("TELEPHONENO");
    String mobileno = request.getParameter("MOBILENO");
    String emailaddress = request.getParameter("EMAILADDRESS");
    String spousefirstname = request.getParameter("spouseFIRSTNAME");
    String spousemiddlename = request.getParameter("spouseMIDDLENAME");
    String spousesurname = request.getParameter("spouseSURNAME");
    String spouseoccupation = request.getParameter("spouseOCCUPATION");
    String spouseebn = request.getParameter("spouseEBN");
    String spouseba = request.getParameter("spouseBA");
    String spousetelephone = request.getParameter("spouseTELEPHONE");
    String childname = request.getParameter("childNAME");
    String childbirthdate = request.getParameter("childBIRTHDATE");
    String fatherfirstname = request.getParameter("fatherFIRSTNAME");
    String fathermiddlename = request.getParameter("fatherMIDDLENAME");
    String fathersurname = request.getParameter("fatherSURNAME");
    String fathernameextension = request.getParameter("fatherNAMEEXTENSION");
    String motherfirstname = request.getParameter("motherFIRSTNAME");
    String mothermiddlename = request.getParameter("motherMIDDLENAME");
    String mothersurname = request.getParameter("motherSURNAME");
 
    String graduate = request.getParameter("gsname");
    String college = request.getParameter("collegename");
  
 
 
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/employee_database",
            "root", "");
    PreparedStatement ps = null;
 
 
    String query= "INSERT INTO personal_information(SURNAME, FIRSTNAME, MIDDLENAME, NAMEEXTENSION, BIRTHDATE, BIRTHPLACE, SEX, CIVILSTATUS, WEIGHT, HEIGHT, BLOODTYPE, GSISNO, PAGIBIGIDNO, PHILHEALTHNO, SSSNO, TINNO, AGENCYEMPLOYEENO, CITIZENSHIP, RESIDENTIALADDRESS, RAZIPCODE, PERMANENTADDRESS, PAZIPCODE, TELEPHONENO, MOBILENO, EMAILADDRESS, spouseFIRSTNAME, spouseMIDDLENAME, spouseSURNAME, spouseOCCUPATION, spouseEBN, spouseBA, spouseTELEPHONE, childNAME, childBIRTHDATE, fatherFIRSTNAME, fatherMIDDLENAME, fatherSURNAME, fatherNAMEEXTENSION, motherFIRSTNAME, motherMIDDLENAME, motherSURNAME) "
          + "VALUES('"+surname+"','"+firstname+"','"+middlename+"','"+nameextension+"','"+birthdate+"','"+birthplace
                +"','"+sex+"','"+civilstatus+"','"+weight+"','"+height+"','"+bloodtype+"','"+gsisno+"','"+pagibigidno+"','"+philhealthno+"','"+sssno+"','"+tinno+"','"+agencyemployeeno+"','"+citizenship+"','"+residentialaddress+"','"+razipcode+"','"+permanentaddress+"','"+pazipcode+"','"+telephoneno+"','"+mobileno+"','"+emailaddress+"','"+spousefirstname+"','"+spousemiddlename+"','"+spousesurname+"','"+spouseoccupation+"','"+spouseebn+"','"+spouseba+"','"+spousetelephone+"','"+childname+"','"+childbirthdate+"','"+fatherfirstname+"','"+fathermiddlename+"','"+fathersurname+"','"+fathernameextension+"','"+motherfirstname+"','"+mothermiddlename+"','"+mothersurname+"')"; 
"INSERT INTO graduatestudies(gsname) " + "VALUES ('"+graduate+"')"; 
"INSERT INTO college(collegename) " + "VALUES('"+college+"')";
            ps = con.prepareStatement(query);
   
   try{
        int i = ps.executeUpdate(query);
 
        if (i > 0){
         session.setAttribute("loginid", surname);
            response.sendRedirect("adminclientmanagement.jsp");
        } else {
            response.sendRedirect("clientsignuperror.jsp");
        }
    } catch(SQLIntegrityConstraintViolationException duplicate){
        response.sendRedirect("clientsignuperror.jsp");
    }
%>
Posted
Updated 25-Jun-18 22:51pm
Comments

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

No wonder you have problems, that is such a bad way to use SQL. Use proper paramaterised queries so you can see the actual value of each parameter being passed to the statement. You also have two text strings beginning "INSERT", which have no meaning in Java.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100