java and msaccess insert sql doesnt work

Question

I have 2 problems.

The INSERT queries below are not inserting records in the msAccess database. Can someone please explain why this is happenning and how I can fix it? I replaced single quotes with double quotes, but that doesnt help either.

In this code below, only 1 query gets executed, first one, rest are all skipped. I have to comment previous queries each time and recompile to execute the next one. Is there another way of doing this?

try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection conn=null;
   conn= DriverManager.getConnection("jdbc:odbc:SS");
   Statement s;
   s=conn.createStatement();
   ResultSet rs;
   rs=s.executeQuery("drop table users");
   rs=s.executeQuery("CREATE TABLE users ( id AUTOINCREMENT, username varchar(255) , pass varchar(255), PRIMARY KEY(id) ) ");
   rs=s.executeQuery(" insert into users (username, pass) values( 'name1', 'pass1') ");
   rs=s.executeQuery(" insert into users (username, pass) values( 'name2', 'pass2') ");
} catch (SQLException ex) { 
   ex.printStackTrace(); 
} catch(Exception ee) {
   ee.printStackTrace();
}

The problem could be s.executeQuery("drop table users") will throw an exception if the table does not exists. You need to check whether the table exists before dropping it — Arun P Johny
– Arun P Johny, Commented Mar 4, 2013 at 8:32
The exceptions I get are: "no resultset was generated". Also, I used the drop statement on an existing table only. The main problem is that its not inserting anything in the database. — jeet
– jeet, Commented Mar 4, 2013 at 9:05

Costis Aivalis · Accepted Answer · 2013-03-04 09:40:35Z

There is no ResultSet involved, because you do not make any queries, you should just execute updates. Your program should work if you change your code like this:

Connection conn=null;
Statement s=null;
try {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    conn = DriverManager.getConnection("jdbc:odbc:SS");
    s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    s.executeUpdate("drop table users");
    s.executeUpdate("CREATE TABLE users ( id AUTOINCREMENT, username varchar(255) , pass varchar(255), PRIMARY KEY(id) ) ");
    s.executeUpdate("insert into users (username, pass) values( 'name1', 'pass1') ");
    s.executeUpdate("insert into users (username, pass) values( 'name2', 'pass2') ");
} catch (SQLException ex) {
    ex.printStackTrace();
} catch (Exception ee) {
    ee.printStackTrace();
} finally {
    try {
        conn.close();
        s.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

Beware that in order to insert into a database table you should create your statetement passing the appropriate parameters.

Another important point is that you should always close the Connection, the Statement and the ResultSet when you are finished. When you close your Connection the second insert will be flushed to the database table.

marc_s · Accepted Answer · 2013-03-15 14:57:56Z

0

For the first problem you should use executeUpdate for statements altering the database, and executeQuery for when you retrieve data from the database.

For the second one you should create multiple statements, and not use the same statement for every query you are trying to execute. you also should close each statement/connection properly.

edited Mar 15, 2013 at 14:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 4, 2013 at 9:12

G-Man

1,3318 silver badges15 bronze badges

Collectives™ on Stack Overflow

java and msaccess insert sql doesnt work

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related