0

The below code:

public void insertNewStudent(int id, String pass, String fname, String lname, String   street, String city, String state, int Zip, String Email, double GPA) {
    try {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    conn = DriverManager.getConnection("jdbc:odbc:RegistrationDB", "", "");
    String query = "INSERT INTO Students (ID, Password, FirstName, LastName, Street, City, State, Zip, EMail, GPA)" + "VALUES (?,?,?,?,?,?,?,?,?,?)";
    PreparedStatement ps = conn.prepareStatement(query);
    ps.setInt(1, id);
    ps.setString(2, pass);
    ps.setString(3, fname);
    ps.setString(4, lname);
    ps.setString(5, street);
    ps.setString(6, city);
    ps.setString(7, state);
    ps.setInt(8, Zip);
    ps.setString(9, Email);
    ps.setDouble(10, GPA);
    ps.executeUpdate();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

Throws the below exception:

java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an     architecture mismatch between the Driver and Application
    sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6956)
    sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7113)
    sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3072)
    sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
    sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
    java.sql.DriverManager.getConnection(DriverManager.java:579)
    java.sql.DriverManager.getConnection(DriverManager.java:221)
    business.studentDB.insertNewStudent(studentDB.java:53)
    controller.registercontrol.doPost(registercontrol.java:47)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)

How is this caused and how can I solve it?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
drowningincoffee
  • 93
  • 2
  • 3
  • 11
  • try leaving a space character in your query like this: "INSERT INTO Students (ID, Password, FirstName, LastName, Street, City, State, Zip, EMail, GPA)" + " VALUES (?,?,?,?,?,?,?,?,?,?)"; – Stelios Savva May 07 '13 at 17:31
  • Just tried your suggestion @SteliosSavva but that didn't seem to work. – drowningincoffee May 07 '13 at 17:42
  • Have you tried executing the query from the terminal? that way we can eliminate that the error is in the query syntax. Also i would recommend in your try-catch statement that you use catch SQLException.Just noticed..remove the space character between VALUES(?,?...) – Stelios Savva May 07 '13 at 17:50
  • I'm using an MS Access DB in MS Access so no. Thanks for that suggestion, I removed the spaces and all and still I'm not seeing changes reflecting. Here is one thought, the "ID" field in my table is an indexed primary key, could I need to pass the column name into the first prepared statement since its automatically indexed? ps.setInt(1, ID)? Might have just had a derp moment – drowningincoffee May 07 '13 at 18:10
  • Change `catch (Exception e) { System.out.print("Error:" + e); }` to `catch (Exception e) { throw new RuntimeException("Error with database", e); }` and see what happens then... – eis May 07 '13 at 18:22
  • @eis Did so and posted the error I got in my original post at the bottom. Don't really see how that is a possible error if I can select from my database. – drowningincoffee May 07 '13 at 18:50

2 Answers2

1

Add a finally block

PreparedStatement ps = null; // declare outside the try block
try {
  // ...
} catch (Exception e) {
  // ...
} finally {
  try {
    if (ps != null) ps.close();
    if (conn != null) conn.close();
  } catch (SQLException e) {
    // ...
  }
}

Leaving the Connection open may not be committing the changes to the database. If the Connection is being shared and you can't close it commit() your changes explicitly.

ps.executeUpdate();
conn.commit();

EDIT: In light of the stack trace shared please make sure that your Java IDE, Microsoft Access and JVM or JDK are all of the same bit i.e. either 32 or 64 bit versions. But, yes adding the finally block is still recommended.

Ravi K Thapliyal
  • 51,095
  • 9
  • 76
  • 89
1

So changing your exception handling uncovered your real problem, which seems to be:

The specified DSN contains an architecture mismatch between the Driver and Application

This basically tells that you have either 32-bit driver against 64-bit access or other way around. You need to find out which and use the correct one. More on this problem on this thread, for example.

Community
  • 1
  • 1
eis
  • 51,991
  • 13
  • 150
  • 199
  • I know I have a 64bit Access and a 32bit Netbeans. I set up data sources in both the 32ODBC and 64ODBC (like the post you linked) and still get the error. So I need to force Netbeans to run in 64? – drowningincoffee May 07 '13 at 19:07
  • @eis isn't `e.printStackTrace()` simple? – Ravi K Thapliyal May 07 '13 at 19:10
  • Thanks ravi and eis - I had ran into this problem before and now I want to slam my head into the keyboard for doing it again. It's good to know my actual code is correct though (I think) Cheers! – drowningincoffee May 07 '13 at 19:11
  • @Ravi OP was already missing the error message in the stdout, so I don't see how printing it in a different way would've helped. Exceptions should be raised, not just printed out. – eis May 07 '13 at 19:12
  • @drowningincoffee Happens to the best of us mate! Go get your coffee. :) – Ravi K Thapliyal May 07 '13 at 19:13
  • @eis the sop, pst and throw new runtime.. all would have printed on the server console. am i missing something here? Ah, OP saw it on the web page now.. is that it? – Ravi K Thapliyal May 07 '13 at 19:19
  • @Ravi sop prints on stdout, pst prints on stderr. throwing the exception both halts the execution (which should be done) and likely also will print to logs, console and the server output, which gives you the most exposure into what is wrong. – eis May 07 '13 at 20:11