sir

hi sir,in this program there is a jtable,i am insert the records to the jtable .when i am inserting the record to the 5th row of the jtable,the inserting query is not executed,plz see this program and provide solution


sir,i am mentioning the error as <<>>>......main bug here>........ in this program,plz see and provide solution sir,database Tables are mentioning below the program sir


package salesbill;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import javax.swing.table.*;
import javax.swing.table.TableModel.*;
import jdbc11.Jdbc11;
import java.sql.*;
import java.sql.Statement;
import java.util.*;

public class SalesInformation extends JPanel
{

String item1,p1,p2,p3,p4,pname1;
int count1=0,sum;
Validator v;
Connection connect;
JTable daytable;
ResultSet rs,rs1,rs2;
JComboBox jb,jb1,batchid;
PreparedStatement pst;
Statement stmt,s;
static final int ITEMID_COLUMN_INDEX=3;
static final int QUANTITY_COLUMN_INDEX=4;
static final int PRICE_COLUMN_INDEX=6;
static final int AMOUNT_COLUMN_INDEX=7;
static final int SNO_COLUMN_INDEX=0;
int count11;
String sno;
//setLayout(null);
JPanel jp,mainPanel;
String d;
double paid,change;
int k;int roecount;
String rajesh,item2,item3,item4;
String x,y;
JLabel titleLabel,cityLabel,totalamountLabel,city1Label,cusnameLabel,dateLabel,tidLabel,amtpaidLabel,changeLabel;
JTextField totalamountField=null,city1Field=null,cusnameField=null,dateField=null,tidField=null,amtpaidField,changeField;
JButton print,update;
double sum12;

DefaultTableModel model ;
String sql;String total=null, city=null, cusname=null,date=null,tid=null;
public SalesInformation()
{
try{
Class.forName("oracle.jdbc.OracleDriver");
connect =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","chinna");
stmt = connect.createStatement();
}catch(Exception e){System.out.println(e.getMessage());}

Font labelFont=new Font("timesroman",Font.BOLD,20);
Color c=new Color(236,233,216);
//Color c=new Color(236,233,255);
setSize(1024,618);
setLayout(null);


mainPanel=new JPanel();
mainPanel.setLayout(new FlowLayout(FlowLayout.CENTER,50,0));
mainPanel.setSize(1024,100);
mainPanel.setBackground(c);

jp= new JPanel();

setSize(900, 900);
jp.setSize(600,600);
setBackground(c);

titleLabel=new JLabel("Departmental Stores",JLabel.CENTER);
titleLabel.setFont(new Font("timesroman",Font.BOLD,25));
add(titleLabel);

cityLabel=new JLabel("Narsapur",JLabel.CENTER);
cityLabel.setFont(new Font("timesroman",Font.BOLD,15));
add(cityLabel);

cusnameLabel=new JLabel("Customer Name:",JLabel.CENTER);
cusnameLabel.setFont(new Font("timesroman",Font.BOLD,15));
add(cusnameLabel);

city1Label=new JLabel(" Customer City :",JLabel.CENTER);
city1Label.setFont(new Font("timesroman",Font.BOLD,15));
add(city1Label);


dateLabel=new JLabel("Date :",JLabel.CENTER);
dateLabel.setFont(new Font("timesroman",Font.BOLD,15));
add(dateLabel);

tidLabel=new JLabel("TID :",JLabel.CENTER);
tidLabel.setFont(new Font("timesroman",Font.BOLD,15));
add(tidLabel);


totalamountLabel=new JLabel("TotalAmount ",JLabel.CENTER);
totalamountLabel.setFont(new Font("timesroman",Font.BOLD,15));
add(totalamountLabel);

amtpaidLabel=new JLabel("AmountPaid: ",JLabel.CENTER);
amtpaidLabel.setFont(new Font("timesroman",Font.BOLD,15));
add(amtpaidLabel);

changeLabel=new JLabel("Change: ",JLabel.CENTER);
changeLabel.setFont(new Font("timesroman",Font.BOLD,15));
add(changeLabel);



totalamountField=new JTextField();
cusnameField=new JTextField();
city1Field=new JTextField();
dateField=new JTextField();
tidField=new JTextField();
amtpaidField=new JTextField();
changeField=new JTextField();

add(cusnameField);
add(city1Field);
add(dateField);
add(tidField);
add(amtpaidField);
add(changeField);


totalamountField.setFont(new Font("Courier New",Font.BOLD,15));
totalamountField.setEditable(false);
add(totalamountField);

print=new JButton("PRINT");
add(print);
update=new JButton("UPDATE");
add(update);



titleLabel.setBounds(340,25,300,30);
cityLabel.setBounds(390,45,90,30);

cusnameLabel.setBounds(50,90,120,25);
city1Label.setBounds(270,90,120,25);
dateLabel.setBounds(495,90,70,25);
tidLabel.setBounds(695,90,70,25);

cusnameField.setBounds(165,90,110,25);
city1Field.setBounds(390,90,110,25);
dateField.setBounds(560,90,110,25);
tidField.setBounds(760,90,110,25);




/*totalamountLabel.setBounds(700,600,100,30);
totalamountField.setBounds(810,600,100,30);*/

totalamountLabel.setBounds(100,640,100,30);
totalamountField.setBounds(200,640,185,30);

amtpaidLabel.setBounds(420,640,100,30);
amtpaidField.setBounds(520,640,100,30);

changeLabel.setBounds(700,640,100,30);
changeField.setBounds(810,640,200,30);












print.setBounds(890,450,90,30);
update.setBounds(890,500,90,30);
String head1[]={"soap","oil","rice","shampoo","daals","toothpaste","hairoils","roomcleaners","detergents"};
String head2[]={"1 kg","500 g","250 g","100 g","1 lt","500 ml","250 ml","100 ml"};
jb=new JComboBox();
jb.removeAllItems();
jb1=new JComboBox();
jb1.removeAllItems();
batchid=new JComboBox();




try{
Class.forName("oracle.jdbc.OracleDriver");
Connection con =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","chinna");
Statement st = con.createStatement();

String sql="select distinct(pname) from productid";
rs=st.executeQuery(sql);


while(rs.next())
{
String ptype2=rs.getString(1);
jb1.addItem(ptype2);


}


}
catch(Exception e)
{
}



jb1.addItemListener(new ItemListener() {
public void itemStateChanged(ItemEvent ae)
{

jb.removeAllItems();
String st1=(String)jb1.getSelectedItem();
//
String sql1="select distinct(pname) from productdetails1 where ptype=\'"+st1+"\'";
try{
connect =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","chinna");
stmt = connect.createStatement();
rs=stmt.executeQuery(sql1);

while(rs.next())
{
String pname1=rs.getString(1);
jb.addItem(pname1);

}
System.out.println(" .....king ...................");


}catch(Exception e){}
}
});













String data[][] = {null};
String col[] = {"Sno","ptype","PName","ItemId","Quantity","BatchId","unit_Price","Amount"};
model = new DefaultTableModel(data,col){
public boolean isCellEditable(int row, int col) {

if (col == 7) {
return false;
} else {
return true;
}
}
};



daytable = new JTable(model);
model.addTableModelListener(new TableModelListener(){
public void tableChanged(TableModelEvent e)
{
String p=null;





if( e.getColumn()==2 )
{


item1 = (String)daytable.getModel().getValueAt(e.getFirstRow(),1);
item2 = (String)daytable.getModel().getValueAt(e.getFirstRow(),2);
System.out.println("error.chinna111.....qty.............."+item1);
System.out.println("error.chinna111.....qty.............."+item2);
sql = "select distinct(itemid) from purchaseorder where ptype=\'"+item1+"\' and pname=\'"+item2+"\'";

try{
rs =stmt.executeQuery(sql);
while(rs.next())
{

p1=rs.getString(1);

System.out.println(" p1.................................. "+p1);
model.setValueAt(p1,e.getFirstRow(),3) ;
}

}catch(Exception er){System.out.println("error.chinna....");}
}



if(e.getColumn()==4)
{


item1 = (String)daytable.getModel().getValueAt(e.getFirstRow(),1);
item2 = (String)daytable.getModel().getValueAt(e.getFirstRow(),2);
item4 = (String)daytable.getModel().getValueAt(e.getFirstRow(),4);


int item5=Integer.parseInt(item4);
System.out.println("error.chinna111.....qty.............."+item5);
System.out.println("error.chinna111....ptype..............."+item1);
System.out.println("error.chinna111.......pname............"+item2);



<>>>.....................This is the main bug sir>>.....................
try{

batchid.removeAllItems();

Class.forName("oracle.jdbc.OracleDriver");
Connection c =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","chinna");
Statement s1= c.createStatement();
sql = "select batchid from purchaseorder where ptype=\'"+item1+"\' and pname=\'"+item2+"\' and qty>=\'"+item5+"\'";


System.out.println(" p2.................................. ");
ResultSet rs21 =s1.executeQuery(sql);

while(rs21.next())
{

String q=rs21.getString(1);
System.out.println(" salem.................................. "+q);
batchid.addItem(q);
batchid.setVisible(true);
daytable.getColumnModel().getColumn(5).setCellEditor(new DefaultCellEditor(batchid));
/*batchid.addItemListener(new ItemListener() {
public void itemStateChanged(ItemEvent ae)
{

String st1=(String)batchid.getSelectedItem();
String sql1="select saleprice from purchaseorder where batchid=\'"+st1+"\'";
try{
connect =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","chinna");
stmt = connect.createStatement();
rs=stmt.executeQuery(sql1);
while(rs.next())
{
pname1=rs.getString(1);
}

}catch(Exception e){}
}
});*/
}

}catch(Exception er){System.out.println("error.chinna....");}

}





if(e.getColumn()==1)
{
daytable.getColumnModel().getColumn(2).setCellEditor(new DefaultCellEditor(jb));
}


if(e.getColumn()==0)
{
daytable.getColumnModel().getColumn(1).setCellEditor(new DefaultCellEditor(jb1));
}

























if(e.getColumn()==4){

batchid.addItemListener(new ItemListener() {
public void itemStateChanged(ItemEvent ae)
{

String st1=(String)batchid.getSelectedItem();

String sql1="select saleprice from purchaseorder where batchid=\'"+st1+"\'";
try{
connect =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","chinna");
stmt = connect.createStatement();
rs=stmt.executeQuery(sql1);
while(rs.next())
{
pname1=rs.getString(1);



}



}catch(Exception e){}
}
});


}


















if(e.getColumn()==5){

model.setValueAt(pname1,e.getFirstRow(),6) ;
model.setValueAt(String.valueOf(getAmount(e)),e.getFirstRow(),AMOUNT_COLUMN_INDEX);

}








if(e.getColumn() == AMOUNT_COLUMN_INDEX)
{

sum12=0.0;
for(int rowindex=0;rowindex<model.getRowCount();rowindex++)
{
String d = (String)model.getValueAt(rowindex,AMOUNT_COLUMN_INDEX);
x=(String)model.getValueAt(0,4);
y=(String)model.getValueAt(0,5);

if(d!=null)
sum12 += Double.parseDouble(d);

}
totalamountField.setText(String.valueOf(sum12));

}

}


private double getAmount(TableModelEvent e)
{
double amt=0.0;
String pr=(String)model.getValueAt(e.getFirstRow(),6);

String qty=(String)model.getValueAt(e.getFirstRow(),4);

System.out.println("...,,,,,,,pname12pr,,,,,,,,,,,,,,,"+pr);

System.out.println("...,,,,,,,pname13qty,,,,,,,,,,,,,,,"+qty);

//int p41=Integer.parseInt(p4);
// int qty1=Integer.parseInt(qty);
//if(qty1<=p41){

if(pr != null && qty != null)

amt = Double.parseDouble(pr) * Double.parseDouble(qty);

// System.out.println(" amount "+amt);

// }
//else{
// JOptionPane.showMessageDialog(null,"There is no sufficient quantity");
//}


return amt;
}
});
// batchid.removeAllItems();
daytable.setRowSelectionAllowed(true);
daytable.setColumnSelectionAllowed(true);
daytable.setCellSelectionEnabled(true);

roecount=daytable.getRowCount();
System.out.println("................................................"+roecount);
count11=1;
sno=String.valueOf(count11);
daytable.getModel().setValueAt(sno,roecount-1,0);

daytable.addKeyListener(new KeyAdapter() {
public void keyPressed(KeyEvent ke) {
if(ke.getKeyChar()==ke.VK_ENTER)
{
/* roecount=daytable.getRowCount();
System.out.println("................................................"+roecount);
count11=1;
sno=String.valueOf(roecount);
daytable.getModel().setValueAt(sno,roecount-1,0);*/


model.addRow(new Object[]{"","","","","","","",""});
int selrow=daytable.getRowCount();
String selrow1=String.valueOf(selrow);
daytable.getModel().setValueAt(selrow1,selrow-1,0);
batchid.setVisible(false);
daytable.getModel().setValueAt("0.0",selrow-1,6);
daytable.getModel().setValueAt("0.0",selrow-1,7);

}
}
});
sum=count1;
if(sum==count1){
try{
rs=stmt.executeQuery("select tid from salesbill");
while(rs.next())
{
String id=rs.getString(1);
//System.out.println("id...."+id);
String id2= id.substring(12);
int id1=Integer.parseInt(id2);
//System.out.println("id1........"+id1);
id1=id1+1;
// System.out.println("id1.........."+id1);
String id3=String.valueOf(id1);
String cut=id.substring(0,12);
//System.out.println("cut.........."+cut);

rajesh=cut.concat(id3);
//System.out.println("rajesh............."+rajesh);

tidField.setText(rajesh);
}
}catch(Exception e)
{}
}


changeField.addMouseListener(new MouseAdapter(){
public void mouseClicked(MouseEvent me){
String paid1=amtpaidField.getText().trim();
if(!(paid1.equals("")))
{

paid=Double.parseDouble(paid1);
System.out.println(" ......chinaaaaaaaaaaaa......"+sum12);

if(paid>=sum12)
{

change=paid-sum12;
String c=String.valueOf(change);
changeField.setText(c);
System.out.println(c);
}
else
{
JOptionPane.showMessageDialog(null,"please paid more amount ");


amtpaidField.setText("0.0");
}
}
else
JOptionPane.showMessageDialog(null,"Please Enter Amount in AmountpaidField ");


}
});
















JScrollPane js=new JScrollPane(daytable);
add(js);
js.setBounds(50,130,820,490);





v=new Validator();



update.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent ae)
{
if( !(cusnameField.getText().equals("")) && !(dateField.getText().equals("")) && !(city1Field.getText().equals("")))
{


char smallchar=cusnameField.getText().trim().charAt(0);
char smallchar1=city1Field.getText().trim().charAt(0);
if(((int)smallchar >= (int)'a') && ((int)smallchar <=(int)'z') ||((int)smallchar >= (int)'A') && ((int)smallchar <=(int)'Z')){

if( ((int)smallchar1 >= (int)'a') && ((int)smallchar1 <=(int)'z') ||((int)smallchar >= (int)'A') && ((int)smallchar <=(int)'Z')){

total=totalamountField.getText().trim();
//System.out.println("total.........."+total);
city= city1Field.getText().trim();
//System.out.println("cityl.........."+city);
cusname=cusnameField.getText().trim();
//System.out.println("cusname......."+cusname);
date=dateField.getText().trim();
//System.out.println("date......."+date);
String dd=date.substring(0,2);
int dd1=Integer.parseInt(dd);
// System.out.println(dd+"th55555dhjd");
String mm=date.substring(3,5);
int mm1=Integer.parseInt(mm);

// System.out.println("month:"+mm1);
//System.out.println(mm+"sdfsfsv");
String yyyy=date.substring(6,10);
int yyyy1=Integer.parseInt(yyyy);
//System.out.println(yyyy1+"dfdvd");

if((mm1<=12 &&mm1>=1)&&(dd1<=31 &&dd1>=1)) {

String mon=null;
switch(mm1){

case 1:mon="jan";
break;
case 2:mon="feb";
break;
case 3:mon="mar";
break;
case 4:mon="apr";
break;
case 5:mon="may";
break;
case 6:mon="jun";
break;
case 7:mon="jul";
break;
case 8:mon="aug";
break;
case 9:mon="sep";
break;
case 10:mon="oct";
break;
case 11:mon="nov";
break;
case 12:mon="dec";
break;
}

d=String.valueOf(dd1+"/"+mon+"/"+yyyy1);
System.out.println("date:"+d);
}
tid=tidField.getText().trim();
// System.out.println("tid..."+tid);














try{
pst=connect.prepareStatement("insert into salesbill values(?,?,?,?,?) ");
pst.setString(1,cusname);
pst.setString(2,d);
pst.setString(3,city);
pst.setString(4,tid);
pst.setString(5,total);

k=pst.executeUpdate();
connect.setAutoCommit(true);
if(k==1)
{

JOptionPane.showMessageDialog(null,"customer bill successfully added in database");

totalamountField.setText("");
city1Field.setText("");
cusnameField.setText("");
dateField.setText("");
tidField.setText("");
changeField.setText("");
amtpaidField.setText("");
sum=sum+1;
// System.out.println("sum"+sum);

//int count=daytable.getRowCount();
int count=model.getRowCount();
int colcount=daytable.getColumnCount();
// System.out.println("count"+count);
for(int i=daytable.getRowCount()-1;i>=0;i--)
{

//daytable.getModel().removeRow(i);
model.removeRow(i);
//model1.removeRow(i);
// System.out.println(" removed row............"+i);
}
model.addRow(new Object[]{null});

if(count1<sum)
{
//System.out.println("......................wdsw");
try{
rs=stmt.executeQuery("select tid from salesbill");
while(rs.next())
{
String id=rs.getString(1);
//System.out.println("id...."+id);
String id2= id.substring(12);
int id1=Integer.parseInt(id2);
//System.out.println("id1........"+id1);
id1=id1+1;
//System.out.println("id1.........."+id1);
String id3=String.valueOf(id1);
String cut=id.substring(0,12);
// System.out.println("cut.........."+cut);

rajesh=cut.concat(id3);
//System.out.println("rajesh............."+rajesh);

tidField.setText(rajesh);
}//while close
}catch(Exception e)
{}

}

}else{
JOptionPane.showMessageDialog(null,"please enter currect details");
}

}catch(Exception s)
{}

















System.out.println("Check 2................................................."+x);

System.out.println("Check 2................................................."+y);


int update=Integer.parseInt(x);
String query100="select qty from purchaseorder where batchid=\'"+y+"\'";
int k;
try
{
rs1=stmt.executeQuery(query100);
while(rs1.next())
{
int updatebase=rs1.getInt(1);
int remain=updatebase-update;
String query10="update purchaseorder set qty=\'"+remain+"\' where batchid=\'"+y+"\'";

try
{
PreparedStatement pst3=connect.prepareStatement(query10);
k=pst3.executeUpdate();
System.out.print(" "+k);
if(k>0){
JOptionPane.showMessageDialog(null,"Quantity is successfully updated in purchaseorder Table");
}
connect.setAutoCommit(true);
}
catch(Exception ie){
System.out.print("error in update"+ie);
}

}

}catch(Exception e)
{
System.out.println(e+"Check 2");
}
}else{

JOptionPane.showMessageDialog(null,"please enter correct city name");
}





}else{

JOptionPane.showMessageDialog(null,"please enter correct name");
}





}else{

JOptionPane.showMessageDialog(null,"please enter All details");
}


/*}else{
JOptionPane.showMessageDialog(null,"please enter currect details");
}

}catch(Exception s)
{}*/
}
});


/* JScrollPane js=new JScrollPane(daytable);
add(js);
js.setBounds(250,200,500,150);*/


/*print.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent ae)
{
try{

daytable.print();
}catch(Exception a){}
}
});*/


}

}



Database Tables
_______________






SQL> desc productdetails1;
Name Null? Type
----------------------------------------- -------- ----------------------------

PTYPE VARCHAR2(30)
PNAME VARCHAR2(30)
PID NOT NULL VARCHAR2(20)
MEASURETYPE VARCHAR2(20)
MIN_QTY VARCHAR2(20)

SQL> desc purchaseorder;
Name Null? Type
----------------------------------------- -------- ----------------------------

SNO VARCHAR2(20)
PTYPE VARCHAR2(20)
PNAME VARCHAR2(30)
ITEMID VARCHAR2(30)
QTY NUMBER
UNITPRICE VARCHAR2(30)
AMOUNT VARCHAR2(30)
BATCHID VARCHAR2(200)
PROFIT VARCHAR2(30)
SALEPRICE VARCHAR2(30)
TID VARCHAR2(100)

SQL> select * from productid;

PNAME PID
-------------------- --------------------
daal 1002
powder 1003
shampoo 1004
oil 1005
apples 1006
rice 1000
soap 1001

7 rows selected.

SQL> desc productid;
Name Null? Type
----------------------------------------- -------- ----------------------------

PNAME VARCHAR2(20)
PID NOT NULL VARCHAR2(20)

SQL>