Problem with Access prepared statements in JSP
HI, Im getting an error trying to insert a record into Access, any ideas whats going wrong?
Heres the error message and code:
javax.servlet.ServletException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:386)
at myproject.easyshop._0002fmyproject_0002feasyshop_0002fshoppostorder_0002ejspshoppostorder_jsp_5._jspService(_0002fmyproject_0002feasyshop_0002fshoppostorder_0002ejspshoppostorder_jsp_5.java:200)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:126)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.runtime.JspServlet$JspServletWrapper.service(JspServlet.java:174)
at org.apache.jasper.runtime.JspServlet.serviceJspFile(JspServlet.java:261)
at org.apache.jasper.runtime.JspServlet.service(JspServlet.java:369)
at com.borland.jbuilder.webserverglue.tomcat.jsp.JspLoaderEcho.service(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.handleRequest(ServletWrapper.java:503)
at org.apache.tomcat.core.ContextManager.service(ContextManager.java:559)
at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:160)
at org.apache.tomcat.service.TcpConnectionThread.run(SimpleTcpEndpoint.java:338)
at java.lang.Thread.run(Thread.java:484)
<%@ page language="java" contentType="text/html" import="easyshop.*, java.util.*,java.sql.* " %>
<jsp:useBean id="basket" class="easyshop.ShoppingBasket" scope="session" />
<HTML>
<HEAD>
<TITLE>
Your order has been recieved
</TITLE>
</HEAD>
<BODY>
<%
//sql stuff here
//Sets up all the parameters for connections etc
String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
String protocolHeader = "jdbc:odbc:";
String dbName = "easyshop";
String user = "";
String password = "";
// 1. Load in the driver
Class.forName(jdbcDriver);
// 2. Get a connection
Connection cn =
DriverManager.getConnection(protocolHeader+dbName, user, password);
// 3. Create a query string, assiging a column pattern to the variables
String query = "INSERT INTO orders VALUES ('',?,?,?,?,?,?,?,?)";
// 4. Create a statement object to send SQL queries
java.sql.PreparedStatement stmt = cn.prepareStatement(query);
// 5. Get all form attributes values from the request header and insert them
// into the statement object in the defined pattern
stmt.setString(1, request.getParameter("firstname"));
stmt.setString(2, request.getParameter("surname"));
stmt.setString(3, request.getParameter("address"));
stmt.setString(4, request.getParameter("city"));
stmt.setString(5, request.getParameter("postcode"));
stmt.setString(6, request.getParameter("card_number"));
stmt.setString(7, request.getParameter("card_type"));
stmt.setString(8, request.getParameter("totalvalue"));
// 6. Add the records to the orders table
System.out.println("trying to add the order now");
stmt.executeUpdate();
System.out.println("Managed to write the order");
// 7. get an order number incremented from last order
// need to figure this out ****long order_id = ((sun.jdbc.odbc.JdbcOdbcDriver.p
// Create a atatement to get the maximum order number from file (this wouldnt work if it were
// a multi user system but OK for now.
Statement getmaxstatement = cn.createStatement();
ResultSet maxrs = getmaxstatement.executeQuery("select max([order_id]) from orders");
maxrs.next();
long order_id = maxrs.getLong(1);
getmaxstatement.close();
maxrs.close();
// write the actual ordered products to the database
// 8. assign SQL query and column pattern to variable
String order_info_query = "INSERT INTO order_info VALUES ('', ?,?,?)";
// 9. Retrieve the products from the bsket bean
Enumeration products = basket.getProducts();
// 10. Add a record for each DIFFERENT selected item to the table
while(products.hasMoreElements())
{
Product product = (Product)products.nextElement();
stmt = cn.prepareStatement(order_info_query);
stmt.setLong(1,order_id);
stmt.setInt(2,Integer.parseInt(product.getId()));
stmt.setInt(3,product.getQuantity());
stmt.execute();
}
// 11. Clean up the objects
stmt.close();
cn.close();
// 12. Empty the basket
basket.emptyBasket();
%>
Thanks for your order.
Your order number is <%= order_id %>
<a href="<%= response.encodeURL("shopproducts.jsp") %> ">
<img src="images\toshop.jpg" border="0"> </a>
</BODY>
</HTML>
