Win a copy of Head First Android this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Insert into MS Access with PreparedStatement

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic