Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    NJ
    Posts
    2

    Unanswered: Insert MS Access with PreparedStatement

    I am having a problem inserting into MS Access with a PreparedStatement. I am not getting any exception thrown or error message but when I check the database, the data that should be inserted isn't there. Here's the code:

    //=====================
    public String load(){
    String sql = "Select Distinct Phone from [Employee Data] WHERE Phone <> ''";
    String psSql = "Insert into tblPhone (id, Phone, Type) VALUES "
    + "(?, ?, 1)";
    String out = "";
    try
    {
    Connection con = db.getNewConnection();
    StringUtil su = new StringUtil();
    PreparedStatement ps = con.prepareStatement(psSql);
    ResultSet rs = db.openResultSet(sql);
    int i = 0;
    int j = 0;
    System.out.println("about to do loop");
    while(rs.next())
    {
    String thisPh = rs.getString("Phone");
    ps.setInt(1, ++i);
    System.out.println("set int 1");
    ps.setString(2, su.stripNonDigits(thisPh));
    System.out.println("set string 2");
    System.out.println("executing this update ...");
    j += ps.executeUpdate();
    System.out.println("current insert is:" + su.stripNonDigits(thisPh));
    }
    rs.close();
    System.out.println("Looped through " + i + " records.");
    return "Total of " + j + " records inserted";
    }
    catch(Exception e)
    {
    System.out.println(e.getMessage());
    System.out.println(e.toString());
    }

    return out;
    }
    //===============
    The script runs without any problems returning the following message at the end:

    Looped through 273 records.
    Total of 273 records inserted

    However, when I check the database, there are no records inserted.

    I tried the insert without a PreparedStatement and it works. Any suggestions?

  2. #2
    Join Date
    Apr 2002
    Posts
    188

    Re: Insert MS Access with PreparedStatement

    Originally posted by Wesley
    I am having a problem inserting into MS Access with a PreparedStatement. I am not getting any exception thrown or error message but when I check the database, the data that should be inserted isn't there. Here's the code:

    //=====================
    public String load(){
    String sql = "Select Distinct Phone from [Employee Data] WHERE Phone <> ''";
    String psSql = "Insert into tblPhone (id, Phone, Type) VALUES "
    + "(?, ?, 1)";
    String out = "";
    try
    {
    Connection con = db.getNewConnection();
    StringUtil su = new StringUtil();
    PreparedStatement ps = con.prepareStatement(psSql);
    ResultSet rs = db.openResultSet(sql);
    int i = 0;
    int j = 0;
    System.out.println("about to do loop");
    while(rs.next())
    {
    String thisPh = rs.getString("Phone");
    ps.setInt(1, ++i);
    System.out.println("set int 1");
    ps.setString(2, su.stripNonDigits(thisPh));
    System.out.println("set string 2");
    System.out.println("executing this update ...");
    j += ps.executeUpdate();
    System.out.println("current insert is:" + su.stripNonDigits(thisPh));
    }
    rs.close();
    System.out.println("Looped through " + i + " records.");
    return "Total of " + j + " records inserted";
    }
    catch(Exception e)
    {
    System.out.println(e.getMessage());
    System.out.println(e.toString());
    }

    return out;
    }
    //===============
    The script runs without any problems returning the following message at the end:

    Looped through 273 records.
    Total of 273 records inserted

    However, when I check the database, there are no records inserted.

    I tried the insert without a PreparedStatement and it works. Any suggestions?

    Hello

    do not issue an
    ps.executeUpdate();
    instead try a
    ps.execute();

    followed by a commit();

    I think that should do it

    marcos

  3. #3
    Join Date
    Aug 2003
    Location
    NJ
    Posts
    2

    Re: Insert MS Access with PreparedStatement

    Originally posted by Maor71
    Hello

    do not issue an
    ps.executeUpdate();
    instead try a
    ps.execute();

    followed by a commit();

    I think that should do it

    marcos
    I tried your suggestion, but that didn't work either. The same problem occurs where the program runs smoothly, but not data appears in the database.

  4. #4
    Join Date
    Mar 2010
    Posts
    1

    problem in prepared statement using ms access

    it is bcz of null value we pass in setXxx(-,-) method .if the retrieved parameter value from request is null then it shows blank in ms access table.Try to check it first............................................. ..........................

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •