This week's book giveaway is in the Cloud forum.
We're giving away four copies of The Business Blockchain and have William Mougayar on-line!
See this thread for details.
Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL syntax error

 
Donna Harrington
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I'd be very grateful if somebody could see where my syntax error is in my SQL statements.
thanx :-)

<html>
<head>
<title> Checkout</title>
</head>
<body bgcolor="#228B22">
<font face="Times New Roman,Times" size="+3"> Checkout</font>
<hr>
<jsp:useBean id="cb" scope="session" class="myBeans.CartBean"/>

<form method=get>

<% String notEntered = "";
String connectionURL ="jdbc:mysql://localhost:3306/petshopwebsite";
String driver= "com.mysql.jdbc.Driver";
Connection c = null;
ResultSet rs = null;
Statement st = null;
String password = "";
String username ="root";



try{
Class.forName("com.mysql.jdbc.Driver");
c = DriverManager.getConnection(connectionURL,username, password);
st = c.createStatement();
%>

<%
String Username = request.getParameter("username");
String productId = request.getParameter("productId");

String Quantity = request.getParameter("Quantity");


PreparedStatement p = c.prepareStatement("SELECT Username FROM customerdetails WHERE username='"+ Username +"'");
PreparedStatement p1 = c.prepareStatement("SELECT productId FROM product WHERE productId='"+ productId +"'");
PreparedStatement p2 = c.prepareStatement("INSERT INTO order (Username,productId,Quantity)VALUES('"+Username+"','"+productId+"','"+Quantity+"')");
p.execute();
p1.execute();
int affectedRows= p2.executeUpdate();

if (rs.next()){
Username = rs.getString(1);
System.out.println(Username);
productId = rs.getString(2);
System.out.println(productId);
Quantity = rs.getString(3);
System.out.println(Quantity);
%>
<jsp:include page = "signinsuccessful.jsp"/> <%--display this page on the current page--%>
<%
}
else{
%>
<jsp:include page = "Err.jsp"/> <%--display this page on the current page--%>
<%}



%>




<a href="http://localhost:3306/categoryList.jsp">Shop some more!</a>
<%

}
catch(Exception yu){
yu.printStackTrace();
}

finally
{
try { if( rs != null ) rs.close() ; } catch( SQLException ex ) { }
try { if( st != null ) st.close() ; } catch( SQLException ex ) { }
try { if( c != null ) c.close() ; } catch( SQLException ex ) { }
}
%>
</center>

</body>
</html>
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35266
383
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Donna,
In your database table, what is the type of the quantity column. If it is integer, you are trying to insert a string. This would result in an error.

If it's not this, can you post the error message you are gettting?
 
Ivor Lithan
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Maybe I'm missing something here but, I think you have a fundamental problem here...

you do this


At no point do you assign anything to the result set - looking at your code you don't use anything from the select statements/resultset - so I would strip all of that out, which will leave you just with your insert statement.

Also, since you are using a preparedstatement it would be better to do your sql call like this :


This way you don't need to worry about wrapping strings in quotes and so on?


I
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35266
383
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Donna,
I agree with everything Ivor said. I had only looked at the SQL statements, not the rest of the code. Also, some database drivers have problems when you run multiple prepared statements from the same connection simulateously. It's better to close the prepared statement before running the next one.
 
Donna Harrington
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yep that worked! thanx a million for yer help :-) i really appreciate that cos i'm crap at this stuff.
take care!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic