Urgent Help: Problem inserting to database

Have a registration page that takes in a number of values, but i am getting the following problem when i press submit:

javax.servlet.ServletException: ORA-01722: invalid number

The code i used to create the oracle table is as follwows:

CREATE TABLE TblPatient (
PatientID VARCHAR2(10) NOT NULL CONSTRAINT CheckPatient CHECK(PatientID LIKE ('p%')),
         PatientFirstName VARCHAR2(15) NOT NULL,
          PatientSurName VARCHAR2(15) NOT NULL,
        PatientAddress VARCHAR2(40) NOT NULL,
PatientTelephone NUMBER(10),
PatientMobile NUMBER(10),
PatientSSNO NUMBER(10) NOT NULL CONSTRAINT Unique_PatSSNO    UNIQUE,
         PatientLocalDoctor VARCHAR2(40),
PatientGender CHAR(1) NOT NULL CONSTRAINT Check_Gender  CHECK (PatientGender IN ('M','F')),
          PatientAge NUMBER(3) NOT NULL,
          CONSTRAINT PK_TblPatient PRIMARY KEY (PatientID)
);
COMMIT;

I can post the register.jsp page if ye need more
act1veAsked:
Who is Participating?
 
koppchaCommented:
No ..
Keep them i this

String Patientid = "'" + request.getParameter("PatientID") + "'";
    String name = "'" + request.getParameter("PatientFirstname") + "'";
    String surname = "'" + request.getParameter("PatientSecondname") + "'";
     String address = "'" + request.getParameter("PatientAddress") + "'";
    String telephone =  request.getParameter("PatientTelephone");
    String mobile  =  request.getParameter("PatientMobile");
    String ssno =  request.getParameter("PatientSSNO");
     String localdoctor = "'" + request.getParameter("PatientLocalDoctor") + "'";
    String gender = "'" + request.getParameter("PatientGender") + "'";
    String age = request.getParameter("Patientage");

0
 
act1veAuthor Commented:
my jsp code for register is as follows:

 <%@ pageimport ="java.sql.*"%>
<html>
<head>
<title>Registered</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<%

    Connection connection = null;
    try {
        // Load the JDBC driver
        String driverName = "oracle.jdbc.OracleDriver";
        Class.forName(driverName);
   
        // Create a connection to the database
        String serverName = "witnt07.wit.ie";
        String portNumber = "1521";
        String sid = "orawit";

        String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
        String username = "W99436582";
        String password = "YZQBAF";
        connection = DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException e) {
        out.println( "Could not find the database driver");
    } catch (SQLException e) {
      e.printStackTrace();
        out.println( "Could not connect to the database");
    }
      
%>
<%


    String Patientid = "'" + request.getParameter("PatientID") + "'";
    String name = "'" + request.getParameter("PatientFirstname") + "'";
    String surname = "'" + request.getParameter("PatientSecondname") + "'";
      String address = "'" + request.getParameter("PatientAddress") + "'";
    String telephone = "'" + request.getParameter("PatientTelephone") + "'";
    String mobile  = "'" + request.getParameter("PatientMobile") + "'" ;
    String ssno = "'" + request.getParameter("PatientSSNO") + "'";
      String localdoctor = "'" + request.getParameter("PatientLocalDoctor") + "'";
    String gender = "'" + request.getParameter("PatientGender") + "'";
    String age = "'" + request.getParameter("Patientage") + "'";

    Statement stmt = connection.createStatement();
    stmt.execute( "INSERT INTO TblPatient(PatientID,PatientFirstName,PatientSurName,PatientAddress,PatientTelephone,PatientMobile,PatientSSNO,PatientLocalDoctor,PatientGender,PatientAge)Values(" + Patientid + "," + name + "," + surname + "," + address + "," + telephone + "," + mobile + "," + ssno + "," + localdoctor + "," + gender + "," + age + ")" );


{
    connection.close();
}

%>

<%


            String redirectURL = "/sad/complete.jsp";
        response.sendRedirect(redirectURL);
            
%>

</body>
</html>
0
 
koppchaCommented:
can you post the entire error
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
koppchaCommented:
You do not have to give quotes again for all the statements
String Patientid = "'" + request.getParameter("PatientID") + "'";
jsut give

String Patientid =  request.getParameter("PatientID");
0
 
koppchaCommented:
oh ok i understand why you are keeping the quotes.
I can see that for Number type also you are keeping the quotes
what you can do is keep the quotes for the varchar type and remove the quotes for the number type
0
 
act1veAuthor Commented:
here is page that shows with error

type Exception report

message Internal Server Error

description The server encountered an internal error (Internal Server Error) that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: ORA-01722: invalid number

      at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:471)
      at org.apache.jsp.registered$jsp._jspService(registered$jsp.java:136)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)
      at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
      at java.lang.Thread.run(Thread.java:536)


root cause

java.sql.SQLException: ORA-01722: invalid number

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java)
      at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java)
      at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
      at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java)
      at org.apache.jsp.registered$jsp._jspService(registered$jsp.java:108)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)
      at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
      at java.lang.Thread.run(Thread.java:536)

0
 
koppchaCommented:
Change from

String telephone = "'" + request.getParameter("PatientTelephone") + "'";
String mobile  = "'" + request.getParameter("PatientMobile") + "'" ;
 String ssno = "'" + request.getParameter("PatientSSNO") + "'";
String age = "'" + request.getParameter("Patientage") + "'";

Change to

String telephone =  request.getParameter("PatientTelephone") ;
String mobile  =  request.getParameter("PatientMobile") ;
 String ssno = request.getParameter("PatientSSNO") ;
String age = request.getParameter("Patientage") ;

   
0
 
act1veAuthor Commented:
After getting rid of quotes and have it like this:

 String Patientid = "'" + request.getParameter("PatientID") + "'";
    String name = "'" + request.getParameter("PatientFirstname") + "'";
    String surname = "'" + request.getParameter("PatientSecondname") + "'";
      String address = "'" + request.getParameter("PatientAddress") + "'";
    String telephone = "'" + request.getParameter("PatientTelephone");
    String mobile  = "'" + request.getParameter("PatientMobile");
    String ssno = "'" + request.getParameter("PatientSSNO");
      String localdoctor = "'" + request.getParameter("PatientLocalDoctor") + "'";
    String gender = "'" + request.getParameter("PatientGender") + "'";
    String age = "'" + request.getParameter("Patientage");

I get the following:

javax.servlet.ServletException: ORA-00917: missing comma
0
 
act1veAuthor Commented:
i have entered in the following
String Patientid = "'" + request.getParameter("PatientID") + "'";
    String name = "'" + request.getParameter("PatientFirstname") + "'";
    String surname = "'" + request.getParameter("PatientSecondname") + "'";
     String address = "'" + request.getParameter("PatientAddress") + "'";
    String telephone =  request.getParameter("PatientTelephone");
    String mobile  =  request.getParameter("PatientMobile");
    String ssno =  request.getParameter("PatientSSNO");
     String localdoctor = "'" + request.getParameter("PatientLocalDoctor") + "'";
    String gender = "'" + request.getParameter("PatientGender") + "'";
    String age = request.getParameter("Patientage");

But getting the following problem

javax.servlet.ServletException: ORA-01400: cannot insert NULL into ("W99436582"."TBLPATIENT"."PATIENTAGE")

I did enter an age on the form
0
 
act1veAuthor Commented:
yes i see the problem and it worked. thanks i would never have figured it out
0
 
koppchaCommented:
Take It Easy

Thanks for the Grade :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.