Forum:

Servlets

Error in posting data to sql

Ranch Hand

Posts: 153

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,

I've the below data in my HTML.

<body>
	<div class="container">
	<form name="main" action="Insert_Record" method="post">
		<table class="bordered">
			<tr>
				<td>Serial Number</td>
				<td><input type="text" name="SerID" id="SerID"></td>
				<td colspan="2"> </td>
				<td colspan="2"> </td>
			</tr>
			<tr>
				<td>Shipment ID</td>
				<td><input type="text" name="sid" id="sid"></td>
				<td>Product NAME</td>
				<td><input type="text" name="desc" id="desc"></td>
				<td>Phoenix Pub Code</td>
				<td><input type="text" name="pubcode" id="pubcode"></td>
			</tr>
			<tr>
				<td>Product Type</td>
				<td><select name="ProdType" id="ProdType">
						<option value="" disabled selected>Select your option</option>
						<option value="Looseleaf - Update">Looseleaf - Update</option>
						<option value="Pamphlet">Pamphlet</option>
				</select></td>
				<td colspan="4"> </td>
			</tr>
			<tr>
				<td>Date Request received</td>
				<td><input type="text" id="DateRec" name="DateRec"></td>
				<td colspan="2">Time Received</td>
				<td colspan="2"><input type="text" id="TimeRec" name="TimeRec"></td>
			</tr>
			<tr>
				<td>Units</td>
				<td><input type="text" name="Units" id="Units"></td>
				<td colspan="2">VWR Due Date</td>
				<td colspan="2"><input type="text" name="VWR" id="VWR"></td>
			</tr>
			<tr>
				<td>AE Due Date</td>
				<td><input type="text" id="AEDue" name="AEDue"></td>
				<td colspan="2">Assign by Date</td>
				<td colspan="2"><input type="text" id="AsByDate"
					name="AsByDate"></td>
			</tr>
			<tr>
				<td colspan="6" align="center"><input type="submit"
					value="Add Screen" id="addscr" name="addscr" onclick="addscr()" />

</td>
			</tr>
		</table>
		</form>
	</div>
	<script type="text/javascript">
		$(function() {
			$("#DateRec").datepicker({
				dateFormat : "mm-dd-y"
			}).val();
		});
		$(function() {
			$("#VWR").datepicker({
				dateFormat : "mm-dd-y"
			}).val();
		});
		$(function() {
			$("#AEDue").datepicker({
				dateFormat : "mm-dd-y"
			}).val();
		});
		$(function() {
			$("#AsByDate").datepicker({
				dateFormat : "mm-dd-y"
			}).val();
		});
	</script>
</body>

and i'm trying to post it to Insert_Record servlet. and it is as below.

try{
	
SimpleDateFormat formater = new SimpleDateFormat("EE MMM dd");
String a=request.getParameter("sid");
String b=request.getParameter("desc");
String c=request.getParameter("pubcode");
String d=request.getParameter("ProdType");
Date e=(Date) formater.parse(request.getParameter("DateRec"));
String f=request.getParameter("TimeRec");
String g=request.getParameter("Units");
Date h=(Date) formater.parse(request.getParameter("VWR"));
Date i=(Date) formater.parse(request.getParameter("AEDue"));
Date j=(Date) formater.parse(request.getParameter("AsByDate"));
String k=request.getParameter("SerID");
/* String sql="insert into Welcome_Page (SHIPMENT_ID_/_IDS, PRODUCT_CODE, PRODUCT_NAME,
 PRODUCT_TYPE, UNIT_COUNT, RECEIVED_DATE, RECEIVED_TIME, VWR_DUE_DATE, AE_DUE_DATE,
 ASSIGN_BY_DATE,SERIAL_NUMBER) values (?,?,?,?,?,?,?,?,?,?,?)"; */
String sql="insert into Welcome_Page(SHIPMENT_ID_/_IDS, PRODUCT_CODE, PRODUCT_NAME,
PRODUCT_TYPE, UNIT_COUNT, RECEIVED_DATE, RECEIVED_TIME, VWR_DUE_DATE, AE_DUE_DATE, 
ASSIGN_BY_DATE,SERIAL_NUMBER) values ("+a+", '"+c+"', "+b+", '"+d+"', "+g+", str_'"
+e+"', '"+f+"', '"+h+"', '"+i+"', '"+j+"', "+k+")";
//ps=con.prepareStatement(sql);
/* ps.setString(1,a);
ps.setString(2,c);
ps.setString(3,b);
ps.setString(4,d);
ps.setString(5,g);
ps.setString(6,e);
ps.setString(7,f);
ps.setString(8,h);
ps.setString(9,i);
ps.setString(10,j);
ps.setString(11,k);

*/
 System.out.println(sql);
 /* 
ps.executeQuery();
con.commit(); */
 }
catch(Exception e){
	System.out.println(e);
}

I'm getting the below error.

if i use d-MM-yyy, the output is

but i want the date in output to be.

below is how my data base is like.

please let me know how can i get this.

Thanks,
Rakesh

DB.png

Ulf Dittmer

Rancher

Posts: 43081

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

The issue of data storage is unrelated to the issue of data display. You will need to provide the DB with a data format it can store.

The first thing you should do is switch back to using the PreparedStatement. It will do many things for you, including the handling data types that you're currently struggling with. You would not use "setString" for date and timestamp fields, but methods that are appropriate for those data types.

Another thing PreparedStatement does is to protect you from SQL injection attacks - your current code is wide open to those attacks.

Rakesh Keerthi

Ranch Hand

Posts: 153

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Ulf,

Thanks for the quick response, here first of all, i'm trying to print the query and lateron, once i get the date in the format that i want, i'll change the setString to setDate or corresponding things, her the date the query i'm printing is not in the proper format that i want.

but when i get this type of date in output it is throwing the exception as mentioned above.

Ulf Dittmer

Rancher

Posts: 43081

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

What is the "output"? I don't see code that would output dates anywhere. So I'm not clear which line of code produces the exception you mention.

If you're talking about printing the query, then that calls the Date.toString method, the output of which is irrelevant to either storing the date in the DB, or displaying the date in a web page. (Date objects have no inherent format, they're just data.)

So I repeat: start by using PreparedStatement so that the storing in the DB works.

Rakesh Keerthi

Ranch Hand

Posts: 153

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Below screenshots shows the input and the query generated.

The red boxes represents the dates.

Input.JPG

Query.JPG

Ulf Dittmer

Rancher

Posts: 43081

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

That doesn't look like valid SQL - what's the "str_" doing there? That's another example of how a PreparedStatement would help.

Rakesh Keerthi

Ranch Hand

Posts: 153

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Sorry, that was a typo in my sql string. updated it, but still the same output

Query.JPG

Dave Tolls

Rancher

Posts: 4801

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

As Ulf has said, you are mixing displaying your dates up with how they are stored.
Your Date fields in the database do not have a format...just as java.util.Date's do not have a format.

So parse the dates supplied by the user into a Date and use a prepared statement to store that in the relevant date column in your table.
When you read it out for display then format the returned Date into the form you require.

Ulf Dittmer

Rancher

Posts: 43081

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

So what date format are you now using? For "09-10-14", it should be something like "MM-dd-yy", assuming that the month comes first.

Rakesh Keerthi

Ranch Hand

Posts: 153

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Yes that is right when i use

the output is

when i use

the output is

Ulf Dittmer

Rancher

Posts: 43081

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

So all is good now?

Rakesh Keerthi

Ranch Hand

Posts: 153

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Nop. I'm getting the Exception if i change the format.

Ulf Dittmer

Rancher

Posts: 43081

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Now that you know what the correct format is, why would you switch back to the wrong one?

Rakesh Keerthi

Ranch Hand

Posts: 153

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

when i'm trying to use the correct format it doesn't show the correct output in the query, if the format is modified, then i get the correct format with an exception

Ulf Dittmer

Rancher

Posts: 43081

posted 10 years ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

I'll say it again: the way the Date object is printed in that SQL string is completely irrelevant. If you think it is then you have misconceptions about how Date objects and their parsing and formatting works. As I've also said, you should be using PreparedStatement (and not care about what SQL is generated). What matters is that you are able to store it in the DB, no?

How dates are formatted in the input and output HTML pages are separate matters that have nothing to do with how the date is stored in the DB.

Rakesh Keerthi

Ranch Hand

Posts: 153

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Got it thank you very much. :-)

Did you see how Paul cut 87% off of his electric heat bill with 82 watts of micro heaters?