Problem With Data Insertion in Ms Access

Question

 import java.sql.*;

   public class NewConnection{   private static Connection con;
   private static ResultSet rs;
   private static Statement sm;
   private static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
   private static final String URL = "jdbc:odbc:Driver={Microsoft Access driver (*.mdb)};DBQ=E:\\Database.mdb;";
   private static String query;
   int i;
  private void getConnection(){

    try {
        Class.forName(DRIVER);
        System.out.println("Driver Connected");
        con=DriverManager.getConnection(URL);
        System.out.println("Database Connected");
        sm=con.createStatement();

         }  catch (Exception e) {
     }

    }

   private int ExecuteUpdate(String query1)
   {

    try {
        System.out.println(query1);
          i=sm.executeUpdate(query1);
        } catch (Exception e) {
          e.printStackTrace();
        }

        return i;
    }



     public static void main(String []args){
     NewConnection n= new NewConnection();
     n.getConnection();
         query="insert into Emp values('samr','sam','sa','aas')";
         System.out.println(n.ExecuteUpdate(query));
   }
}

This is the code for inserting the data in d MS Access Database.Databse is the name of d database. But i m not able to insert the data in it.The query runs successfully but doesn't add data in database. Don't know y..? The code creats the table in database successfully if query changed.

Can any one tell me what is the problem Or Where i m wrong..

Thank you

Answer 1

Modify catch statement in getConnection() method to do this first and see if you get any errors

private void getConnection(){
  try {
    Class.forName(DRIVER);
    System.out.println("Driver Connected");
    con=DriverManager.getConnection(URL);
    System.out.println("Database Connected");
    sm=con.createStatement();
  }  catch (Exception e) {
     e.printStacktrace();
  }
}

Answer 2

Never do this!

catch (Exception e) 
{
}

First off you are catching all sort of things that you should not be catching - NullPointerException, ArrayIndexOutOfBoundsException, etc... - things that indicate bugs in your code.

Only catch what the compiler tells you that you have to, unless you have a really good reason to.

Secondly if there are any exceptions being thrown you will never know it because you do nothing. At the very least do "e.printStackTrace()" or log it so you can see what went wrong.

Answer 3

Did you actually commit the transaction? as in java.sql.Connection.commit() after you sent your insert statement to the db? it should theoretically be set to "auto-commit", which you can check by querying java.sql.Connection.getAutoCommit().

private int ExecuteUpdate(String query1)
   {

    try {
        System.out.println(query1);
          i=sm.executeUpdate(query1);
          con.commit();                //try to commit the query
        } catch (Exception e) {
          e.printStackTrace();
        }
        return i;
    }

Also and this is very important: dont use statics for your Connection, Statement and Query objects, since they can and will be overriden by other Instances of your NewConnection. Using this architecture youll have to make absolutely sure that NewConnection is a Singleton or that there are never more that one of these Objects used in parallell (and they are reset everytime they are reused).

Check here for more info on java.sql.Connection