Why am I getting: [Oracle][ODBC][Ora]ORA-00904: invalid identifier

Oracle keeps giving me an invalid identifier error when I clearly have identified the variable.

  //get parameters from the request
   String custID=request.getParameter("cust_ID");
   String saleID=request.getParameter("sale_ID");
   String firstName=request.getParameter("first_Name");
   String mInitial=request.getParameter("mI");
   String lastName=request.getParameter("last_Name");
   String streetName=request.getParameter("street");
   String city=request.getParameter("city");
   String state=request.getParameter("state");
   String zipCode=request.getParameter("zip_Code");
   String DOB2=request.getParameter("DOB");
   String agentID=request.getParameter("agent_ID");
   String homePhone=request.getParameter("home_Phone");
   String cellPhone=request.getParameter("cell_Phone");
   String profession=request.getParameter("profession");
   String employer=request.getParameter("employer");
   String referrer=request.getParameter("referrer");


  query =
    "UPDATE customer"
    + " SET customer.cust_ID=custID, customer.sale_ID=saleID, customer.first_Name=firstName, customer.mI=mInitial, customer.last_Name=lastName, customer.street_Name=streetName, customer.city=city, customer.state=state, customer.zip_Code=zipCode,custome开发者_JAVA技巧r. DOB=DOB2, customer.agent_ID=agentID, customer.home_Phone=homePhone, customer.cell_Phone=cellPhone, customer.profession=profession, customer.employer=employer, customer.referrer=referrer"
    + " WHERE customer.cust_ID=custID " ;

  preparedStatement = conn.prepareStatement(query);


  preparedStatement.executeUpdate();

SQL TABLE

        CREATE TABLE customer
          (cust_ID      NUMBER          NOT NULL,
          sale_ID       NUMBER          NOT NULL,
          first_NameVARCHAR2(30)                NOT NULL,
          mI            VARCHAR2(2)         ,
          last_Name     VARCHAR2(50)        NOT NULL,
          street_Name       VARCHAR2(50)        ,
          city          VARCHAR2(30)        NOT NULL,
          state         VARCHAR2(50)        NOT NULL,
          zip_Code      VARCHAR2(5)     NOT NULL,
          DOB           DATE            ,
          agent_ID      NUMBER              ,
          home_Phone        VARCHAR2(12)        UNIQUE,         
          cell_Phone        VARCHAR2(12)        UNIQUE,
          profession        VARCHAR2(30)            ,
          employer      VARCHAR2(30)            ,
          referrer      VARCHAR2(30)            
   );       

Your code is not doing what you think it is. Look at this:

query =
"UPDATE customer"
+ " SET customer.cust_ID=custID, customer.sale_ID=saleID, customer.first_Name=firstName, customer.mI=mInitial, customer.last_Name=lastName, customer.street_Name=streetName, customer.city=city, customer.state=state, customer.zip_Code=zipCode,customer. DOB=DOB2, customer.agent_ID=agentID, customer.home_Phone=homePhone, customer.cell_Phone=cellPhone, customer.profession=profession, customer.employer=employer, customer.referrer=referrer"
+ " WHERE customer.cust_ID=custID "

The content of query at this point is exactly what will be sent to the database. JSP will not magically fill in custID, saleID (etc...) for you before sending the query to the database. Because of this, Oracle has no sweet clue what custID is (it certainly isn't the name of some other column in the customer table). Hence, you receive the invalid identifier error.

I think you were trying to do this:

query =
"UPDATE customer"
+ " SET customer.cust_ID=" + custID + ", customer.sale_ID=" + saleID + ...

Like duffymo mentioned, this is asking for serious SQL-injection trouble (just think of the values that the client could submit in order to hijack your SQL via the custID field). The better way is to use parameters on a PreparedStatement:

query =
"UPDATE customer"
+ " SET customer.cust_ID=?, customer.sale_ID=? ...";

PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, custID);
statement.setString(2, saleID);
statement.executeUpdate();

I'd recommend not using scriplets in your JSPs. Learn JSTL as quickly as you can.

The answer seems pretty obvious: your parameters are all Strings, but the Oracle schema has some Data and Number types. You've got to convert to the correct type when you INSERT.

This code is begging for a SQL injection attack. You don't do any binding or validation before you INSERT. You couldn't possibly be less secure than this. I hope you don't intend to use this site for anything on the web.

A better approach would take the scriptlet code out of the JSP, use only JSTL to write it, and introduce a servlet and some other layers to help with binding, validation, security, etc.

I think in the sql query you have entered space in between customer,DOB.

customer. DOB=DOB2