File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Servlets and the fly likes HTML Form/Servlet Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "HTML Form/Servlet Problem" Watch "HTML Form/Servlet Problem" New topic
Author

HTML Form/Servlet Problem

Gill Clover
Greenhorn

Joined: Aug 10, 2002
Posts: 28
Hi there,
I�m having a problem with a servlet I�m developing and hope that someone can point out what I think must be a small error in my code.
The following details are most likely irrelevant, but�I�m using JDeveloper and its embedded OC4J server on Windows XP.
OK, here�s my problem:
I have successfully connected my servlet to my Oracle database (running on the same machine) and can use (hardwired into my Java) SQL SELECT and INSERT statements successfully also. I am trying to use HTML forms as my front end, the servlet as middleware and the Oracle database as my back end. I have successfully coded my HTML forms so that the servlet can accept user input via the forms.
However, my problem arises when I try to use an INSERT statement in my servlet which is supposed to take input from my HTML forms and insert it into my database: the code compiles fine and as I mentioned before, if I hardwire values for the insert statement into my servlet code, it works. I get a SQL Exception ORA-01722: invalid number when my insert statement is coded to take data given to it by my forms, code snippet below (all within a doGet method.

The code that works:
ResultSet result = null;
result = stmnt.executeQuery("SELECT * FROM tbl_experiment");
�etc

The code that doesn�t work:
int updateResult = 0;
// Declare Java variables that hold HTML forms input
String formForename = request.getParameter("Form_Forename");
String formIDNo = request.getParameter("Form_ID_No");
String formCountry = request.getParameter("Form_Country");
// this works when the values are hard-wired
// Execute an SQL update:
updateResult = stmnt.executeUpdate("INSERT INTO tbl_prototype (Forename, ID_No, Country)" +
"VALUES ('formForename', 'formIDNo', 'formCountry')" );

Many thanks to anyone who can help � this has been driving me crazy!!
Gillian Klee
Jose Velarde
Ranch Hand

Joined: Sep 11, 2002
Posts: 78
Why are you hardcoding the values you got from the form? Try this:
updateResult = stmnt.executeUpdate("INSERT INTO tbl_prototype (Forename, ID_No, Country)" +
"VALUES (" + formForename + ", " + formIDNo + ", " + formCountry + "))";
Gill Clover
Greenhorn

Joined: Aug 10, 2002
Posts: 28
Thanks for your reply, unfortunately, it still didn't help, I got the following error:
ORA-00984: column not allowed here
I'm finding these error messages very cryptic!
In reply to your question, I tried hardwiring the values just to make sure that interaction between my servlet and Oracle was working alright; it was just a way of narrowing down the error possibilities. Sorry if that was unclear.
Gillian
Maulin Vasavada
Ranch Hand

Joined: Nov 04, 2001
Posts: 1871
hi
it seems u want to insert a number but inserting a string instead....
i believe the formIDNo is in a number and formCountry and formForename are strings in the database.
u 've to do following after u get formIDNo as a string form request.getParameter() method,
int newFormID = new Integer(formIDNo).intValue();
or
int newFormID = new Long(formIDNo).longValue(); if it is really compatible to java 's long datatype...
then run like,
INSERT ...VALUES ('"+formForename+"',"+newFormID+",'"+formCountry+"'");
notice single quotes i put around the string values that needs to be inserted intot he database which has type char, varchar or something text type... and i didn't use anything for the newFormID as it is NUMBER datatype in database...
hih,
regards
maulin
Gill Clover
Greenhorn

Joined: Aug 10, 2002
Posts: 28
Thanks again for that, althoughit still hasn't solved my problem. You are right about formID_No being a NUMBER datatype in Oracle, although for some reason when I put hardwired values in and treated that value as a string, Oracle accepted it! Couldn't figure out why it didn't throw up an error at that.
I've abandoned trying to insert that column and have been focussing on inserting just the two strings, formForename and formCountry. With this code:
updateResult = stmnt.executeUpdate("INSERT INTO tbl_experiment (Forename, Country)" +
"VALUES ('formForename', 'formCountry')" );
I don't get an Oracle error but do get a NullPointerException), but when I input values from my HTML forms, 'formForename' and 'formCountry' are the values that get put into my database.
I have looked in many books on JDBC and for tutorials online but can't find anything that demonstrates how to insert the values that those Strings hold (I know that the values I enter through my forms do go into my Strings formForename and formCountry because I can print them out).

Can anyone enlighten me please?
Gillian
Jose Velarde
Ranch Hand

Joined: Sep 11, 2002
Posts: 78
Again, try this:
updateResult = stmnt.executeUpdate("INSERT INTO tbl_experiment (Forename, Country)" +
"VALUES ('" + formForename + "', '" + formCountry + "'))";
Malhar Barai
Author
Ranch Hand

Joined: Aug 17, 2001
Posts: 399
try parsing your entry..
int frmIdNo = Integer.parseInt(request.getParameter("Form_ID_No"));
& then use the insert stuff...like..
INSERT ...VALUES ('"+formForename+"',"+frmIdNo+",'"+formCountry+"'");
hope this helps
malhar
Gill Clover
Greenhorn

Joined: Aug 10, 2002
Posts: 28
Thanks very much for everyone's help, my code now works! I haven't tried putting my formID_No back into my Insert statement, but now I know that I can at least get the string into my database. Off to try the number now...
Thanks again, you've saved me from eternal frustration
Gillian
Originally posted by Jose Velarde:
Again, try this:
updateResult = stmnt.executeUpdate("INSERT INTO tbl_experiment (Forename, Country)" +
"VALUES ('" + formForename + "', '" + formCountry + "'))";
 
jQuery in Action, 2nd edition
 
subject: HTML Form/Servlet Problem