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));

}

}

September 15, 2011 at 4:31 PM

At last i solved this problem ...

HOw:

I made this table through java program itself rather creating the table from Microsoft Access.

Those who need an example in Java DataBase connetivity can use this program code as an example.

If You can not understand this program. Then u send 1 mail for getting codes which is working... [email protected] is mail id

Thank You