Problems when viewing specific description when search

[Loh Peggie]

Hey hey how com when I click on the specific products there is no records
This is my search servlet:

package sg.edu.nyp.feppz;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.*;

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * Servlet implementation class BeanieAdmin
 */
public class BeanieAdmin extends HttpServlet {
	public void doPost(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();

System.out.println("MySQL Connect Example.");
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";

String dbName = "feppz";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "";

Statement st;
try {
	Class.forName(driver).newInstance();
	conn = DriverManager
			.getConnection(url + dbName, userName, password);
	System.out.println("Connected to the database");
	String ProdColor = request.getParameter("ProdColor");

	ArrayList al = null;
	ArrayList emp_list = new ArrayList();
	String query = "select * from products where ProdColor like'"
			+ ProdColor + "' order by ProdColor";
	System.out.println("query " + query);
	st = conn.createStatement();
	ResultSet rs = st.executeQuery(query);

	while (rs.next()) {
		al = new ArrayList();

		al.add(rs.getString(1));
		al.add(rs.getString(2));
		al.add(rs.getString(3));
		al.add(rs.getString(4));
		al.add(rs.getString(5));
		al.add(rs.getString(6));
		al.add(rs.getString(7));
		al.add(rs.getString(8));
		al.add(rs.getString(9));

		System.out.println("al :: " + al);
		emp_list.add(al);
	}

	request.setAttribute("empList", emp_list);

	System.out.println("empList " + emp_list);

	// out.println("emp_list " + emp_list);

	String nextJSP = "/ProductDesc.jsp";
	RequestDispatcher dispatcher = getServletContext()
			.getRequestDispatcher(nextJSP);
	dispatcher.forward(request, response);
	rs.close();
	st.close();
	conn.close();
	System.out.println("Disconnected from database");
} catch (Exception e) {
	e.printStackTrace();
}
}

}

This is my searh box so that I can type in the productcolor

<form method="post" action="/FEPPZ/BeanieAdmin">
		<table border="0" width="300" align="center" bgcolor="#FFFFFF">
			<tr>
				<td colspan=2 style="font-size: 12pt; color: #00000;" align="center">
				<h3>Search product</h3>
				</td>
			</tr>
			<tr>
				<td><b>ProductColor</b></td>
				<td>: <input type="text" name="ProdColor" id="ProdColor" /></td>
			</tr>
			<tr>
				<td colspan=2 align="center"><input type="submit" name="submit" value="Search"></td>
			</tr>
		</table>
		</form>

This is my search result like when I type yellow, all the yellow records will display.

<table width="500px" align="center" style="border:1px solid #000000;">

<tr style="background-color:efefef;">

<td><b>ProductId</b></td>
<td><b>ProductName</b></td>
<td><b>ProductPrice</b></td>



</tr>
<%
int count=0;
String color = "#F9EBB3";


if(request.getAttribute("empList")!=null)
{
ArrayList al = (ArrayList)request.getAttribute("empList");
Iterator itr = al.iterator();




while(itr.hasNext()){

if((count%2)==0){ 
color = "#eeffee"; 
}
else{
color = "#F9EBB3";
}
count++;
ArrayList empList = (ArrayList)itr.next();
%>
<tr style="background-color:<%=color%>;">
<td><a href="ProductDetailsAdmin.jsp?ProdColor=<%=empList.get(1)%>"><%=empList.get(0)%></a></td>
<td><%=empList.get(1)%></td>
<td>$<%=empList.get(4)%></td>
<td>



</td>



</tr>
<%
}
}
%> 
<%
if(count==0){
%>
<tr>
<td colspan=8 align="center" style="background-color:eeffee"><b>No Record</b></td>
</tr>
<%
}
%>

</table>

When click this specific beanie, It will go to this record:

<p>
<%
				//Retrieve the id of the product selected by the user
				//the product id is sent via the URL as a parameter
				int ProductId = -1;
				String strProductId = request.getParameter("ProductId");
				if(strProductId != null) {
					//Convert from string to int
					ProductId = Integer.parseInt(strProductId);
				}
				
				//Load the database driver
		       	Class.forName("com.mysql.jdbc.Driver");
				//Create a connection to our database
		       	Connection con = DriverManager.getConnection("jdbc:mysql://localhost/feppz", "root", "");
				//create an SQL statement
		       	PreparedStatement ps = con.prepareStatement("SELECT * FROM products WHERE ProdColor=?");
				//set the ID to be the id above
		       	ps.setInt(1, ProductId);
				//Execute and retrieve our result
		       	ResultSet rs = ps.executeQuery();
			%>
</p>
<%
        	//if there is a result, rs.next() will be true
        	//else it will be false
			if(rs.next()) {
		%>
		<fieldset>
<legend>Product Information</legend>
<table border="0">

<tr>
		<td>   
        <div style=""><img border="3"  
            src="images/Beanie/<%=rs.getString("ProdImage") %>" height="150" width="150" /></div>   
        </td> 

		<td>Product Name: <%=rs.getString("ProdName")+"\t" %>

		<div align="left">Product Color: <%=rs.getString("ProdColor")+"\t" %></div>

		<div align="left">Product Description: <%=rs.getString("ProdDesc")+"\t" %></div>

		<div align="left">Product Price: <%=String.format("$%.2f",rs.getDouble("UnitPrice"))+"\t" %></div>

		<div align="left">Quantity: <%=rs.getString("Quantity")+"\t" %></div>
		
		
	
   
		
	    

		</td>
	</tr>


	
</table>
</fieldset>

<%
			}
			else {
				//if no record is found, simply display a no record message
		%>
No record found.Sorry!
<%
			}
		 %>
<p> </p>

However, It did not display

Can you guys help me see what's wrong with this?


Thanks

[David Newton]

Probably because you're doing a query using a product ID as a color.

Unless your product ID's are deliberately hex-based HTML-/CSS-style color identifiers this is unlikely to work.

Is this production code?

[Loh Peggie]

Thank you