Urgent cant get update to work

I have a page that displays a number of fields with the patients entries in them already. he/she is giving option to update but I am getting the following error:
javax.servlet.ServletException: ORA-00001: unique constraint (W99436582.PK_TBLPATIENT) violated

Im not updating the primary key so shouldnt be affecting it. its been passed through text filed alrite. Here is code i have at moment:

<%
  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");
      
//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 + ")" );

      
  Statement stmt = connection.createStatement();
  //ResultSet rs = stmt.executeQuery("UPDATE Supervisor SET Password='"+NewPassword+"' where Username = '" + session.getValue("Username") +"'");
//stmt.execute( "UPDATE  TblPatient SET Values=" + Patientid + "," + name + "," + surname + "," + address + "," + telephone + "," + mobile + "," + ssno + "," + localdoctor + "," + gender + "," + age + ")" );
 ResultSet rs = stmt.executeQuery("UPDATE TblPatient set PatientFirstName = '" & name & "', PatientSurname = '" & surname & "', PatientAddress = '" &_
address & "', PatientTelephone = '" & telephone & "', PatientMobile = '" & mobile & "', PatientSSNO = '" & ssno & "' , PatientLocalDoctor = '" & localdoctor & "', PatientGender = '" & gender & "', PatientAge = '" & age & "' WHERE PatientID = '" &  Patientid & "'"
 


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

<%
connection.close();
%>
act1veAsked:
Who is Participating?
 
bloodredsunCommented:
You really should be using a Prepared Statement for this. It would be so much easier to code and to read, and also less error prone as the jdbc driver takes care of data types.

Check that the PatientID starts with the letter "p" (i think this is the culprit) and that all the parameters are filled and are not null.

Also checkn for any foreign key violations were your db schema checks for duplicates.
0
 
bloodredsunCommented:
You might be entering NULLS by accident with your request parameters

Use this for your request parameter assignments to prevent it.

String bert = request.getParameter("bert")!=null ? "'"+request.getParameter("bert")+"'":"''" ;

0
 
bloodredsunCommented:
but only for the Strings/VARCHARS....
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
act1veAuthor Commented:
Well i changed my code to disable the patientid field. seetting session instead and going to update where session = value in database
Here is code i have:

<%
  //String Patientid = "'" + request.getParameter("PatientID");
  String user  = (String)session.getAttribute( "Name" );
   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");
      
//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 + ")" );

      
  Statement stmt = connection.createStatement();
  //ResultSet rs = stmt.executeQuery("UPDATE Supervisor SET Password='"+NewPassword+"' where Username = '" + session.getValue("Username") +"'");
//stmt.execute( "UPDATE  TblPatient SET Values=" + Patientid + "," + name + "," + surname + "," + address + "," + telephone + "," + mobile + "," + ssno + "," + localdoctor + "," + gender + "," + age + ")" );
 ResultSet rs = stmt.executeQuery("UPDATE TblPatient set PatientFirstName = '" & name & "', PatientSurname = '" & surname & "', PatientAddress = '" &_
address & "', PatientTelephone = '" & telephone & "', PatientMobile = '" & mobile & "', PatientSSNO = '" & ssno & "' , PatientLocalDoctor = '" & localdoctor & "', PatientGender = '" & gender & "', PatientAge = '" & age & "' WHERE PatientID = '" &  user & "'"
 


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

<%
connection.close();
%>

Getting the following mistake now:
javax.servlet.ServletException: ORA-02290: check constraint (W99436582.CHECKPATIENT) violated

Here is code i have used to create table for patient:

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)
);

Will i still check for nulls?

0
 
act1veAuthor Commented:
The page with the update button has text fields with the values that are currently stored about that patient, for example:
<input name="PatientID" disabled="disabled" type="text" id="ID" VALUE="<%= session.getAttribute( "Name" ) %>">

the patient id is in a session so that must be passing to the next page so cant see where it is being violated.

What do you mean a prepared statement
0
 
act1veAuthor Commented:
PatientID is a foreign key in other tables but im not letting the primary key to be updated just the values associated with it
0
 
act1veAuthor Commented:
Actually im real sorry im after seeing where went wrong i never changed action on previous page to update.jsp instead of register.jsp
So stupid.
But getting the following error now:

An error occurred between lines: 35 and 64 in the jsp file: /sad/updatesuccess2.jsp

Generated servlet error:
G:\Apache\work\Standalone\localhost\_\sad\updatesuccess2$jsp.java:111: ')' expected.
                 ResultSet rs = stmt.executeQuery("UPDATE TblPatient set PatientFirstName = '" & name & "', PatientSurname = '" & surname & "', PatientAddress = '" &_


ResultSet rs = stmt.executeQuery("UPDATE TblPatient set PatientFirstName = '" & name & "', PatientSurname = '" & surname & "', PatientAddress = '" &_
address & "', PatientTelephone = '" & telephone & "', PatientMobile = '" & mobile & "', PatientSSNO = '" & ssno & "' , PatientLocalDoctor = '" & localdoctor & "', PatientGender = '" & gender & "', PatientAge = '" & age & "' WHERE PatientID = '" &  user & "'"
 
0
 
act1veAuthor Commented:
sorry again i put query all on one line and got this mistake

Generated servlet error:
G:\Apache\work\Standalone\localhost\_\sad\updatesuccess2$jsp.java:111: ')' expected.
                 ResultSet rs = stmt.executeQuery("UPDATE TblPatient set PatientFirstName = '" & name & "', PatientSurname = '" & surname & "', PatientAddress = '" & address & "', PatientTelephone = '" & telephone & "', PatientMobile = '" & mobile & "', PatientSSNO = '" & ssno & "' , PatientLocalDoctor = '" & localdoctor & "', PatientGender = '" & gender & "', PatientAge = '" & age & "' WHERE PatientID = '" &  user & "'"
                                                                                                                                                                                                                                                                                                                                                                                                                                        ^
0
 
act1veAuthor Commented:
ill accept your answer because you did kinda help me out. its really a new question im asking
0
 
bloodredsunCommented:
thanks act1ve

Why do you have & rather than + in your sql String with no closing bracket and semicolon?

   ResultSet rs = stmt.executeQuery("UPDATE TblPatient set PatientFirstName = '" & name & "',............& "'"

should be
   ResultSet rs = stmt.executeQuery("UPDATE TblPatient set PatientFirstName = '" + name + "'..........+ "'");

does this help?

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.