Problem with update

stressball · Nov 15, 2001

Hi,

I'm having real problems with this.
A user enters a permit number and clicks ok, the next page has a form with all the values pulled out from the permit table, the user needs to be able to add a key number, key issued by, date key issued, date key due. If the permit already contains a key, they won't add, if it does they can but don't have to (the alternative is an option to just print the form without adding a key).
The problem lies with updating the key table. I just get errors. I don't know when it is calling the update either, the entire page contents are listed right at the bottom of this.

Here is the query:
UPDATE permit_key
SET key_issued_by = 'varIssued', date_key_issued = 'varDateIssued', date_key_due = 'varDateDue', permit_id = 'varId'
WHERE key_code = 'varKey';

And the table structure:
Attribute | Type | Modifier
-----------------+-----------------------+----------
key_code | character varying(6) | not null
date_key_issued | date |
date_key_due | date |
key_issued_by | character varying(12) |
description | character varying(20) |
comments | character varying(30) |
permit_id | integer |
Index: permit_key_pkey

And some sample data:
user=> select * from permit_key;
key_code | date_key_issued | date_key_due | key_issued_by | description | comments | permit_id
----------+-----------------+--------------+---------------+-------------+----------+-----------
A12 | | | | | |
A13 | | | | | |
A14 | | | | | |
A15 | | | | | |
B1 | | | | | |
B3 | | | | | |
B4 | | | | | |
B5 | | | | | |
B2 | 15/11/2001 | 29/11/2001 | user1 | | | 721
A11 | 16/11/2001 | 30/11/2001 | test | | | 739
(10 rows)

Here is the code on the page:

PreparedStatement prKey = ConnprKey.prepareStatement("UPDATE permit_key SET key_issued_by = '"+ prKey__varIssued + "', date_key_issued = '"+ prKey__varDateIssued + "', date_key_due = '"+ prKey__varDateDue + "', permit_id = '"+ prKey__varId + "' WHERE key_code = '"+ prKey__varKey + "';&quot

;

And here is the error when it runs:

Error: 500
Location: /alter_permit.jsp
Internal Servlet Error:

javax.servlet.ServletException: ERROR: Bad date external representation 'null'

at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:459)
at _0002falter_0005fpermit_0002ejspalter_0005fpermit_jsp_10._jspService(_0002falter_0005fpermit_0002ejspalter_0005fpermit_jsp_10.java:294)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspServlet.java:130)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:282)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:812)
at org.apache.tomcat.core.ContextManager.service(ContextManager.java:758)
at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
at java.lang.Thread.run(Thread.java:484)

Root cause:
java.sql.SQLException: ERROR: Bad date external representation 'null'

at org.postgresql.Connection.ExecSQL(Connection.java:533)
at org.postgresql.jdbc2.Statement.execute(Statement.java:294)
at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:78)
at org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:122)
at _0002falter_0005fpermit_0002ejspalter_0005fpermit_jsp_10._jspService(_0002falter_0005fpermit_0002ejspalter_0005fpermit_jsp_10.java:126)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspServlet.java:130)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:282)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:812)
at org.apache.tomcat.core.ContextManager.service(ContextManager.java:758)
at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
at java.lang.Thread.run(Thread.java:484)

Any ideas would be most appreciated.

Regards,

Sharon Cowling

The whole alter_permit.jsp page:

<%@page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*"%>
<%@ include file="Connections/postgresql.jsp" %>
<%

String prKey__varId = null;
if(request.getParameter("permit_id&quot

!= null){ prKey__varId = (String)request.getParameter("permit_id&quot

;}

String prKey__varKey = null;
if(request.getParameter("key_num&quot

!= null){ prKey__varKey = (String)request.getParameter("key_num&quot

;}

String prKey__varIssued = null;
if(request.getParameter("issued&quot

!= null){ prKey__varIssued = (String)request.getParameter("issued&quot

;}

String prKey__varDateIssued = null;
if(request.getParameter("date_from&quot

!= null){ prKey__varDateIssued = (String)request.getParameter("date_from&quot

;}

String prKey__varDateDue = null;
if(request.getParameter("date_due&quot

!= null){ prKey__varDateDue = (String)request.getParameter("date_due&quot

;}

%>
<%
String rsAlterPermit__varId = "%";
if (request.getParameter("permit_id&quot

!=null) {rsAlterPermit__varId = (String)request.getParameter("permit_id&quot

;}
%>
<%
Driver DriverrsAlterPermit = (Driver)Class.forName(MM_postgresql_DRIVER).newInstance();
Connection ConnrsAlterPermit = DriverManager.getConnection(MM_postgresql_STRING,MM_postgresql_USERNAME,MM_postgresql_PASSWORD);
PreparedStatement StatementrsAlterPermit = ConnrsAlterPermit.prepareStatement("SELECT t.permit_id, t.person_id, t.date_from, t.date_to, t.location, t.purpose, t.subpurpose, t.vehicle_rego, t.vehicle_type, t.dogs, t.permit_conditions, t.other_info, t.issued_by, t.issue_date, t.firearms_licence, t.drivers_licence, k.key_code, k.key_issued_by, k.date_key_issued, k.date_key_due, p.firstname ||' '|| p.lastname as name, p.address1, p.address2, p.address3 FROM person p INNER JOIN (forest_permit t LEFT OUTER JOIN permit_key k ON t.permit_id = k.permit_id) ON p.person_id = t.person_id WHERE t.permit_id LIKE '" + rsAlterPermit__varId + "' &quot

;
ResultSet rsAlterPermit = StatementrsAlterPermit.executeQuery();
boolean rsAlterPermit_isEmpty = !rsAlterPermit.next();
boolean rsAlterPermit_hasData = !rsAlterPermit_isEmpty;
Object rsAlterPermit_data;
int rsAlterPermit_numRows = 0;
%>
<%
Driver DriverprKey = (Driver)Class.forName(MM_postgresql_DRIVER).newInstance();
Connection ConnprKey = DriverManager.getConnection(MM_postgresql_STRING,MM_postgresql_USERNAME,MM_postgresql_PASSWORD);
PreparedStatement prKey = ConnprKey.prepareStatement("UPDATE permit_key SET key_issued_by = '"+ prKey__varIssued + "', date_key_issued = '"+ prKey__varDateIssued + "', date_key_due = '"+ prKey__varDateDue + "', permit_id = '"+ prKey__varId + "' WHERE key_code = '"+ prKey__varKey + "';&quot

;
prKey.executeUpdate();
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">

</script>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p><font face="Arial, Helvetica, sans-serif" size="3" color="004080"><b>Add a
key to this permit and/or print a duplicate copy</b></font></p>
<form name="form1" action="">
<table width="75%" border="0">
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Permit
Id:</font></div>
</td>
<td width="220">
<input type="text" name="permit_id" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("permit_id&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td width="162" valign="baseline"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Person
Id:</font></td>
<td width="241">
<input type="text" name="textfield13" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("person_id&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Date
From:</font></div>
</td>
<td width="220">
<input type="text" name="textfield3" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("date_from&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td width="162" valign="baseline"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Name:</font></td>
<td width="241">
<input type="text" name="textfield14" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("name&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Date
To:</font></div>
</td>
<td width="220">
<input type="text" name="textfield4" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("date_to&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td width="162" valign="baseline"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Address:</font></td>
<td width="241">
<input type="text" name="textfield15" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("address1&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Location:</font></div>
</td>
<td width="220">
<input type="text" name="textfield2" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("location&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td width="162" valign="baseline"> </td>
<td width="241">
<input type="text" name="textfield16" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("address2&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Purpose:</font></div>
</td>
<td width="220">
<input type="text" name="textfield5" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("purpose&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td width="162" valign="baseline"> </td>
<td width="241">
<input type="text" name="textfield17" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("address3&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Sub
Purpose:</font></div>
</td>
<td width="220">
<input type="text" name="textfield6" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("subpurpose&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td width="162" valign="baseline"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Vehicle
Type:</font></td>
<td width="241">
<input type="text" name="textfield18" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("vehicle_type&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Dogs:</font></div>
</td>
<td width="220">
<input type="text" name="textfield7" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("dogs&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td width="162" valign="baseline"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Vehicle
Rego:</font></td>
<td width="241">
<input type="text" name="textfield19" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("vehicle_rego&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" valign="top" width="111">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Issued
By:</font></div>
</td>
<td width="220">
<input type="text" name="textfield8" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("issued_by&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td valign="baseline" width="162"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Drivers
Licence:</font></td>
<td width="241">
<input type="text" name="textfield20" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("drivers_licence&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" valign="top" width="111">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Issue
Date:</font></div>
</td>
<td width="220">
<input type="text" name="textfield9" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("issue_date&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td valign="baseline" width="162"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Firearms
Licence:</font></td>
<td width="241">
<input type="text" name="textfield21" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("firearms_licence&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Permit
Conditions:</font></div>
</td>
<td width="220">
<textarea name="textfield10"><%=(((rsAlterPermit_data = rsAlterPermit.getObject("permit_conditions&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%></textarea>
</td>
<td width="162" valign="baseline"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Other
Info:</font></td>
<td width="241">
<textarea name="textfield22"><%=(((rsAlterPermit_data = rsAlterPermit.getObject("other_info&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%></textarea>
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Key
Number:</font></div>
</td>
<td width="220">
<input type="text" name="key_num" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("key_code&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td width="162" valign="baseline"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Key
Issued By:</font></td>
<td width="241">
<input type="text" name="issued" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("key_issued_by&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Date
Key Issued:</font></div>
</td>
<td width="220">
<input type="text" name="date_from" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("date_key_issued&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
<td width="162" valign="baseline"><font face="Arial, Helvetica, sans-serif" size="2" color="004080">Date
Key Due:</font></td>
<td width="241">
<input type="text" name="date_due" value="<%=(((rsAlterPermit_data = rsAlterPermit.getObject("date_key_due&quot)==null || rsAlterPermit.wasNull())?"":rsAlterPermit_data)%>">
</td>
</tr>
<tr>
<td nowrap align="right" width="111" valign="baseline">
<div align="left"> </div>
</td>
<td width="220">
<input type="button" name="Button" value="Exit" onClick="MM_goToURL('parent','after_frame.jsp');return document.MM_returnValue">
</td>
<td valign="baseline" width="162">
<div align="center">
<input type="submit" name="Submit" value="Submit">
</div>
</td>
<td width="241"> </td>
</tr>
</table>
</form>
<p> </p>
<p> </p>
</body>
</html>
<%
rsAlterPermit.close();
ConnrsAlterPermit.close();
%>
<%
ConnprKey.close();
%>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Problem with update

stressball

Programmer

Similar threads

Part and Inventory Search

Sponsor