Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Insert into MS Access with PreparedStatement

 
Wb Webster
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
just for starters, you might want to have a con.commit() to ensure changes are being committed to the database.
if that isn't the problem, you should make sure that you have the most up to date MSAccess ODBC driver on your machine. Some of the older ones are pretty buggy.
Jamie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic