[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Urgent Help: Problem inserting to database

Posted on 2005-04-20
11
Medium Priority
?
833 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:act1ve
  • 6
  • 5
11 Comments
 

Author Comment

by:act1ve
ID: 13827177
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
 
LVL 8

Expert Comment

by:koppcha
ID: 13827241
can you post the entire error
0
 
LVL 8

Expert Comment

by:koppcha
ID: 13827267
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Expert Comment

by:koppcha
ID: 13827313
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
 

Author Comment

by:act1ve
ID: 13827316
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
 
LVL 8

Expert Comment

by:koppcha
ID: 13827337
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
 

Author Comment

by:act1ve
ID: 13827345
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
 
LVL 8

Accepted Solution

by:
koppcha earned 2000 total points
ID: 13827371
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
 

Author Comment

by:act1ve
ID: 13827447
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
 

Author Comment

by:act1ve
ID: 13827518
yes i see the problem and it worked. thanks i would never have figured it out
0
 
LVL 8

Expert Comment

by:koppcha
ID: 13828120
Take It Easy

Thanks for the Grade :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I came across an unsolved Outlook issue and here is my solution.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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