JAVA DB connenting Office access..

hai friends....iam shackir.. i did 1 program to connect OfficeAccess...in this program everything except insertions are working fine...i cant understand what is the problem..."ANY ONE PLEASE HELP ME"

ERROR IS : 3 SQl error in insertValues methord java.sql.SQLException: Driver does not support this function 0 at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:164) at associated.DataBaseConnetion.insertValues(DataBaseConnetion.java:229) at associated.DataBaseConnetion.main(DataBaseConnetion.java:294)

THE PROGRAM CODE IS HERE:

package associated;

import java.sql.*; import java.util.ArrayList; import java.util.List;

public class DataBaseConnetion {

public String partNumber,partName, availableStock,unitCP,unitSP,tax;;
public Double availableStockD,unitCPD,unitSPD,taxD;

private Connection connect;
private PreparedStatement pstm;
private ResultSet rs;
private String sql;
private int i,count,rownum;

//working
//    public void dataRetrieve(String itemNumber){
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code"> 
public DataBaseConnetion retrieveData(String partNumber){
   DataBaseConnetion dbcrd=new DataBaseConnetion();
    try
    {
        //loading the driver
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        //connection object created using DriverManager class
        //student_base is the name of the database
        connect =DriverManager.getConnection("jdbc:odbc:MyShopDetls");

        Statement st=connect.createStatement();

        dbcrd.sql="select * from MyShopDetls where partNumber ='"+partNumber+"'";
        //      execute method to execute the query
        dbcrd.rs=st.executeQuery(dbcrd.sql);
        if(dbcrd.rs.next()){

            dbcrd.partNumber=dbcrd.rs.getString("partNumber");
            dbcrd.partName=dbcrd.rs.getString("partName");
            dbcrd.availableStock=dbcrd.rs.getString("availableStock");
            dbcrd.unitCP=dbcrd.rs.getString("unitCP");
            dbcrd.unitSP=dbcrd.rs.getString("unitSP");
            dbcrd.tax=dbcrd.rs.getString("tax");

        }else{
            dbcrd.partName="";
            dbcrd.availableStock="";
            dbcrd.unitCP="";
            dbcrd.unitSP="";
            dbcrd.tax="";

        }

        //closing the  statement  and connection object
        st.close();
        connect.close();
    }catch(SQLException sqe){
        System.out.println("SQl error from retrieve data");
    }catch(ClassNotFoundException cnf){
        System.out.println("Class not found error");
    }
    return dbcrd;
}// </editor-fold>                        

//working
 //List retrieveAllData()
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">       
public List retrieveAllData(){
    List ls=new ArrayList();
    try
    {
        rownum=rowNumber();
        DataBaseConnetion dbcr[]=new DataBaseConnetion[rownum];

        //loading the driver
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        //connection object created using DriverManager class
        //student_base is the name of the database
        connect =DriverManager.getConnection("jdbc:odbc:MyShopDetls");

        Statement st=connect.createStatement();

        sql="select * from MyShopDetls e";
        //      execute method to execute the query
        rs=st.executeQuery(sql);

        while(rs.next()){
            dbcr[count]=new DataBaseConnetion();
            dbcr[count].partNumber=rs.getString("partNumber");
            dbcr[count].partName=rs.getString("partName");
            dbcr[count].availableStock=rs.getString("availableStock");
            dbcr[count].unitCP=rs.getString("unitCP");
            dbcr[count].unitSP=rs.getString("unitSP");
            dbcr[count].tax=rs.getString("tax");
            ls.add(dbcr[count]);
            count++;
        }

        //closing the  statement  and connection object
        st.close();
        connect.close();
    }catch(SQLException sqe){
        System.out.println("SQl error from retrieve all data");
    }catch(ClassNotFoundException cnf){
        System.out.println("Class not found error");
    }
    return ls;

}// </editor-fold>                        

 //working
//int rowNumber()
@SuppressWarnings({"unchecked", "empty-statement"})
// <editor-fold defaultstate="collapsed" desc="Generated Code">       
public int rowNumber(){
    try
    {
        //loading the driver
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        //connection object created using DriverManager class
        //student_base is the name of the database
        connect =DriverManager.getConnection("jdbc:odbc:MyShopDetls");

        Statement st=connect.createStatement();

        sql="select * from MyShopDetls";
        rs=st.executeQuery(sql);
        for(i=1;this.rs.next();i++);
        i--;

        //closing the  statement  and connection object
        st.close();
        connect.close();
    }catch(SQLException sqe){System.out.println("SQl error from row number");
    }catch(ClassNotFoundException cnf){System.out.println("Class not found error");}
    return i;
}// </editor-fold>                        

//working
//int updateData(DataBaseConnetion dbc)
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">   
public int updateData(DataBaseConnetion dbc){
    DataBaseConnetion dbcL=new DataBaseConnetion();
    dbcL=dbcL.retrieveData(dbc.partNumber);
    if(dbc.partName.equals("")){dbc.partName=dbcL.partName;}
    if(dbc.availableStock.equals("")){dbc.availableStock=dbcL.availableStock;}
    if(dbc.unitCP.equals("")){dbc.unitCP=dbcL.unitCP;}
    if(dbc.unitSP.equals("")){dbc.unitSP=dbcL.unitSP;}
    if(dbc.tax.equals("")){dbc.tax=dbcL.tax;}

    int resultlen=0;

    try
     {
       //loading the driver
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        //connection object created using DriverManager class
        //student_base is the name of the database
        connect =DriverManager.getConnection("jdbc:odbc:MyShopDetls");

        pstm=connect.prepareStatement("update MyShopDetls set partName=?, availableStock=?," +
                    "unitCP=?,unitSP=?,tax=? where partNumber=?");

        pstm.setString(1, dbc.partName);
        pstm.setString(2, dbc.availableStock);
        pstm.setString(3, dbc.unitCP);
        pstm.setString(4, dbc.unitSP);
        pstm.setString(5, dbc.tax);
        pstm.setString(6, dbc.partNumber);

        resultlen= pstm.executeUpdate();
        System.err.println("Details updated "+resultlen);
        //closing the prepared statement  and connection object
         pstm.close();
         connect.close();
       }
       catch(SQLException sqe){
         System.out.println("SQl error frm update");
        }
        catch(ClassNotFoundException cnf)
        {
         System.out.println("Class not found error");
        }
    return resultlen;
}// </editor-fold>                        

    //int insertValues(DataBaseConnetion dbc)
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">       
public int insertValues(DataBaseConnetion dbc){
    i=0;
    try{
        //loading the driver
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        //connection object created using DriverManager class
        //student_base is the name of the database
        connect =DriverManager.getConnection("jdbc:odbc:MyShopDetls");

        Statement st=connect.createStatement();

        sql="select partNumber from MyShopDetls where partNumber ='aml'";
        rs=st.executeQuery(this.sql);

        if(rs.next()){
            i=0;
            st.close();
        }else{
               st.close();
        sql="insert into MyShopDetls (partNumber, partName, availableStock, unitCP, unitSP, tax) VALUES (?,?,?,?,?,?)"; 

        pstm=connect.prepareStatement(sql);      
        //      execute method to execute the query

        pstm.setString(1, "aml");
        pstm.setString(2, "Anti Medical Lang");
        pstm.setString(3, "34.9");
        pstm.setString(4, "66");
        pstm.setString(5, "75");
        pstm.setString(6, "0.0");

        i=pstm.executeUpdate(sql);

        pstm.close();
        }
        //closing the  statement  and connection object

        connect.close();
    }catch(SQLException sqe){
        System.err.println("SQl error in insertValues methord");
        sqe.printStackTrace();
    }catch(ClassNotFoundException cnf){
        System.err.println("Class not found error");
    }
    return i;
}// </editor-fold>                        

//working
//int deleteData(String partNumber)
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">
 public int deleteData(String partNumber){

     i=0;
    try
 {
   //loading the driver
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

    //connection object created using DriverManager class
    //student_base is the name of the database
    connect =DriverManager.getConnection("jdbc:odbc:MyShopDetls");

    sql="delete from MyShopDetls where partNumber=?";
    pstm=connect.prepareStatement(sql);
    pstm.setString(1,partNumber);
    i=pstm.executeUpdate();

    System.err.println("deletion sucess");
     //closing the prepared statement  and connection object
    pstm.close();
    connect.close();
   }
   catch(SQLException sqe)
   {
     System.out.println("SQl error");
    }
    catch(ClassNotFoundException cnf)
    {
     System.out.println("Class not found error");
    }
     return i;
 }// </editor-fold>

// public int iD; //static void main(String... args) public static void main(String... args){ DataBaseConnetion dbc=new DataBaseConnetion(); // DataBaseConnetion dbc1=new DataBaseConnetion(); // dbc.iD=5; dbc.partNumber="ask"; dbc.partName="Amplitude Shift Keying"; dbc.availableStock="20"; dbc.unitCP="1000"; dbc.unitSP="1300"; dbc.tax="0.5"; System.out.println(dbc.rowNumber()); System.out.println(dbc.insertValues(dbc));

}

View Answers