• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL syntax error

 
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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>
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Paper jam tastes about as you would expect. Try some on this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic