0

I'm learning how to simply add records to a table with a child table using MS Access, please see the figure at the bottom. Below, c is just an instance of a class that I made where I set up the connection for the database. 

public void addRecord(String name, String itemName){
    long key = 0;
    try {
        c.openConnection();
        String sql = "INSERT INTO custDetail ([custName]) VALUES('"+name+"')";
        // After hopefully inserting the values to the fields of custDetail,
        // I'm trying to get the custID from the newly inserted record
        c.stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        c.rs = c.stmt.getGeneratedKeys();

        if (c.rs != null && c.rs.next()){
            key = c.rs.getLong(1);
        }
        c.closeConnection();

        c.openConnection();
        c.ps = c.con.prepareStatement("INSERT INTO rentDetail (custID, rentItemName)" + "VALUES (?, ?)");
        c.ps.setString(1, String.valueOf(key));
        c.ps.setString(2, itemName);
        c.ps.executeQuery();
        c.closeConnection();
    } catch (SQLException ex) {
        Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Right now I'm getting this error:

Exception in thread "main" java.lang.NullPointerException
at test.Test.addRecord(Test.java:34)
at test.Test.<init>(Test.java:24)
at test.Test.main(Test.java:16)

So I can't really see if it will work even if I got rid of that error. I'm just a beginner so I apologize if the code is beyond unpleasant, but I've been trying to figure this out for 5 hours now so I took the guts to post it here.

ms access

EDIT

Here's my complete code for NewConnection

public class NewConnection {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Statement stmt;

    public void openConnection(){
        String url = "C://Users//Lenovo//Documents//PROGRAMS//JAVA//Test//Test.accdb";
        try{
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            String driver = "jdbc:ucanaccess://" + url;
            con = DriverManager.getConnection(driver);
        }
        catch(SQLException ex){
            System.out.println(ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(NewConnection.class.getName()).log(Level.SEVERE, null, ex);
        }  
    }

    public void closeConnection(){
        try {
            if (con != null)
                con.close();
            if (ps != null)
                ps.close();
            if (rs != null)
                rs.close();
        } 
        catch (SQLException ex) {
            Logger.getLogger(NewConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
Satellite Sage
  • 440
  • 1
  • 4
  • 13
  • Can you post the stacktrace for the error? Additionally opening and closing connections is expensive rather reuse the existing connection for both the operations. If required issue a commit in between – hamlet Mar 01 '20 at 10:38
  • @hamlet, I have now edited the post to include the complete error message. – Satellite Sage Mar 01 '20 at 10:43
  • And whats on line 34? – hamlet Mar 01 '20 at 10:47
  • @hamlet, line 34 is this statement: `c.stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);` – Satellite Sage Mar 01 '20 at 10:50
  • @KarthikeyanVaithilingam, I've been reading some articles as well that might make me realize why I'm getting that null pointer exception, but I haven't have any luck. – Satellite Sage Mar 01 '20 at 11:43
  • In your case `c.stmt` might be `null` without seeing the code of the class of the object `c` it's hard to identify the problem. – seenukarthi Mar 01 '20 at 11:46
  • @KarthikeyanVaithilingam, I have now edited the post to include the code for 'NewConnection' – Satellite Sage Mar 01 '20 at 11:56
  • As per you code the `stmt` property in `NewConnection` will always be null. Having a helper class to maintain connection is normal, but having Statement, ResultSet and PreparedStatement in another class is not advisable. – seenukarthi Mar 01 '20 at 12:00
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/208791/discussion-between-lenovo-genovia-and-karthikeyan-vaithilingam). – Satellite Sage Mar 01 '20 at 12:07

0 Answers0