How to insert two strings into my Access database from Java using UCanAccess?

Question

I am trying to add two strings on two separate columns columns of my database using Java but I'm not sure what I am doing wrong. The code I am using

 try{
    Connection conn = DriverManager.getConnection(
                "jdbc:ucanaccess://C:/Users/nevik/Desktop/databaseJava/Employee.accdb");
        Statement st = conn.createStatement();
        String sql = "Select * from Table2";
        ResultSet rs = st.executeQuery(sql);


        rs.updateString("user", user);
        rs.updateString("pass", pass);

        rs.updateRow();

    }
    catch(SQLException ex){
        System.err.println("Error: "+ ex);
    }

The first column on my database is user and the next one is pass. I am using UCanAccess in order to access my database.

Nikhil Talreja · Accepted Answer · 2014-07-01 04:11:26Z

1

This is how you normally update a row in java:

String query = "update Table2 set user = ?, pass= ?";
PreparedStatement preparedStmt = conn.prepareStatement(query);
preparedStmt.setInt   (1, user);
preparedStmt.setString(2, pass);

// execute the java preparedstatement
preparedStmt.executeUpdate();

answered Jul 1, 2014 at 4:11

Nikhil Talreja

2,7741 gold badge16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MadProgrammer · Accepted Answer · 2014-07-01 04:49:35Z

1

First of, you've not updated the position of the current cursor in the ResultSet, which means that it's pointing to nothing...

You could use...

if (rs.next()) {
   rs.updateString("user", user);
   rs.updateString("pass", pass);
   rs.updateRow();
}

But this assumes two things...

You have a database that supports updating values in the ResultSet and
You want to update the existing values.

To insert a value into the database, you should be using the INSERT command, for example...

try(Connection conn = DriverManager.getConnection(
            "jdbc:ucanaccess://C:/Users/nevik/Desktop/databaseJava/Employee.accdb")) {
    try (PreparedStatement stmt = conn.prepareStatement("INSERT into Table2 (user, pass) VALUES (?, ?)") {
        stmt.setString(1, user);
        stmt.setString(2, pass);
        int rowsUpdated = stmt.executeUpdate();
    }
}
catch(SQLException ex){
    System.err.println("Error: "+ ex);
}

You might like to take some time to go over a basic SQL tutorial and the JDBC(TM) Database Access trail

As a side note...

You should not be storing passwords in Strings, you should keep them in char arrays and
You should not be storing passwords in the database without encrypting them in some way

edited Jul 1, 2014 at 4:49

answered Jul 1, 2014 at 4:11

MadProgrammer

348k22 gold badges243 silver badges379 bronze badges

8 Comments

guevarak12 Over a year ago

setString is asking me to pass an int for the first argument

guevarak12 Over a year ago

updated the try clause try(Connection conn = DriverManager.getConnection( "jdbc:ucanaccess://C:/Users/nevik/Desktop/databaseJava/Employee.accdb")) { try (PreparedStatement stmt = conn.prepareStatement("INSERT into Table2 (user, pass) VALUES ("+user+", "+pass+")")) { stmt.setString(1, user); stmt.setString(2, pass); stmt.executeUpdate(); } got error: net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: USER12 I cant seem to figure out what USER12 is referring to

MadProgrammer Over a year ago

The try-with-resources demonstrated in the answer is only available in Java 7+

guevarak12 Over a year ago

user and pass are the variables that i used to get from the user to the program

MadProgrammer Over a year ago

Yes, but PreparedStaments bind the variables to the parameters positions, that's what the setString calls are for. Take a few minutes to read How to use Prepared Statements. Thought I had linked it, sorry

|

jamadei · Accepted Answer · 2014-07-04 08:32:32Z

1

@guevarak12 About the original question (how to use updatable ResultSet): your code is wrong, you have to move the cursor in the right position. In particular, if you are inserting a new row you have to call rs.moveToInsertRow(); before rs.updateString("user", user). If you are updating an existent row, you have to move the cursor calling rs.next() and so reach the row to update. Also you have to create the Statement in a different way:

Statement st =conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

See junit examples in the UCanAccess source distribution, class net.ucanaccess.test.CrudTest. All other comments seem to be correct.

answered Jul 4, 2014 at 8:32

jamadei

1,7109 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to insert two strings into my Access database from Java using UCanAccess?

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related