Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQLException error: "Invalid Cursor position"

 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
my code goes somewhat like this:

queryx ="Select * From cleanTableA order by START_DATE";
ResultSet rsAcd = stmtA.executeQuery(queryx);

while(rsAcd.next())
{
String parseDrugname = rsAcd.getString(4);
int index = parseDrugname.indexOf(" ");
String result = parseDrugname.substring(0,index);
if(!confounderA.equals(result))
{
rsAcd.deleteRow();
//deleting a specific row
}
else
{
System.out.println("do not delete the row");
}

System.out.println(parseDrugname);
}
stmtA.close();
con.close();

Now when i run this code it is not showing the drugname(column data) from the row it is supposed to delete but it is not actually deleting from the the database.

And it also giving an exception error like this:

do not delete the row
IBUPROFEN 600MG TAB
do not delete the row
IBUPROFEN 400MG TAB
do not delete the row
IBUPROFEN 600MG TAB
...(omiting the the name that is deleted)
SQLException: [Microsoft][ODBC Microsoft Access Driver]Invalid cursor position;
no keyset defined
Press any key to continue...
 
Ram Krish
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

How many records are there in the table?

I guess the problem is in the result set usage...

Thanks
Ram
 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
5
out of which 2 are to be deleted....
though i found out a partial solution by now...

like i have made resultset as scrollable and updatable BY:

stmtA = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

...and it is indeed deleting the required rows actually from the table

but i am still gettting the SQLException Error

 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
is it something to do with commit()/rollback each time you delete and update the table....please help

Thanks in advance
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bijoy,
Is there a reason you aren't using a delete statement instead of looping through each row?

For example,
 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for replying though...,

but i can't do that way because drugnam in thre database is like "IBUPROFEN 200MG TAB or 400MG etc." whereas i have just part of the string i.e. (just the actual drug name "IBUPROFEN")to compare with....

so i have to select each one of them from the table parse the string name (drug name) and then compare with the drug name that user had entered...And if it doesnt match i delete the row and vice versa....


but i now managed to delete the row from the DB, but i am getting an invalid Cursor state error....Can you tel me why? Thanks once again
 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
please please ....help
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bijoy,
You can still have the database do more of the work. The % sign is a wildcard match. So you can tell the database to delete any records that begin with the drug name, a space and then anything else. Essentially, this is what you are doing in your Java program.


The reason I'm harping on this is because letting the database do the work is cleaner, faster and easier to maintain.

Jeanne

Just a note: It's good to give people 24 hours for a reply before bumping a thread. A lot of people, like me, only go on once a day.
 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I followed your Idea of deleting a row by an sql command instead of using a delete method of ResultSet - [DeleteRow() methode]. the idea worked very well..
but in the else portion i again have to delete a specific row based on some criteria and i can't use the same way for it (i.e. SQL command) and i have to use the deleteRow methode. The problem is though it is working but it is givin an SQL error/warning "invalid cursor state". can anyone tell me why?

Thanks
 
Carol Enderlin
drifter
Ranch Hand
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What about the "other criteria" can't be turned into SQL?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bijoy,
I agree with Carol. Almost anything can be represented in SQL. There are a few exceptions, but let's see if yours is one of them before troubleshooting more complex code.
 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But i wanna know why is that whenever i use DeleteRow() method of ResultSet class i gives me "invalid cursor state" even though it is deleting the row from the Db.

i am guessing in the last loop in the while Loop, when it deletes a row Say the last row of the Table and as we know whenver it deletes a row it increments by one position. now when cursor again goes back to
---> while(rsAcd.next()) position, it may tries to locate the next available row in which case it is already ahead of last row.... i believe thats when it fires the exception "invalid cursor state".

am i guessing rt or is there something else??

Thanks for any help!!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bijoy,
To be honest, I have no idea. It would depend on the implementation of your driver. I've never encountered that problem because I consider it poor design to delete rows in that fashion.
 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne, i agree because i am getting this kind of warning:

SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state

do you know how to handle this exception
 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i have another question: 1) is JDBC DBC driver does allow scrolling feature because whenever i am using DeleteRow() methode of ResultSet class i am getting and exception specified above. 2)and is there any place i can download this driver or does it come with JDK1.4.2 version....i am basically using Jcreator PRO as my development IDE.

thanks
Bijoy
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bijoy,
1) This would be in your driver documentation.
2) The driver comes with your database, not with the JDK.
 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
HI jeanne

thanks for help first... but do you think I will achieve scrolling feature if i use Rowset or CachedRowSet??

thanks for any help in advance
 
bijoy bose
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks FOlKs ATLAST I got it running....actually it was the problem in the driver itself, I was using J2SE 1.4.1 version whereas JDBC 2.0 and above only handles/extends scrollabilty feature in it. so all i have to do is to change my JDK from 1.4.1 to J2SE 1.4.2 version


THANKS to all of you!!!

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bijoy,
Thanks for sharing the solution!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic