Getting random values on refresh in JSP page

[raghuvendra]

Hi

I have a jsp page with 4 columns: namely Category name , Category order, Input field and a submit button.
All these are aligned in a row. And Each Category Name has its corresponding Category order, Input field and a submit button.

The Category name is being fetched from the oracle db along with the corresponding Category order.

In the corresponding input field (text box) the user enters a new category order which gets stored in the db on using the submit button.
The new category order (number) has to be less or equal to the count of the categories . On Submitting the New category order a sql procedure is called which updates the order and then the new order is displayed in the category Order column in the same jsp page.

The procedure looks like this:

CREATE OR REPLACE PROCEDURE proc_ordUpdate

(oldOrder IN number,newOrder IN number, catname IN varchar2 )
IS
begin
if oldOrder>0 and newOrder>0 and oldOrder!=newOrder then
if oldOrder<newOrder then
update cms_video_info set CATORDER=-1 where CATORDER = oldOrder and CATNAME=catname;
update cms_video_info set CATORDER=CATORDER-1 where CATNAME=catname and CATORDER between oldOrder+1 and newOrder;
update cms_video_info set CATORDER=newOrder where CATORDER=-1 and CATNAME=catname;
else
update cms_video_info set CATORDER=-1 where CATORDER=oldOrder and CATNAME=catname;
update cms_video_info set CATORDER=CATORDER+1 where CATNAME=catname and CATORDER between newOrder and oldOrder-1;
update cms_video_info set CATORDER=newOrder where CATORDER=-1 and CATNAME=catname;
end if;

My jsp page looks like this:

<%@ page language="java" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.security.*" %>
<%@ page import="java.net.URL" %>
<%@ page import="java.net.HttpURLConnection" %>
<%@ page import="com.btsl.*" %>
<%
response.setHeader("Cache-Control","no-cache");
response.setHeader("Pragma","no-cache");
response.setDateHeader ("Expires", 60);
%>

<script type="text/javascript">
function FormAction(cost_rad)
{
  document.frm1.submit();
}
</script>

<script language="JavaScript">
function update(old_catorder,new_catorder,catname,seq,count)
{ 
   if (new_catorder == old_catorder){
    alert("New order and Old order should not be same");
    return false;
  }
  else if (new_catorder > count) {
    alert("New Order should be between 1 and "+count);
    return false;
  }
else{
  var field_pos="new_catorder"+seq;
  //alert(field_pos)
  //alert("new_catorder:"+document.getElementById(field_pos).value);

  document.frm1.old_catorder.value=old_catorder;
  document.frm1.new_catorder.value=document.getElementById(field_pos).value;
  document.frm1.catname.value=catname;

 alert(document.frm1.catname.name);
 alert(document.frm1.catname.value);
 alert(document.frm1.old_catorder.value);
 alert(document.frm1.new_catorder.value);

  document.frm1.submit();
  return true;
}
}
</script>



<%
int old_catorder=0;
int new_catorder=0;
String catname="";
int count=0;
int curr_catorder=0;
String cost_rad="";
String query ="";

Connection db_conn = null;
Statement db_st = null;
ResultSet rs =null;
CallableStatement proc=null;

Vector<String> catnameVec = new Vector<String>(1,1);
Vector<Integer> old_CatOrderVec  = new Vector<Integer>(1,1);

if((String)request.getParameter("cost_rad")!=null)
{
  cost_rad=request.getParameter("cost_rad");
}

System.out.println("Selected cost_rad is :: "+cost_rad);

try {
  db_conn = OracleConnectionUtil.getConnection();
  db_st=db_conn.createStatement();

  if (cost_rad.equals("Free"))
  {
    System.out.println("applying free query");
    query = "select distinct catname,catorder from cms_video_info where cost ='0' order by catorder asc";
  }
  else
  {
    System.out.println("applying paid query");
    query = "select distinct catname,catorder from cms_video_info where cost !='0' order by catorder asc";
  }

  try
  {
    rs=db_st.executeQuery(query);
  }
  catch(Exception e)
  {
    System.out.println("ERROR!!! Exception while getting catname");
  }

/* if not null print the result of query */

  if(rs !=null)
  {
    System.out.println("value of rs:"+rs);
    try
    {
      while(rs.next())
      {
        try
        {
          catnameVec.add((rs.getString(1)).trim());
          old_catorder=rs.getInt(2);
          old_CatOrderVec.add(old_catorder);
        }
        catch(SQLException subE)
        {
          System.out.println("ERROR!!! sql exception while fetching data" +subE);
        }
      }
    } catch(Exception er)
    {
      System.out.println("ERROR!!! problem in result set" +er);
    }
  }
try{
  if((String)request.getParameter("catname")!= null)
  {
System.out.println("catname is" );
    catname=request.getParameter("catname");
    System.out.println("catname is" +catname);
  }
		
  if(Integer.parseInt((String)request.getParameter("new_catorder").trim())!=0)
  {
	System.out.println("new_catorder is" );
new_catorder=Integer.parseInt((String)request.getParameter("new_catorder").trim());
    System.out.println("new catorder is " +new_catorder);
  }
}catch(Exception e)
{
System.out.println("Exception catname old_catorder"+old_catorder);
}
  
if(Integer.parseInt((String)request.getParameter("old_catorder").trim())!=0)
  {
old_catorder=Integer.parseInt((String)request.getParameter("old_catorder").trim());
    System.out.println("old catorder is " +old_catorder);

    try
    {
		
     System.out.println("Proc calling");  
      String strQuery = "{call proc_ordUpdate(?,?,?)}";
      proc = db_conn.prepareCall(strQuery);
			//proc.registerOutParameter(3,Types.VARCHAR);
	  	System.out.println("Execution update values:old_catorder"+old_catorder+"new_catorder"+new_catorder+"catname"+catname);
      proc.setInt(1,old_catorder);
      System.out.println("old_catorder="+old_catorder);
      proc.setInt(2,new_catorder);
      System.out.println("new_catorder="+new_catorder);
      proc.setString(3,catname);
      System.out.println("catname="+catname);

      proc.registerOutParameter(3,Types.VARCHAR);
			System.out.println("executing Proc");
			try{
		System.out.println("inside :executing Proc");
      proc.execute();
}catch(Exception e)
{
System.out.println("executing ProcEXECP");
}
			System.out.println("executing completed");
      System.out.println("old catorder updated successfully");
    } catch(Exception ex)
    {
      System.out.println("ERROR!!! failed to update catorder" +ex);
    }
  }

}
catch(Exception e)
{
  e.printStackTrace();
  System.out.println("ERROR After Procedure!!! unable to connect to oracle DB");
}
finally
{
  if (proc != null)
  {
    proc.close();
    proc = null;
  }

  if(db_conn!=null)
  {
    db_conn.commit();
    rs.close();
    db_st.close();
    db_conn.close();
  }
}
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />







<script type="text/javascript">
function reloadPage()
{ window.location.reload(); }
</script>

</head>

<form  name=frm1 action="vem_catOrder.jsp" method="Post">

<body>







<table width="95%"; align="center"; border="2"; cellpadding="0">
<tr height="30">
<th width="38%"; align="center">Category</th>
<th width="32%"; align="center">Current Order</th>
<th width="25%"; align="center">Set Order</th>

</tr>
</table>

<div style="overflow:auto; width:100%; height:110px" align="center">
<table width="95%"; align="center"; border="1"; cellpadding="0"; cellspacing="2">

<%
try {
  for (int i =0; i < catnameVec.size(); i++)
  {
      %>
      <tr>
      <td width="38%"; align="center";>
      <input type=hidden name='<%="catname"+i%>' id=catname"+i+" value='<%=catnameVec.elementAt(i)%>'/><%=catnameVec.elementAt(i)%>
      </td>
      <td width="32%"; align="center";>
      <input type=hidden name=old_catorder"+i+" id=old_catorder"+i+" value='<%=old_CatOrderVec.elementAt(i)%>'/><%=old_CatOrderVec.elementAt(i)%>
      </td>
      <td width="15%"; align="center";>
      <INPUT type="text" name='<%="new_catorder"+i%>' id='<%="new_catorder"+i%>'  style="WIDTH: 80px" maxLength=15 value=""/>
      </td>

      <td width="10%" align="center">
      <input type="button" name=button"+i+" id=button"+i+" onClick='return update("<%=old_CatOrderVec.elementAt(i)%>","<%=new_catorder%>","<%=catnameVec.elementAt(i)%>",<%=i%>,<%=catnameVec.size()%>)' value="Submit"/></td>
      </tr>
      <%
  }
}
catch(Exception e) {
  System.out.println("got Exception"+e);
}
%>

</table>
</div>

<INPUT type=hidden name=old_catorder value="">
<INPUT type=hidden name=new_catorder value="">
<INPUT type=hidden name=catname value="">

<br />

</form>

</body>
</html>


       else

          dbms_output.put_line('please check the new order and old order');

     end if;

end;

My problem is that after i submit the page the previous given value is displayed and on calling refresh i get a random values. I guess the function update gets called everytime i refresh the page.
someone suggested to use a request dispatcher.
I have no clues as to how to go about it.

Please HELP!!!

[jhardman]

raghuvendra,

You posted way too much code, there is no way I am going to browse through 300 lines to find a problem, but no worries! In the future just show the parts of the code that we are interested in.

The real problem is that I don't understand what the problem is - you need to explain what the page SHOULD DO and then explain what it ACTUALLY DOES. You might have said what it is doing, but you didn't say what it should do. It sounds to me that the real issue might be that the page you are displaying is the result of a form submission, so that when you refresh the page, you have essentially re-submitted the form, but I'm not sure because your explanation is unclear and I'm not going to read through that much code to try to decipher what you meant.

If that is the case, you will need to check the db to see if the data you entered has already been entered, and if so you will need to block it, or at least alert the user "This info is already in the db". Let me know if this helps.

Jared