JSP send data to 4 database tables 2

rubertoga · Apr 5, 2003

Can one of you bright sparks help me out. Connecting to MS Access database and have problems with connection using JSP. This is the code, errors follow:

<%@ page language="java" contentType="text/html"
import="ShoppingBasket,Product,java.util.*"
errorPage="errorpage.jsp"%>

<jsp:useBean id="basket" class="ShoppingBasket" scope="session"/>

<html>
<head>
<title>Your Order Has Been Received</title>
</head>
<body>

<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver&quot

;
java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc

dbc:Novbase","","&quot

;

String query = "INSERT INTO Customer VALUES ('',?,?,?,?,?,?,?,?,?,?,?)";
java.sql.PreparedStatement Stmt = connection.prepareStatement(query);

Stmt.setString(1,request.getParameter("title&quot

);
Stmt.setString(2,request.getParameter("surname&quot

);
Stmt.setString(3,request.getParameter("firstname&quot

);
Stmt.setString(4,request.getParameter("address&quot

);
Stmt.setString(5,request.getParameter("address2&quot

);
Stmt.setString(6,request.getParameter("town&quot

);
Stmt.setString(7,request.getParameter("postcode&quot

);
Stmt.setString(8,request.getParameter("email&quot

);
Stmt.setString(9,request.getParameter("password&quot

);
Stmt.setString(10,request.getParameter("dob&quot

);
Stmt.setString(11,request.getParameter("tel_no&quot

);

Stmt.executeUpdate(query);

long customer_id = ((sun.jdbc.odbc.JdbcOdbcDriver)Stmt).getLastInsertID();

String query2 ="INSERT INTO Order VALUES ('','',?,?,'')";
java.sql.PreparedStatement Stmt2 = connection.PrepareStatement(query2);

Stmt2.setLong(1, customer_id);
Stmt2.setString(2, request.getParameter("total_value&quot

);

Stmt2.executeUpdate(query2);

long order_id = ((sun.jdbc.odbc.JdbcOdbcDriver)Stmt).getLastInsertID();

String query3 ="INSERT INTO OrderDetail VALUES ('',?,?,?)";

Enumeration products = basket.getProducts();
while(products.hasMoreElements())
{
Product product = (Product)products.nextElement();
Stmt3 = connection.prepareStatement(query3);
Stmt3.setLong(1,order_id);
Stmt3.setInt(2,Integer.parseInt(product.getId()));
Stmt3.setInt(3,product.getQuantity());
Stmt3.executeUpdate();
}

String query4="INSERT INTO Payment VALUES ('',?,?,?,?,?,?)";
java.sql.PreparedStatement Stmt4 = connection.PrepareStatement(query4);

Stmt4.setLong(1,order_id);
Stmt4.setString(2, request.getParameter("card_type&quot

);
Stmt4.setString(3, request.getParameter("card_number&quot

);
Stmt4.setString(4, request.getParameter("issue_no&quot

);
Stmt4.setString(5, request.getParameter("valid_from&quot

);
Stmt4.setString(6, request.getParameter("valid_to&quot

);

Stmt4.executeUpdate(query4);

Stmt.close();
Stmt2.close();
Stmt3.close();
Stmt4.close();
connection.close();
basket.emptyBasket();
%>
Thanks for your order. It will be processed within 24 hours.
Please note that your Order Number is No: <%= order_id %>
<br/><br/>
<a href=" <%= response.encodeURL("shop-products.jsp&quot

%> ">
<img src="images\toshop.gif" border="0" alt="Return to the Shop"></a>

</body>
</html>

These are the errors I get:

org.apache.jasper.JasperException: Unable to compile class for JSPNote: sun.tools.javac.Main has been deprecated.

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:111: Method getLastInsertID() not found in class sun.jdbc.odbc.JdbcOdbcDriver.
long customer_id = ((sun.jdbc.odbc.JdbcOdbcDriver)Stmt).getLastInsertID();
^

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:114: Method PrepareStatement(java.lang.String) not found in interface java.sql.Connection.
java.sql.PreparedStatement Stmt2 = connection.PrepareStatement(query2);
^

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:121: Method getLastInsertID() not found in class sun.jdbc.odbc.JdbcOdbcDriver.
long order_id = ((sun.jdbc.odbc.JdbcOdbcDriver)Stmt).getLastInsertID();
^

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:129: Undefined variable: Stmt3
Stmt3 = connection.prepareStatement(query3);
^

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:130: Undefined variable or class name: Stmt3
Stmt3.setLong(1,order_id);
^

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:131: Undefined variable or class name: Stmt3
Stmt3.setInt(2,Integer.parseInt(product.getId()));
^

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:132: Undefined variable or class name: Stmt3
Stmt3.setInt(3,product.getQuantity());
^

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:133: Undefined variable or class name: Stmt3
Stmt3.executeUpdate();
^

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:137: Method PrepareStatement(java.lang.String) not found in interface java.sql.Connection.
java.sql.PreparedStatement Stmt4 = connection.PrepareStatement(query4);
^

An error occurred between lines: 13 and 77 in the jsp file: /Novum/shop-postorder.jsp

Generated servlet error:
C:\Tomcat\jakarta-tomcat-4.0.4\work\Standalone\localhost\_\Novum\shop_0002dpostorder$jsp.java:150: Undefined variable or class name: Stmt3
Stmt3.close();
^
10 errors, 1 warning

at org.apache.jasper.compiler.Compiler.compile(Compiler.java:285)
at org.apache.jasper.servlet.JspServlet.loadJSP(JspServlet.java:548)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.loadIfNecessary(JspServlet.java:176)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:188)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)
at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
at java.lang.Thread.run(Thread.java:536)

sem · Apr 7, 2003

Try to declare Stmt3 as PreparedStatement at least. Regards, Dima

rubertoga · Apr 7, 2003

Ok thanks to Sem's help I now only have 2 errors relating to the :
getLastInsertID()

Someone has given me a tip that this is not in the java.sql API so does anybody know how to get an autonumber id out of an access database from a newly inserted record?

Thanks

palbano · Apr 7, 2003

>> does anybody know how to get an autonumber id out of an
>> access database

That is not supported other than performing a SELECT that includes that column as output.

-pete

rubertoga · Apr 7, 2003

Pete - thanks for the info. Since your last post I've been busy trying to do a work round getting the autonumber id.
Now I've hit a wall in that the order_id I need to get from the database is only uniquely identified by the customer_id and the date/time.

I've tried to select the right order_id dynamically by getting the date (short i.e.DD/MM/YY) from the server using a JSP custom tag and then putting this value into a hidden form field.

However, when I load the page everything gets input into the order table okay but the page displayed is blank i.e. it doesn't display the order_id or the form to input credit card details. I'm so close I can taste it but its driving me potty.

Code below, any ideas?

<%@ page language="java" contentType="text/html"
import="ShoppingBasket,Product,java.util.*"
errorPage="errorpage.jsp"%>

<jsp:useBean id="basket" class="ShoppingBasket" scope="session"/>

<html>
<head>
<title>Your Order Has Been Received</title>
</head>
<body>
<%
String customer_id = request.getParameter("customer_id&quot

;
String total_value = request.getParameter("total_value&quot

;
String order_date = request.getParameter("order_date&quot

;

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver&quot

;
java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc

dbc:Novbase","","&quot

;

java.sql.Statement Stmt = connection.createStatement();
String query = ("INSERT INTO Orders (customer_id, total_value) VALUES ("+customer_id+", "+total_value+&quot

&quot

;
Stmt.executeUpdate(query);

java.sql.Statement Stmt2 = connection.createStatement();
java.sql.ResultSet RS = Stmt2.executeQuery("SELECT order_id FROM Orders WHERE customer_id = "+customer_id+" AND time_date LIKE '"+order_date+"%'" );
while(RS.next())
{
String order_id = RS.getString("order_id&quot

;
%>
your order id is <%=order_id %>. Please input your credit card details in the form below:
<form name="form1" method="post" action="shop-postorder4.jsp">
<table width="90%" border="0" cellspacing="1" cellpadding="5">
<tr>
<td width="5%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="25%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Credit
Card Type</font></td>
<td width="70%"> <font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<select name="card_type">
<option value="AMERICAN EXPRESS">American Express</option>
<option value="MASTER CARD">Master Card</option>
<option value="SWITCH">Switch</option>
<option value="VISA">Visa</option>
</select>
*</font></td>
</tr>
<tr>
<td width="5%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="25%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Credit
Card No.</font></td>
<td width="70%"> <font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<input type="text" name="card_number" size=18 maxlength="18">
*</font></td>
</tr>
<tr>
<td width="5%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="25%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Issue
No. <font size="1"><br>
(Switch card holders<br>
only)</font></font></td>
<td width="70%"> <font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<input type="text" name="issue_no" size="2" maxlength="2">
</font></td>
</tr>
<tr>
<td width="5%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="25%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Valid
From</font></td>
<td width="70%"> <font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<input type="text" name="valid_from" size="4" maxlength="4">
e.g. 0502</font></td>
</tr>
<tr>
<td width="5%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="25%"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Validy
To</font></td>
<td width="70%"> <font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<input type="text" name="valid_to" size="4" maxlength="4">
* e.g. 0105</font></td>
</tr>
</table>
<input type="submit" name="Submit" value="Submit">
<input type="hidden" name="order_id" value="<%=order_id %>">
</form>
<br/>
<a href=" <%= response.encodeURL("shop-products.jsp&quot

%> ">
<img src="images\toshop.gif" border="0" alt="Return to the Shop"></a>
<%
}
RS.close();
Stmt.close();
Stmt2.close();
connection.close();
%>
</body>
</html>

palbano · Apr 7, 2003

I seriously suggest going into your database environment, in your case MS Access, and figuring out the query there. You really MUST know the SQL to produce before you build statements in any programming language dynamically.

There are MS Access forums here at Tek-Tips where members can likely help you with the SQL.

Once you know the SQL it might be simple to write the Java code to accomplish it.

-pete

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!

JSP send data to 4 database tables 2

rubertoga

Technical User

sem

Programmer

rubertoga

Technical User

palbano

Programmer

rubertoga

Technical User

palbano

Programmer

Similar threads

Part and Inventory Search

Sponsor