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:
Expand|Select|Wrap|Line Numbers
- 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;
Expand|Select|Wrap|Line Numbers
- <%@ 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;
someone suggested to use a request dispatcher.
I have no clues as to how to go about it.
Please HELP!!!