java.sql.SQLException: Invalid handle

[poojavb]

Hello Friends,

Can anyone tell me how to increment a String variable by 1 in database.

Suppose the database table has value as SL1000 and we want the next value to be SL1001.
What should be done for such type of requirement in java.

[masijade]

for oracle investigate "trigger" and "sequence", for mysql invesitage "auto-increment", for other dbs something similar.

As a Senior Oracle Database Developer, I would NEVER mix datatypes in a single column!
However...
If it is in a standard format (ie. always 6 characters, starting with 2 letters)
New_ID := SubStr (Old_ID, 1, 2) || LPad (To_Char (To_Number (SubStr (Old_ID, 3)) + 1), 4, '0');

[poojavb]

As a Senior Oracle Database Developer, I would NEVER mix datatypes in a single column!
However...
If it is in a standard format (ie. always 6 characters, starting with 2 letters)
New_ID := SubStr (Old_ID, 1, 2) || LPad (To_Char (To_Number (SubStr (Old_ID, 3)) + 1), 4, '0');

The database is an access database.
The coding is done in Java.

[masijade]

Oh, so, in other words, you are simply going to wait and hope someone does it for you? Find an access forum/documentation/whatever and find out about sequences, then simply slap a letter onto the front of it while padding its length (and the access documentation will also show you how to do that). At least try, then post your attempt and maybe someone will help you fix it, if it doesn't work.

[poojavb]

Oh, so, in other words, you are simply going to wait and hope someone does it for you? Find an access forum/documentation/whatever and find out about sequences, then simply slap a letter onto the front of it while padding its length (and the access documentation will also show you how to do that). At least try, then post your attempt and maybe someone will help you fix it, if it doesn't work.

I have written a code in such a way that a numeric string gets incremented.

But I want an alphabet to be at its position and a number should be auto incremented.

Statement stmtUpdate = con.createStatement();
String Patientidval=txtPatientId.getText();
System.out.println("Patient ID value : "+Patientidval);
//s = String.valueOf(Integer.parseInt(s) + 1);
Patientidval=String.valueOf(Integer.parseInt(Patientidval)+1);
int idval=Integer.parseInt(Patientidval);
System.out.println("id value after converting : "+idval);
//idval=idval+1;
System.out.println("id value after adding one : "+idval);
String abc=Integer.toString(idval);
System.out.println("value of abc : "+abc);
String del1="delete from autoidvalue";
stmtUpdate.executeUpdate(del1);
System.out.println("Delete query: "+del1);
String Query="insert into autoidvalue values('"+Patientidval+"')";
System.out.println("Insert query: "+Query);
stmtUpdate.executeUpdate(Query);
System.out.println("Auto ID value: "+abc);
con.close();

[masijade]

But I want an alphabet to be at its position and a number should be auto incremented.

And for that I said to find some MS Access doumentation which will tell you how to get an auto increment type field and that same access documentation should be able to tell you how to, in a query, make sure that number (as a string) contains a specific amount of characters, and pad it when it is not, as well as how, in an access query, to concatenate strings. Have you done that yet? In any case, this is not a Java question in the first place.

[mrsathishkumar]

This occurs when you close the connection multiple times or withing the using period of the connection i.e within the scope of the result set.This reason only i found in my code.
While clearing this kind of exception.