how to update data of a field in one table from other table using jsp or java?

[Rd Dari]

hi all,

I am making a project and in this project my requirement is that I fill quantity in one dynamic table and using same quantity in other dynamic table. When I fill quantity in second table then how minus last table quantity automatic by coding in java or jsp.

please help me giving some solution............

Thanks in advanced!!!.............

[Vijitha Kumara]

I am making a project and in this project my requirement is that I fill quantity in one dynamic table and using same quantity in other dynamic table

What kind of table are you talking about here (HTML, any third party library etc..)?

When I fill quantity in second table then how minus last table quantity automatic by coding in java or jsp.

What you mean by this. Where you have these table(s), in a JSP?

[sourabh girdhar]

Are you using some custom tags like display tag etc for tables in jsp .

or

are you using javascript tables like jquery/ Yahoo UI.

It can be done easily, once you clear about how you are clearing tables.

Thanks
Sourabh

[Rd Dari]

Hi I am using these code and tablesssssss.....
and jsp page for insertion and updation these are so lenthy so

private String rid;
    private String material_receipt_no;
    private String mrn_date;
    private String order_number;
    private String order_date;
    private String mrn_description;
    private String supplier;
    private String deliver_note;
    private String mat_received_by;
    private String[] item_name;
    private String[] quantity;
    private String[] uom;
    private String[] rate_unit;
    private String isuueid;
    private String issue_no;
    private String issue_date;
    private String issue_to;
  //  private String[] issue_item_name;
   // private String[] issue_quantity;
    private String[] issue_uom;

    public String getMaterial_receipt_no() {
        return material_receipt_no;
    }

    public void setMaterial_receipt_no(String material_receipt_no) {
        this.material_receipt_no = material_receipt_no;
    }

    public String getDeliver_note() {
        return deliver_note;
    }

    public void setDeliver_note(String deliver_note) {
        this.deliver_note = deliver_note;
    }

    public String[] getItem_name() {
        return item_name;
    }

    public void setItem_name(String[] item_name) {
        this.item_name = item_name;
    }

    public String getMat_received_by() {
        return mat_received_by;
    }

    public void setMat_received_by(String mat_received_by) {
        this.mat_received_by = mat_received_by;
    }

    public String getMrn_date() {
        return mrn_date;
    }

    public void setMrn_date(String mrn_date) {
        this.mrn_date = mrn_date;
    }

    public String getMrn_description() {
        return mrn_description;
    }

    public void setMrn_description(String mrn_description) {
        this.mrn_description = mrn_description;
    }

    public String getOrder_date() {
        return order_date;
    }

    public void setOrder_date(String order_date) {
        this.order_date = order_date;
    }

    public String getOrder_number() {
        return order_number;
    }

    public void setOrder_number(String order_number) {
        this.order_number = order_number;
    }

    public String[] getQuantity() {
        return quantity;
    }

    public void setQuantity(String[] quantity) {
        this.quantity = quantity;
    }

    public String[] getRate_unit() {
        return rate_unit;
    }

    public void setRate_unit(String[] rate_unit) {
        this.rate_unit = rate_unit;
    }

    public String getRid() {
        return rid;
    }

    public void setRid(String rid) {
        this.rid = rid;
    }

    public String getSupplier() {
        return supplier;
    }

    public void setSupplier(String supplier) {
        this.supplier = supplier;
    }

    public String[] getUom() {
        return uom;
    }

    public void setUom(String[] uom) {
        this.uom = uom;
    }
    public String getIssue_date() {
        return issue_date;
    }

    public void setIssue_date(String issue_date) {
        this.issue_date = issue_date;
    }

  /**  public String[] getIssue_item_name() {
        return issue_item_name;
    }

    public void setIssue_item_name(String[] issue_item_name) {
        this.issue_item_name = issue_item_name;
    }*/

    public String getIssue_no() {
        return issue_no;
    }

    public void setIssue_no(String issue_no) {
        this.issue_no = issue_no;
    }

   /** public String[] getIssue_quantity() {
        return issue_quantity;
    }

    public void setIssue_quantity(String[] issue_quantity) {
        this.issue_quantity = issue_quantity;
    }*/

    public String getIssue_to() {
        return issue_to;
    }

    public void setIssue_to(String issue_to) {
        this.issue_to = issue_to;
    }

    public String[] getIssue_uom() {
        return issue_uom;
    }

    public void setIssue_uom(String[] issue_uom) {
        this.issue_uom = issue_uom;
    }

    public String getIsuueid() {
        return isuueid;
    }

    public void setIsuueid(String isuueid) {
        this.isuueid = isuueid;
    }
    public String add_material_Receipt()
    {
        PreparedStatement pst1=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql1;
        int count=0;
        con=new JdbcHelper1();
        try
        {
            
            conn=(Connection)con.getConnection();
           // this.conn.setAutoCommit(false);
            ps=conn.prepareStatement("insert into material_receipt values(0,?,?,?,?,?,?,?,?,?,sysdate())");
            ps.setString(1,rid);
            ps.setString(2,material_receipt_no);
            ps.setString(3,mrn_date);
            ps.setString(4,order_number);
            ps.setString(5,order_date);
            ps.setString(6,mrn_description);
            ps.setString(7,supplier);
            ps.setString(8,deliver_note);
            ps.setString(9,mat_received_by);
            ps.executeUpdate();
            System.out.println("Query1 is executing... " + ps);

            pst1=conn.prepareStatement("insert into material_receipt_ref values(0,?,?,?,?,?,sysdate())");
            for(int j=0;j<item_name.length && j<quantity.length && j<uom.length && j<rate_unit.length ;j++)
            {
                pst1.setString(1,rid);
                pst1.setString(2,item_name[j].trim());
                pst1.setString(3,quantity[j].trim());
                pst1.setString(4,uom[j].trim());
                pst1.setString(5,rate_unit[j].trim());
                pst1.executeUpdate();
                System.out.println("Query2 is executing... " + pst1);
            }
            count++;
       
            if(count>0)
            {
                rtn="Ok";
                System.out.println("Insertion is duccessfully!! ");
            }
            else
            {
                rtn="not ok";
                System.out.println("Data insertion is failed!");
            }
        }catch(Exception e)
        {
            System.out.println("SQL EXCEPTION  in insertion Receipt  material..... " + e);
        }
        finally{}
        return rtn;
    }
    public String insert_material_issue()
    {
        PreparedStatement pst2=null;
        PreparedStatement pst=null;
        int count=0;
        String sql;
     // String uom=this.uom[0];
      //String rate=this.rate_unit[0];
        String item1=null;
        int i=0;
        String sql2;
        con=new JdbcHelper1();
        
        try
        {
            conn=(Connection)con.getConnection();
            conn.setAutoCommit(false);
            pst=conn.prepareStatement("insert into material_issue value(0,?,?,?,?,sysdate())");
                pst.setString(1,isuueid.trim());
                pst.setString(2,issue_no);
                pst.setString(3,issue_date);
                pst.setString(4,issue_to);
                pst.executeUpdate();
                System.out.println("Query3 is executing... " + pst);

               /** pst2=conn.prepareStatement("insert into material_issue_ref value(0,?,?,?,?,sysdate())");
              for(i=0;i<this.item_name.length && i<this.quantity.length && i<issue_uom.length ; i++)
              {
                pst2.setString(1,isuueid.trim());
                pst2.setString(2,this.item_name[i].trim());
                pst2.setString(3,this.quantity[i].trim());
                //double last_qty=Double.parseDouble(this.quantity[i])- Double.parseDouble(quantity[i]);
                //pst2.setString(4,Double.toString(last_qty).trim());
                pst2.setString(4,issue_uom[i].trim());
                pst2.executeUpdate();
                System.out.println("Query4 is executing... " + pst2);
              }*/
                count++;
      
            this.conn.commit();            
            if(count>0)
            {
                rtn="Ok";
                System.out.println("data insertion is successfully!!! ");
            }
            else
            {
                rtn="not ok";
                System.out.println("Data insertion is failed!");
            }
        }catch(Exception e)
        {
            System.out.println("SQL EXCEPTION  in updation Receipt  material..... " + e);
        }
        finally{}
        return rtn;
    }  
    public String update_material_ref(String quantity1)throws IOException
    {  
        PreparedStatement pst2=null;
        PreparedStatement pst3=null;
        PreparedStatement ps=null;
        Statement stmt=null;
        ResultSet rs=null;
        String sql1;
        int count=0;
        PreparedStatement pst=null;
        String sql;
        String item1=null;
        int i=0;
        String sql2;
        con=new JdbcHelper1();
        try
        {
            conn=(Connection)con.getConnection();
            conn.setAutoCommit(false);
            //pst2=conn.prepareStatement("insert into material_receipt_ref value(0,?,?,?,?,?,sysdate())");
              pst2=conn.prepareStatement("insert into material_issue_ref value(0,?,?,?,?,sysdate())");
             //for(i=0;i<this.item_name.length  && i<this.quantity.length && i<this.uom.length && i<this.rate_unit.length;i++)
            // {
              while((i<this.item_name.length) && (i<this.quantity.length) && (i<issue_uom.length))
                             {
                pst2.setString(1,isuueid.trim());
                pst2.setString(2,this.item_name[i].trim());
               // pst2.setString(3,this.quantity[i].trim());
                double last_qty=Double.parseDouble(this.quantity[i])- Double.parseDouble(quantity[i]);
                pst2.setString(3,Double.toString(last_qty).trim());
                pst2.setString(4,issue_uom[i].trim());
                pst2.executeUpdate();
                System.out.println("Query4 is executing... " + pst2);
            }
             
                     double  itm_qty1=Double.parseDouble(this.quantity[i]);
                     String item_name1=item_name[i];
                     
                     sql="select * from  material_receipt_ref where quantity='"+quantity1+"'";
                     System.out.println("Query is for quantity  = " + sql);
                     rs=stmt.executeQuery(sql);
                     System.out.println("fdfdsfsdfsdfsdfsd ...."+sql);
                     if (rs.next())
                     {
                     do
                      {
                         double quan=Double.parseDouble(this.rs.getString(4));
                         String item2=this.rs.getString(3);
                         if(item_name1.equalsIgnoreCase(item2))
                         {
                             double remain_qty=itm_qty1-quan;
                             String remain_quantity=Double.toString(remain_qty);
                             
                             pst3=conn.prepareStatement("UPDATE material_receipt_ref SET item_name=?,quantity=?,UOM=?,rate_unit=? where receipt_id=?");
                             pst3.setString(1,item_name[i].trim());
                             //pst3.setString(1,this.item_name[i]);
                             pst3.setString(2,remain_quantity);
                             pst3.setString(3,uom[i].trim());
                             pst3.setString(4,rate_unit[i].trim());
                             pst3.setString(5,quantity1);
                             pst3.executeUpdate();
                             System.out.println("Query5 is executing... " + pst3);
                          }
                 }while(rs.next());
               
               i++;
               count++;
       }//count++;
            this.conn.commit();            
            if(count>0)
            {
                rtn="Ok";
                System.out.println("data updation ");
            }
            else
            {
                rtn="not ok";
                System.out.println("Data updation is failed!");
            }
        }catch(Exception e)
        {
            System.out.println("SQL EXCEPTION  in updation Receipt  material..... " + e);
        }
        finally{}
        return rtn;
    }

I am using these tables......

CREATE TABLE `material_issue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `issue_id` varchar(100) DEFAULT NULL,
  `material_issue_no` varchar(100) DEFAULT NULL,
  `issue_date` varchar(100) DEFAULT NULL,
  `issue_to` varchar(100) DEFAULT NULL,
  `current_date_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `material_issue_ref` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `issue_id` varchar(100) DEFAULT NULL,
  `item_name` varchar(100) DEFAULT NULL,
  `quantity` varchar(100) DEFAULT NULL,
  `uom` varchar(100) DEFAULT NULL,
  `current_date_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `material_receipt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_id` varchar(100) DEFAULT NULL,
  `receipt_no` varchar(100) DEFAULT NULL,
  `mrn_date` varchar(100) DEFAULT NULL,
  `order_no` varchar(100) DEFAULT NULL,
  `order_date` varchar(100) DEFAULT NULL,
  `mrn_description` varchar(200) DEFAULT NULL,
  `supplier` varchar(100) DEFAULT NULL,
  `delivery_note` varchar(100) DEFAULT NULL,
  `material_rec_by` varchar(100) DEFAULT NULL,
  `current_date_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `material_receipt_ref` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_id` varchar(100) DEFAULT NULL,
  `item_name` varchar(100) DEFAULT NULL,
  `quantity` varchar(100) DEFAULT NULL,
  `UOM` varchar(100) DEFAULT NULL,
  `rate_unit` varchar(100) DEFAULT NULL,
  `current_date_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) 

[sourabh girdhar]

Why are you posting subject as JSP and in JSP forum..
The first thing which comes to mind is UI components/rendering table in jsp page.

This is plain database/jdbc issue. Move it to that forum.

What is the issue?. anyways when you insert into database you have all values, whats the problem in calculating while inserting.
Simply write a static method in another util class which provides you desired value and use that while inserting other object.

Anyways you can't insert into more than one table at one point of time, So you have to fire two queries. So use values in first for calculations and getting value for secodn query.
It can also be done on pure database side by writing triggers. But it will be more complex as plain java/jdbc.

Sourabh