HTML Form/Servlet Problem

[Gill Clover]

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]

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]

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]

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]

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]

Again, try this:
updateResult = stmnt.executeUpdate("INSERT INTO tbl_experiment (Forename, Country)" +
"VALUES ('" + formForename + "', '" + formCountry + "'))";

[Malhar Barai]

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]

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