Insert into MS Access with PreparedStatement
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Anonymous
Posted On:   Thursday, August 28, 2003 05:32 AM

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();    More>>
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?

   <<Less

Re: Insert into MS Access with PreparedStatement

Posted By:   Zac_Jacobson  
Posted On:   Thursday, August 28, 2003 12:25 PM

When you say, "I tried the insert without a PreparedStatement and it works," do you mean in Java, or in your DB UI?


Where's the commit? If auto-commit is on, try reading all the data from the ResultSet and closing it before you run the update statements - though if that was the problem I would have expected Exceptions.

About | Sitemap | Contact