WHAT I AM DOING WRONG? Please help

Code:

<%
String connectionURL = “jdbc:mysql://localhost:3306/xxx”;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
java.sql.Connection connection = java.sql.DriverManager.getConnection(connectionURL, “xxx”, “xxx”);

String query = “INSERT INTO orders (username, itemid, quantity, indprice, status) VALUES (?,?,?,?,?)”;
java.sql.PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1,request.getParameter(“username”));
statement.setString(2,request.getParameter(“itemid”));
statement.setString(3,request.getParameter(“quantity”));
statement.setString(4,request.getParameter(“indprice”));
statement.setString(5,request.getParameter(“status”));
statement.executeUpdate();
connection.close();
%>

I get an error =

Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: Exception in JSP: /addtocart.jsp:77

74: statement.setString(3,request.getParameter(“quantity”));
75: statement.setString(4,request.getParameter(“indprice”));
76: statement.setString(5,request.getParameter(“status”));
77: statement.executeUpdate();
78: connection.close();
79: %>
80:

Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:451)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:355)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

root cause

javax.servlet.ServletException: Column ‘itemid’ cannot be null

BUT AS YOU CAN SEE FROM IMAGES itemid is NOT NULL

THANK YOU.

In SQL a sentence needs to end with a ; and your SQL instructions doesn’t. It may be significant because not all the programming languages add that semi-colon when passing the instruction to the database.

actually, i believe it’s optional :slight_smile:

I don’t know if that’s the case for Java.

Edit: I’m assuming, of course, that those interrogations quotes are being substituted for the right values, properly quoted if they’re text.

neither do i

however, you said the semi-colon was needed for SQL, and i believe it’s optional for SQL

:slight_smile:

Thank you for the comments, but I have ;
after SQL -

String query = “INSERT INTO orders (username, itemid, quantity, indprice, status) VALUES (?,?,?,?,?)”;

The error is (below): COLUMN ‘itemid’ cannot be null ???
itemid is an auto_increment in catalogue,

and should be picked up by addtocart. jsp script and added to ‘orders’ table?
But instead of adding it - it says itemid cannot be null - when actual items are given itemid’s and they are not nulls, the field in table also set as ‘not null’.

Maybe you know anothe addtocart simple script in jsp?
Thank you.

root cause

javax.servlet.ServletException: Column ‘itemid’ cannot be null
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:841)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:774)
org.apache.jsp.addtocart_jsp._jspService(addtocart_jsp.java:151)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ‘itemid’ cannot be null
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

That also depends. If you’re in MySQL (black screen) you do need to write the semi-colon, if you use access in SQL view, you don’t need it because access will add it for you.

Thank you for the comments, but I have ;
after SQL -

String query = “INSERT INTO orders (username, itemid, quantity, indprice, status) VALUES (?,?,?,?,?)”;

Yes, you do have a semi-colon but after closing the quotes. That makes me think that the semi-colon is not part of the SQL Sentence but just an indication for Java where the instruction ends (in this case, assigning a string value to a variable).

I agree, though, that it would be rare that this is your error but I’ve seen stranger things in life.

You include the itemid in your query. I assume that someone is assigning a value to itemid before you insert the information on the database, probably because you already have that information from the list of items available to buy or maybe you’re assigning it programmatically.

If you’re sure that you have an itemid assigned, then try to output the values of all your variables (I guess that it would be those all get parameters) to know that you’re getting the right information.

Also, try to echo the text of the query to make sure that those values substitute all those interrogations that you have in your query right now, and that they do it in the right order.

If you don’t know the itemid in advanced, and it is something that the database needs to add by itself, then remove it from the query. The database will assign it an auto-increment value automatically.

Thank you, it seems working with ; now, but now I get another error:

Parameter index out of range (3 > number of parameters, which is 2)

What does it mean? Thank you.
----------------------------------------------------------------------:confused:

Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: Exception in JSP: /addtocart.jsp:74

71: java.sql.PreparedStatement statement = connection.prepareStatement(query);
72: statement.setString(1,request.getParameter(“username”));
73: statement.setString(2,request.getParameter(“itemid”));
74: statement.setString(3,request.getParameter(“quantity”));
75: statement.setString(4,request.getParameter(“indprice”));
76: statement.setString(5,request.getParameter(“status”));
77: statement.executeUpdate(query);

Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:451)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:355)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

root cause

javax.servlet.ServletException: Parameter index out of range (3 > number of parameters, which is 2).
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:841)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:774)
org.apache.jsp.addtocart_jsp._jspService(addtocart_jsp.java:151)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

how many parameters (the “?” placeholders) do you have in your query? It is saying you’re trying to set a 3rd parameter, but you only have 2 placeholders in your prepared statement.

@JURN. Thank you.
Sorry, but I am not sure what does it mean.
Here’s the code - or is it something wrong at MySQL table?

-----------------------------------------------------------:frowning:

<p>You have succesfully added Item <span>No <%= request.getParameter(“itemid”) %></span> to your basket!</p>
<p> Quantity: <span><%= request.getParameter(“quantity”) %></span></p>
<p> Price: <span>£<%= request.getParameter(“indprice”) %></span></p>
<br />
<p>Thank you!</p>
<%@ page import=“java.sql." %>
<%@ page import="java.io.
” %>

<%
String connectionURL = “jdbc:mysql://localhost:3306/xxx”;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
java.sql.Connection connection = java.sql.DriverManager.getConnection(connectionURL, “xxxx”, “xxx”);

String query = “INSERT INTO orders (username, itemid, quantity, indprice, status) VALUES (‘?’,?,?,‘?’,‘?’)”;
java.sql.PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1,request.getParameter(“username”));
statement.setString(2,request.getParameter(“itemid”));
statement.setString(3,request.getParameter(“quantity”));
statement.setString(4,request.getParameter(“indprice”));
statement.setString(5,request.getParameter(“status”));
statement.executeUpdate(query);
connection.close();
%>

@JURN - Here’s the table. Thank you very much.

Field Type Null Default

orderid int(11) No None AUTO_INCREMENT
username varchar(35) No None
itemid int(11) No None
quantity int(11) No 1
indprice double(4,2) Yes NULL
status varchar(35) No basket

what you’re doing wrong? … you’re creating a connection on each JSP load… I’m tired of giving another servlet speech…

@sg707 - thank you for advice, and sorry for making you tired, but that doesn’t solve the problem - it is clearly somewhere with

quantity -

org.apache.jasper.JasperException: Exception in JSP: /addtocart.jsp:71

68: java.sql.PreparedStatement statement = connection.prepareStatement(query);
69: statement.setString(1,request.getParameter(“username”));
70: statement.setString(2,request.getParameter(“itemid”));
71: statement.setString(3,request.getParameter(“quantity”));
72: statement.setString(4,request.getParameter(“indprice”));
73: statement.setString(5,request.getParameter(“status”));
74: statement.executeUpdate(query);

Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:451)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:355)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

root cause

javax.servlet.ServletException: Parameter index out of range (3 > number of parameters, which is 2).

Fine, sorry for diluting your thread w/ non solution response. G’luck.

I’m assuming this is about learning, and not a commercial application :wink:

sorry I missed it earlier -do not put single quotes around the parameters.
It will convert the type for you.