aspose file tools*
The moose likes JDBC and the fly likes Can't delete a row in an Access database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can Watch "Can New topic
Author

Can't delete a row in an Access database

Katie McCann
Ranch Hand

Joined: Jul 24, 2000
Posts: 45
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

Joined: Mar 06, 2001
Posts: 13459

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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Jul 24, 2000
Posts: 45

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can't delete a row in an Access database