Win a copy of Rust Web Development this week in the Other Languages 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
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

Can't delete a row in an Access database

 
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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( "jdbc:odbc: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( "jdbc:odbc: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
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
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
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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
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