Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can't delete a row in an Access database

 
Katie McCann
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi. I'm connecting to an Access database via JDBC- ODBC. I can read data and add rows just fine. But I can't delete a row. I"ve tried 2 ways:
#1:
String sql= "select * from data where state=? and school=? and lastname=? and firstname=? ";
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection( "jdbcdbc:profsaccessdata" );
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, state);
st.setString(2, school);
st.setString(3, lastName);
st.setString(4, firstName);
ResultSet rs= st.executeQuery();
while (rs.next())
{
rs.deleteRow();
break ;
}
This gives this error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Invalid cursor position; no keyset defined
___________
#2 (note the "delete" in the SQL):
String sql= "Delete from data where state=? and school=? and lastname=? and firstname=? ";
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection( "jdbcdbc:profsaccessdata" );
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, state);
st.setString(2, school);
st.setString(3, lastName);
st.setString(4, firstName);
int updated= st.executeUpdate();
System.out.println(updated);
This way, if I give it paramaters I know exist in the database, it will print out "1" in my system.out.println statemnet at the end, which according to the API documentation implies that Row #1 of the resultset was deleted (if I give it bad parameters, it prints out 0, as expected). But the data is still in my Access database.
Any ideas??
Thanks in advance.
Katie
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the executeUpdate part is returning '1' that should be correct. It returns (in this case) the number of rows deleted.
Could be a problem with sql transactions or Access itself. I've had problems with Access if I have the database open at the same time I'm trying to get to it with Java. (ie make sure Access is closed first)
It may also be that you need to commit your changes, either:
1) set the connection as auto-committing, every time you perform an operation it will be committed for you conn.setAutoCommit(true);
2) commit when you need to
conn.commit();
The first one is easier but not recommended unless all your operations are stand-alone...
Dave.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have also seen this problem caused by no fault of the programmer. The method I've seen this solved with is to either
1. close your connection after the delete
or
2. do a dummy select on the table after you delete the record
(ie select column1 from the_same_table where....) Somehow the dummy select on the table that you just deleted from forces the commit.
Jamie
 
Katie McCann
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Thanks for the help- got it working. Seems that it was just the fact that I forgot to close the connection (dumb oversight on my part) that was doing it. D'oh.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic