wood burning stoves 2.0*
The moose likes JDBC and the fly likes Insert into MS Access with PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Insert into MS Access with PreparedStatement" Watch "Insert into MS Access with PreparedStatement" New topic
Author

Insert into MS Access with PreparedStatement

Wb Webster
Greenhorn

Joined: Aug 27, 2003
Posts: 1
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

Joined: Jul 09, 2001
Posts: 1879

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
 
 
subject: Insert into MS Access with PreparedStatement
 
Similar Threads
Help with MS Access & Date
problem in insering data in msaccess thru jdbc
Prepared Statements
Failed to insert a record into MSAcess
Error connect to db after several transaction