How to insert a row into MS Access using JDBC PreparedStatement?

Question

Values are not getting inserted in Access DB. My DSN name is 'DB' and table name is 'Employee'. Access DB is getting connected(indicated by modified time), but no successful insert operations. NetBeans gives error: 'DB location not set correctly.' I've setup DSN with my DB in Data Sources (ODBC).

try (Connection c = DriverManager.getConnection("jdbc:odbc:DB")) {
    PreparedStatement pss = c.prepareStatement("INSERT INTO Employee VALUES (?,?,?,?)");
    pss.setString(1, fn);
    pss.setString(2, ln);
    pss.setString(3, mn);
    pss.setString(4, add);
    pss.executeUpdate();
    c.commit();
}
} catch (ClassNotFoundException | SQLException e) {
    System.out.println(e);
}

Here's the complete code:

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author javaEntu
 */
public class DB extends HttpServlet {

    private static final long serialVersionUID = 1L;

    /**
     *
     * @param request
     * @param response
     * @throws ServletException
     * @throws IOException
     */
    @
    Override
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws         ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        try (PrintWriter out = response.getWriter()) {
            String fn = request.getParameter("fn");
            String ln = request.getParameter("ln");
            String mn = request.getParameter("mn");
            String ps = request.getParameter("ps");
            String add = request.getParameter("add");
            String gen = request.getParameter("gen");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet SV</title>");
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet SV at " + request.getContextPath() + request.getRequestURL()     + "</h1>");
            out.println("</body>");
            out.println("</html>");
            out.println("<B>Your details: </B>");
            out.println("<br>First name: " + fn);
            out.println("<br>Last name: " + ln);
            out.println("<br>Contact number: " + mn);
            out.println("<br>Password: " + ps);
            out.println("<br>Address: " + add);
            out.println("<br>Gender: " + gen);
            out.println("<br>Thank you!");

            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                try (Connection c = DriverManager.getConnection("jdbc:odbc:DB")) {

                    PreparedStatement pss = c.prepareStatement("INSERT INTO Employee VALUES     (?,?,?,?)");
                    pss.setString(1, fn);
                    pss.setString(2, ln);
                    pss.setString(3, mn);
                    pss.setString(4, add);
                    pss.executeUpdate();
                    c.commit();
                }
            } catch (ClassNotFoundException | SQLException e) {
                System.out.println(e);
            }
        }
    }
}

Check your console output. You may be getting the error "Number of query values and destination fields are not the same." — Gord Thompson, Sep 07 '14 at 10:22
@GordThompson: No there are no such errors. I've rechecked my Table columns. FirstName is my PrimaryKey. — javaEntu, Sep 07 '14 at 11:57
Does your Employee table have exactly four (4) fields defined? (By the way, FirstName is a very poor choice for a primary key field. What will you do when you need to add John Smith to the database after previously adding John Doe?) — Gord Thompson, Sep 07 '14 at 12:53
Yes, exactly 4 fields. I just need to check code with Access Database, so Primary key not an issue right now. I've worked with MySQL and it worked well. Not so familiar with ODBC-Access. — javaEntu, Sep 07 '14 at 14:00
Check answers here: http://stackoverflow.com/questions/1418807/accessing-access-over-jdbc-using-odbc?rq=1 — Dmytro Plekhotkin, Sep 07 '14 at 15:23

score 0 · Answer 1 · answered Sep 07 '14 at 15:12

0

You should add c.close() after c.commit():

 try (Connection c = DriverManager.getConnection("jdbc:odbc:DB")) {
     PreparedStatement pss = c.prepareStatement("INSERT INTO Employee VALUES     (?,?,?,?)");
     pss.setString(1, fn);
     pss.setString(2, ln);
     pss.setString(3, mn);
     pss.setString(4, add);
     pss.executeUpdate();
     c.commit();
     c.close();
 }

answered Sep 07 '14 at 15:12

Dmytro Plekhotkin

1,965
2
23
47

Still it doesn't work. I guess there's issue with NetBeans' DataBase connectivity. – javaEntu Sep 08 '14 at 07:32

score 0 · Answer 2 · edited May 23 '17 at 12:21

Your code looks good. You can add log statements to show how connection looks (it will look like sun.jdbc.odbc.JdbcOdbcConnection@15be5b6). Also executeUpdate() returns how many rows were inserted. In my northwind MS Access database it works and returns 1 for similar INSERT. Log it somewhere.

I think you problem is with DB connection. Look for any ODBC editor and check if your DSN works. You can also change your connect string to something like: jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Nwind.mdb.

May it be that DSN in configured in 32 bit and your Java is 64 bit or vice-versa?

Also see for similar Netbeans error message at: the java db location is not set correctly

Thanks for testing my code. I'll check with log statements and new connect string. I'm using 64 bit Java. In Data Sources, 32 bit is not shown. Must be problem with Driver. — javaEntu, Sep 08 '14 at 14:15

How to insert a row into MS Access using JDBC PreparedStatement?

2 Answers2