Update the whole info in database instead of one.

[Loh Peggie]

Hey guys, I have a problem regarding on updating in database using jsp and servlets.

The problem is when I update it will update the whole records instead on the specific records I choose to update. Like I update yellow beanie color from "green to yellow".
It update all the color to be the same one as well as other details

do you know what happen?

This is my code in servlet:

package sg.edu.nyp.feppz;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;



import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import sg.edu.nyp.feppz.product.Product;
import sg.edu.nyp.feppz.user.User;



public class EditProductsAdmin extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
  
    public EditProductsAdmin() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

	/**
	 
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */



protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

	//Retreive the user data from the session object
	User user = (User)request.getSession().getAttribute("USER_DATA");
	Product product = (Product)request.getSession().getAttribute("PROD_DATA");
	
	try {
		//Ensure the the driver class is loaded.
		Class.forName("com.mysql.jdbc.Driver");
		//Create a connection to our database.
		//localhost = server name
		//myshop = database name
		//root = user name, password = empty
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost/feppz", "root", "");
		//create an SQL statement, ? marks denote values that we will add later
		PreparedStatement ps = con.prepareStatement("UPDATE products SET ProdName = ?, ProdColor = ?, ProdDesc = ?, UnitPrice = ?, Quantity = ?, ProdCatId = ?  WHERE UserId= ? or ProductId = ? " );
		//replace first ? mark with values from the feedbackText text box
		ps.setInt(7, user.getUserId());
		ps.setInt(8, Integer.parseInt(request.getParameter("ProductId")));
		ps.setString(1, request.getParameter("ProdName"));
		ps.setString(2, request.getParameter("ProdColor"));
		ps.setString(3, request.getParameter("ProdDesc"));
		ps.setFloat(4, Float.parseFloat(request.getParameter("UnitPrice")));
		ps.setString(5, request.getParameter("Quantity"));
		ps.setInt(6, Integer.parseInt(request.getParameter("ProdCatId")));
		
		
		
		

		//execute to insert into the database
		
		ps.executeUpdate();
		//close connection
		con.close();
		//redirect user to FeedbackSucceed.jsp page
		response.sendRedirect("EditProductsAdminS(A).jsp");
		
	} catch (Exception e) {
		e.printStackTrace();
		return;
	}
	
}

}

This is my update form in jsp using dojox

<%
		int userid;
		//Try to retrieve user information from the session object
		if(user == null) {//if it is null, it means that user did not log in
			response.sendRedirect("Home.jsp"); //redirect the browser to the login page
			return; //exit
		}
		userid = user.getUserId();
	%>
	<form name="form1" method="post" action="./EditProductsAdmin">
  <table width="500" border="0">
    
    <tr>
    <td width="130"><label for="ProductId">Product Id:</label></td>
    <td><input type="text" value=" <%  %>"
	name="ProductId" id="ProductId" size="30"
	dojotype="dijit.form.ValidationTextBox" trim="true" propercase="true"
	required="true" invalidmessage="You must enter a value" /></td>
    </tr>
    
    <tr>
    <td width="130"><label for="ProdName">Product Name:</label></td>
    <td><input type="text" value=" "
	name="ProdName" id="ProdName" size="30"
	dojotype="dijit.form.ValidationTextBox" trim="true" propercase="true"
	required="true" invalidmessage="You must enter a value" /></td>
    </tr>
    
    <tr>
    <td width="130"><label for="ProdColor">Product Color:</label></td>
    <td><input type="text" value="  "
	name="ProdColor" id="ProdColor" size="30"
	dojotype="dijit.form.ValidationTextBox" trim="true" propercase="true"
	required="true" invalidmessage="You must enter a value" /></td>
    </tr>
     
     
     <tr>
    <td width="130"><label for="ProdDesc">Product Description:</label></td>
    <td><input type="text" value=" "
	name="ProdDesc" id="ProdDesc" size="30"
	dojotype="dijit.form.ValidationTextBox" trim="true" propercase="true"
	required="true" invalidmessage="You must enter a value" /></td>
    </tr>
    
    
    <tr>
    <td width="130"><label for="UnitPrice">Unit Price":</label></td>
    <td><input type="text" value=" "
	name="UnitPrice"" id="UnitPrice"" size="30"
	dojotype="dijit.form.ValidationTextBox" trim="true" propercase="true"
	required="true" invalidmessage="You must enter a value" /></td>
    </tr>
  
  
  <tr>
    <td width="130"><label for="Quantity">Quantity:</label>
<br /></td>
    <td><select
	dojotype="dijit.form.FilteringSelect" id="Quantity" name="Quantity"
	autocomplete="false" invalidmessage=" You must enter a value">
      <option value="Select">Select</option>
      <option value="1">1</option>
      <option value="2">2</option>
      <option value="3">3</option>
      <option value="4">4</option>
      <option value="5">5</option>
      <option value="6">6</option>
      <option value="7">7</option>
      <option value="8">8</option>
      <option value="9">9</option>
      <option value="10">10</option>
      <option value="11">11</option>
      <option value="12">12</option>
      <option value="13">13</option>
      <option value="14">14</option>
      <option value="15">15</option>
      <option value="16">16</option>
      <option value="17">17</option>
      <option value="18">18</option>
      <option value="19">19</option>
      <option value="20">20</option>
      <option value="21">21</option>
      <option value="22">22</option>
      <option value="23">23</option>
      <option value="24">24</option>
      <option value="25">25</option>
      <option value="26">26</option>
      <option value="27">27</option>
      <option value="28">28</option>
      <option value="29">29</option>
      <option value="30">30</option>
      <option value="31">31</option>
      <option value="32">32</option>
      <option value="33">33</option>
      <option value="34">34</option>
      <option value="35">35</option>
      <option value="36">36</option>
      <option value="37">37</option>
      <option value="38">38</option>
      <option value="39">39</option>
      <option value="40">40</option>
      <option value="41">41</option>
      <option value="42">42</option>
      <option value="43">43</option>
      <option value="44">44</option>
      <option value="45">45</option>
      <option value="46">46</option>
      <option value="47">47</option>
      <option value="48">48</option>
      <option value="49">49</option>
      <option value="50">50</option>
    </select></td>
  </tr>
  
  
  <tr>
    <td width="130"><label for="ProdCatId">Product Category ID:</label>
<br /></td>
    <td><select
	dojotype="dijit.form.FilteringSelect" id="ProdCatId" name="ProdCatId"
	autocomplete="false" invalidmessage=" You must enter a value">
      <option value="Select">Select</option>
      <option value="1">1</option>
      <option value="2">2</option>
      <option value="3">3</option>
      <option value="4">4</option>
      <option value="5">5</option>
    </select></td>
  </tr>
    
    
    <tr>
    <td width="130"><label for=""></label></td>
    <td><input type="text" value=" <%=userid %> " /></td>
    </tr>
</table>
<label>
      <input type="submit" value="Edit Product" />
    </label>
</form>
	

Thanks for your help in all this while.

I really appreciate it

[ujjwal soni]

Hi,

What is the primary key for the table you are updating ?

is it UserId or ProductId ?

Since you are saying that update is working, so, it seems to be a problem with the query.

[Loh Peggie]

Oh the primary key is ProductId

[ujjwal soni]

Hi,

Change your query to

("UPDATE products SET ProdName = ?, ProdColor = ?, ProdDesc = ?, UnitPrice = ?, Quantity = ?, ProdCatId = ?  WHERE  ProductId = ? " );

If you want to update products based on userid then

("UPDATE products SET ProdName = ?, ProdColor = ?, ProdDesc = ?, UnitPrice = ?, Quantity = ?, ProdCatId = ?  WHERE  ProductId = ? and  UserId= ? " );

This will solve your issue..

[Bear Bibeault]

Please be sure to ask JDBC questions in the JDBC forum. I have moved this post there for you.

[Loh Peggie]

Thank you.

Howerver there seems to be error. It explains that :Syntax error on token(s), misplaced construct(s)
, when I debug

Is there anything wrong with this?

 ps.setInt(7, user.getUserId());   
        ps.setInt(8, Integer.parseInt(request.getParameter("ProductId")));   
        ps.setString(1, request.getParameter("ProdName"));   
        ps.setString(2, request.getParameter("ProdColor"));   
        ps.setString(3, request.getParameter("ProdDesc"));   
        ps.setFloat(4, Float.parseFloat(request.getParameter("UnitPrice")));   
        ps.setString(5, request.getParameter("Quantity"));   
        ps.setInt(6, Integer.parseInt(request.getParameter("ProdCatId")));   

Thanks for your help all this while

[Jeanne Boyarsky]

Loh,
The prepared statement code looks fine. It's much more likely the error is in your SQL string. Can you post the updated one you are using?

[Loh Peggie]

Yeah sure

This is the one i am currently using

package sg.edu.nyp.feppz;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;



import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import sg.edu.nyp.feppz.product.Product;
import sg.edu.nyp.feppz.user.User;



public class EditProductsAdmin extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
  
    public EditProductsAdmin() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

	/**
	 
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */



protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

	//Retreive the user data from the session object
	User user = (User)request.getSession().getAttribute("USER_DATA");

	Product product = (Product)request.getSession().getAttribute("PROD_DATA");
	
	try {
		//Ensure the the driver class is loaded.
		Class.forName("com.mysql.jdbc.Driver");
		//Create a connection to our database.
		//localhost = server name
		//myshop = database name
		//root = user name, password = empty
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost/feppz", "root", "");
		//create an SQL statement, ? marks denote values that we will add later
		PreparedStatement ps = con.prepareStatement("UPDATE products SET ProductId = ?, ProdName = ?, ProdColor = ?, ProdDesc = ?, UnitPrice = ?, Quantity = ?, ProdCatId = ?  WHERE UserId= ? " );
		//replace first ? mark with values from the feedbackText text box
		ps.setInt(8, user.getUserId());
		
		
		ps.setInt(1, Integer.parseInt(request.getParameter("ProductId")));
		ps.setString(2, request.getParameter("ProdName"));
		ps.setString(3, request.getParameter("ProdColor"));
		ps.setString(4, request.getParameter("ProdDesc"));
		ps.setFloat(5, Float.parseFloat(request.getParameter("UnitPrice")));
		ps.setString(6, request.getParameter("Quantity"));
		ps.setInt(7, Integer.parseInt(request.getParameter("ProdCatId")));
		
		
		
		

		//execute to insert into the database
		
		ps.executeUpdate();
		//close connection
		con.close();
		//redirect user to FeedbackSucceed.jsp page
		response.sendRedirect("EditProductsAdminS(A).jsp");
		
	} catch (Exception e) {
		e.printStackTrace();
		return;
	}
	
}

} 

Is this the correct statement that allows me to update all value regarding on product except UserId
However when I update it will show something Syntax error on token(s), misplaced construct(s) when I debug
Thanks

[Jeanne Boyarsky]

Hmm. The SQL looks right. You might try moving the "set" for parameter 8 to the end. There's no rule that says they have to be set in order, but you never know.

As far as debugging goes, does it work if you just update one column? Two columns? etc. Finding the smallest statement that gives the error will help in narrowing it down.

[Leonardo Carreira]

Perhaps you have to remove the 'space' inside your PreparedStatement..

please try this Statement..

   
PreparedStatement ps = con.prepareStatement("UPDATE products SET ProductId=?, ProdName=?, ProdColor=?, ProdDesc=?, UnitPrice=?, Quantity=?, ProdCatId=?  WHERE UserId=?" ); 

Correct me if im wrong..

Hope this help..