CODEWITHSUNDEEP
javamysqlprepared-statementauto-increment
cassrynne
cassrynne

Reputation: 69

Set Auto-Increment into previous value JAVA PreparedStatement MYSQL

I have 3 buttons (add, save, cancel). If I press the add button, it automatically generates an auto-incremented value and is displayed in a text field. If I press the save button, it updates the record. My problem is when I press the cancel button, I want to be able to delete the current data added and set the auto-increment key to the primary key of the deleted data. Is it possible to do this?

dc.connect();
          try {
          PreparedStatement st=dc.getConnection().prepareStatement("Delete from Employeemaster where empno = '" + empno.getText() + "'");
          i=st.executeUpdate();
          if (i>0) {
            dc.getConnection().commit();


        }
    } catch (Exception e) {
        JOptionPane msg=new JOptionPane();
        msg.showMessageDialog(this,"Database Error: "+e.getMessage());
    }

    dc.disconnect();
    dc.connect();
          try {
          PreparedStatement st=dc.getConnection().prepareStatement("ALTER TABLE employeemaster AUTO_INCREMENT ='" + empno.getText() + "'");
          i=st.executeUpdate();
          if (i>0) {
            dc.getConnection().commit();


        }
    } catch (Exception e) {
        JOptionPane msg=new JOptionPane();
        msg.showMessageDialog(this,"Database Error: "+e.getMessage());
    }

I tried replacing 

ALTER TABLE employeemaster AUTO_INCREMENT ='" + empno.getText() + "'"

into 

ALTER TABLE employeemaster AUTO_INCREMENT = 10003;

and it worked. Is it possible to set the auto-incremented value to the one contained/entered in a textfield?

Additional info: The error I get is

"You have an error in your SQL syntax; check the manual that corresponds to your MYSQL server version for the right syntax to use near "10003" at line 1."

Upvotes: 1

Views: 1515

Answers (1)

ajreal
ajreal

Reputation: 47311

The use of single quote will cause mysql cast the auto_increment value (integer) into string, which is not desirable

Strip the single quote, like 

"ALTER TABLE employeemaster AUTO_INCREMENT=" + empno.getText()

Or

cast empno.getText() into integer

Upvotes: 1

Related Questions