Inserting into an Access table with an AutoNumber field

Question

I have an assignment in Java in which I need to update an MS Access Database. The database has six columns, one of them is the ID column which is an Automatic Number. I have written the update statements for five fields like this :

 resultSet.beforeFirst();
  if(resultSet.next()) {
    resultSet.moveToInsertRow();
    resultSet.updateString(1, FirstNameTextField.getText());
    resultSet.updateString(2, LastNameTextField.getText());
    resultSet.updateString(3, SignUpUsernameTextField.getText());
    resultSet.updateString(4, EmailTextField.getText());
    resultSet.updateString(5, SignUpPasswordField.getText());
    **Here should be the statement that updates the 6th field**;
    resultSet.insertRow();

Even though it is an Automatic Number Field i have to write something to update it or the resultSet.insert(); wont work and throw an Exception? Any help is appreciated. Thanks.

score 2 · Accepted Answer · edited May 23 '17 at 11:57

I just tested the following code against a table with an AutoNumber field named "Id" and UCanAccess correctly inserted the new row

String connStr = "jdbc:ucanaccess://C:/Users/Public/mdbTest.mdb";
try (Connection conn = DriverManager.getConnection(connStr)) {
    try (Statement s = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {       
        ResultSet rs = s.executeQuery("SELECT Id, Field1, Field2 FROM ucatest");
        rs.moveToInsertRow();
        rs.updateInt("Id", 0);
        rs.updateString("Field1", "newvalue1");
        rs.updateString("Field2", "newvalue2");
        rs.insertRow();
    }
}

The actual value supplied to updateInt() is not important; the next available AutoNumber value is assigned when insertRow() is executed.

For more information on using UCanAccess, see

Manipulating an Access database from Java without ODBC

Inserting into an Access table with an AutoNumber field

1 Answers1